Monday, September 16, 2024

Index Fragmentation

SELECT 

    index_name,

    table_name,

    leaf_blocks,

    empty_blocks,

    num_rows,

    DISTINCT_KEYS,

    (leaf_blocks - empty_blocks) AS used_blocks,

    ROUND((empty_blocks / leaf_blocks) * 100, 2) AS empty_block_percent

FROM 

    dba_indexes

WHERE 

    owner = UPPER('<YOUR_SCHEMA_NAME>')

    AND index_type = 'NORMAL' -- filter for B-tree indexes

    AND leaf_blocks > 0 -- ensure that only indexes with leaf blocks are evaluated

ORDER BY 

    empty_block_percent DESC;


Monday, September 9, 2024

Tuesday, December 15, 2020

AZ 303 Exam Notes

Exam AZ-303: Microsoft Azure Architect Technologieshttps://docs.microsoft.com/en-us/learn/certifications/exams/az-303 

Practice Test exam https://www.measureup.com/official-practice-test-az-303-microsoft-azure-architect-technologies.html 

Study Guidehttps://aka.ms/ESIStudyGuide_AZ-303

AZ-303 study guide, for the skills measured and link for all topics: https://aka.ms/ESIStudyGuide_AZ-303

Microsoft Azure Certification Pathshttps://docs.microsoft.com/en-us/learn/certifications/

An Azure Virtual Network allows you to communicate resources within the same Vnet, across different Vnets in Azure and for Hybrid connectivity to the on-premise, you can find the same terminology comparing than a physical network (ip addresses, subnetting, CIDR notation, network routing, etc). https://docs.microsoft.com/en-us/azure/virtual-network/virtual-networks-overview

VNet Peering uses the backbone Microsoft network to connect 2 Vnets in the same region or in different regions (Global Vnet Peering), provides a quick configuration, better performance and good security, within the same or different tenants, can be interconnected with Azure VPN Gateways to allow transitivity and Used Defined Routes (UDRs) can be used to control the next hop traffic. https://docs.microsoft.com/en-us/azure/virtual-network/virtual-network-peering-overview

An Azure Virtual Machine uses IaaS (Infrastructure as a Service), allows you to create a resource that runs on an Operating System (Windows or Linux based) to create your workloads, you are responsible for maintaining and operating the OS and its configuration, but not the infrastructure that is under the responsibility of the provider. https://docs.microsoft.com/en-us/azure/virtual-machines/

Your Azure VM has multiple components that in combination makes your VM to work properly, one of the core resources is the VM disk, you have multiple disk performances that uses HDDs or SSD drives, you can select different disk sizes and there are support for encryption at rest with SSE and ADE to encrypt your disk with Bitlocker for Windows and DM-crypt for Linux. https://docs.microsoft.com/en-us/azure/virtual-machines/managed-disks-overview

Azure VMs uses the IaaS deployment model that means that we are responsible for multiple layers, including to create our VMs in a High Availability, and HA option provides a SLA for your workload, but we need to create more than 1 VM for the same purpose under an LB solution to make sure that at least another instance is available for backing up our workload, the HA options are: -Using Premium SSD for all disks in a VM (99.9%) SLA. -Use an Availability Set in at least 2 Update Domains and 2 Fault Domains (99.95%). -Use Availability Zones (limited to few regions) (99.99%) SLA. https://docs.microsoft.com/en-us/azure/virtual-machines/availability

An Virtual Machine Scale set is a single resource that creates from 1 and up to hundreds or thousand computer with the same configuration, commonly used with an LB solution for the HA of your workload, as a difference with regular Azure VMs this resource supports horizontal scaling automatically based on rules, and scales out or in automatically as needed. https://docs.microsoft.com/en-us/azure/virtual-machine-scale-sets/overview

ARM templates allows you to use configuration files to create resources, ARM uses JSON files that can be created from blank or take JSONs from existing resources or access a public ARM template repository to create one or multiple resources from a single file, you can create a library of templates to save yours. https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/overview

Here you can find the Azure Quickstart Templates public official repository to search for hundreds of existing templates that goes from creating a simple Windows or Linux VMs, to create a complete infrastructure for a multi-tier globally available project. https://azure.microsoft.com/en-us/resources/templates/

A runbook uses commands to automate frequent tasks in response to an alert or a known failure, you can create runbooks for azure resources or using an automation account and an agent deployment to extend to the on-premise https://docs.microsoft.com/en-us/azure/automation/automation-runbook-types

An Azure Storage Account is a resources that uses Platform as a Service Model (PaaS) as a difference with an Azure VM, we do not control an operating system, we only create a resource for the purpose of storing data with security that supports from one and up to 4 storage services (Blobs, Files, Tables and Queues), uses HDDs or SSD by selecting Standard or Premium storage performance and assigns by default an initial capacity of 500TB per storage account, your storage account creates an endpoint per service using a unique URL. https://docs.microsoft.com/en-us/azure/storage/common/storage-account-overview

Recommended storage service usage: -Blob Storage: media files, VHD storage, logs. -File Storage: migrate existing on-premise file shares to cloud using SMB protocol. -Queue Storage: to access using code to a service to share messages between applications. -Table Storage: to access to a structured storage trough applications to insert, list, read, delete data using an OData protocol and the ability to query the information.

The different types of storage services within a Storage account supports multiple authentication options, including using Access Keys, Shared Access Signatures, Azure AD Authentication, here you can find detailed information about a specific storage services and authentication supported. https://docs.microsoft.com/en-us/azure/storage/common/storage-auth

Role-Based Access Control (RBAC) is the role delegation model that azure uses to assign permissions to resources within your subscription, there are multiple built-in roles that target the most common roles, but you can create your custom roles, there are role inheritance based on a structure created with Management groups, Subscriptions, Resources groups and resources. https://docs.microsoft.com/en-us/azure/role-based-access-control/overview

With Azure Key Vault you can protect Passwords, Keys and Certificates, each have its own granular role delegation, a key vault is commonly used for the purpose of securely authenticate resources or applications by using this secure storage, preventing developers to save authentication information in code, developers only use SDKs and libraries to reference in code the URI of the key vault in combination with a Managed Service Account that automatically pulls the secret from the vault. https://docs.microsoft.com/en-us/azure/key-vault/

A Shared Access Signature is recommended to use for delegating temporary and permission controlled access, you can set an start time and expiration and you can specify the list of permissions to use with an SAS if SAS expires in no longer valid for authentication. https://docs.microsoft.com/en-us/rest/api/storageservices/delegate-access-with-shared-access-signature

Azure AD is the identity service used by any azure subscription, with this service you manage your directory (users, groups, devices, domains, policies, identity security features, etc.) https://docs.microsoft.com/en-us/azure/active-directory/fundamentals/active-directory-whatis

Azure AD Identity Protection is a security service that monitors your users and automatically detect potential risks at sign in process, with cloud services protecting the users and the sign in process becomes critical. https://docs.microsoft.com/en-us/azure/active-directory/identity-protection/overview-identity-protection

Self-Service Password Reset is commonly used to remove the operational request usually taken by administrator to reset the password of the users as a self-service adding security controls in the process by using multi-factor authentication https://docs.microsoft.com/en-us/azure/active-directory/authentication/concept-sspr-howitworks

A conditional access policy allows you to set one or multiple conditions using signals around the user to based on rules allow or block an access to a user that is not connected from a secure network, or trying to sign in from an unfamiliar location or insecure device. https://docs.microsoft.com/en-us/azure/active-directory/conditional-access/overview

Multi-Factor Authentication adds multiple layers of authentication for the purpose of validate a user integrity continuously, a user will be prompted to configure a second factor of authentication in combination with his user/password by using a mobile for example to receive temporary codes that need to provide in the sign in process. https://docs.microsoft.com/en-us/azure/active-directory/authentication/concept-mfa-howitworks

To allow an external user to collaborate within your subscription, you can send an invitation via email, the user will receive an automatic email with the steps to follow, the user will be prompted to provide a MS related account to finally accept the invitation, once the invitation is accepted you can see the guest user in your directory and you can delegate a role. https://docs.microsoft.com/en-us/azure/active-directory/external-identities/b2b-quickstart-add-guest-users-portal

Azure AD Connect is an agent required to configure synchronization from your On premises AD DS to your Azure AD for the purpose of sync the required users to cloud and avoid creating users manually an make sure that the objects on both directories represents the same user. https://docs.microsoft.com/en-us/azure/active-directory/hybrid/how-to-connect-sync-whatis

Password Writeback complements the functionality of SSPR that is available in azure AD and allow the sync-back option to refresh the password in AD DS for the same user.

In Synchronized environments, SSO allows users to sign in to both environments without providing authentication multiple times, single authentication will be trusted in the other environment. https://docs.microsoft.com/en-us/azure/active-directory/manage-apps/what-is-single-sign-on

Azure Security Center uses a combination of policies that follows standards (ISO, SOC, TSP, etc..) or your custom policies, to validate your security condition in your subscriptions, you will generate a security score and triggers automatic recommendations and alerts when you are not in compliance, those recommendations can help you to increase your score. https://docs.microsoft.com/en-us/azure/security-center/security-center-introduction

Azure Monitor is a service that uses 2 datastores (Activity Log and Metrics) to capture all your activities from your resources and users and also stores information about the performance of your resources, from that information, you can create alerts that triggers from conditions that you can customize or configure with an action group for automatic response. https://docs.microsoft.com/en-us/azure/azure-monitor/overview

Alerts configuration detailed information: https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-overview

A service that displays the status of the Azure services, that we can use for troubleshooting: https://docs.microsoft.com/en-us/azure/service-health/

With Azure Cost management you can track your one or multiple subscription, configure quotas, download bills, create forecast, etc. https://docs.microsoft.com/en-us/azure/cost-management-billing/cost-management-billing-overview

App Insights helps you to track the performance of an application, you can use it for applications that you host in azure (VMs, Web Apps and Containers) or applications that you host outside azure (on premise) or other clouds, you have access to a instrumentation key for the connection. https://docs.microsoft.com/en-us/azure/azure-monitor/app/app-insights-overview

Log Analytics Workspace is consider a deep infrastructure monitoring resource, its not an included services like the Azure Monitor, you need to create it as a resources and after creating it, you need to connect the sources (Azure VMs, other Azure resources) and computers in the on premise or other clouds, after you have the connected sources you can access the workspace to run queries in Kusto Query Language to correlate the logs and create your custom reports. https://docs.microsoft.com/en-us/azure/azure-monitor/log-query/log-analytics-tutorial

Azure Load Balancer can be integrated with IaaS VMs within the same region to create a HA workloads, the Azure LB uses a series of rules, backend pools, health probes to monitor the service response from the multiple instances backing up the same service and automatically sends the traffic to the health instances until others are available, it’s a regional LB. https://docs.microsoft.com/en-us/azure/load-balancer/load-balancer-overview

An Application Gateway can use a Web Application Firewall with a series of OWASP rules that protects from common threats, operates at the Layer 7 taking the OSI model of reference and uses a series of rules, listeners and backend pool to make the decision of the traffic and monitor the health of the services within the backend pool. https://docs.microsoft.com/en-us/azure/application-gateway/overview

Firewall main features: -Stateful Firewall as a Service resource. -Highly available. SLA (99.95%) in single availability zone or 99.99% in two or more availability zone. -Automatic scalability. -Uses a Public Static IP. -FQDN url filtering and Network rule configuration. Frequently Asked Questions for Firewall: https://docs.microsoft.com/en-us/azure/firewall/firewall-faq

Azure Front Door is another Global LB solutions that uses split TCP unicast for the global distribution, also supported different types of resources and Public IP´s in his backend host configuration, includes security with a Web Application Firewall https://docs.microsoft.com/en-us/azure/frontdoor/front-door-overview

If you wonder, What is the LB solution to use for my requirements?, you can take a look at this LB decision tree: https://docs.microsoft.com/en-us/azure/architecture/guide/technology-choices/load-balancing-overview

Traffic Manager is considered a Global LB solutions, under the TM profile we can include Azure VMs, App Services, Public Ips, TM uses DNS to identify the request source and supports different routing options to make the decision of sending the traffic to closest endpoint or better performant. https://docs.microsoft.com/en-us/azure/traffic-manager/traffic-manager-overview

Key information about NSG: -Free of charge. -Can be assigned to Subnets or Network Interface Cards. -Can work in combination with Azure Firewall and 3rd party FWs. -Can only be assigned to NIC or subnets within the same azure datacenter region. https://docs.microsoft.com/en-us/azure/virtual-network/network-security-groups-overview

Important to understand the difference between NSG and ASG: An NSG creates access control rules with priority, port ranges, protocols, source, destination and actions (allow or deny) and an ASG it’s a resource that we can use to group IP´s in to an ASG that we can reference in a NSG on its source or destination to simplify the rule assignment, so NSG and ASG work together. https://docs.microsoft.com/en-us/azure/virtual-network/application-security-groups

Important to understand the difference between NSG and ASG: An NSG creates access control rules with priority, port ranges, protocols, source, destination and actions (allow or deny) and an ASG it’s a resource that we can use to group IP´s in to an ASG that we can reference in a NSG on its source or destination to simplify the rule assignment, so NSG and ASG work together. https://docs.microsoft.com/en-us/azure/virtual-network/application-security-groups

Azure Bastion is another security feature, enables a Subnet within a Virtual Network to accept secure remote connections to Windows or Linux VMs using all the time only private ip addresses, the remote connection runs on a modern update support browser, no need for remote clients. https://docs.microsoft.com/en-us/azure/bastion/bastion-overview

Azure Migration is a project that you can create in your subscription to collect data from your on premises workloads for the purpose of evaluate the actual performance and create reports that will help you in the process of create a discovery and assessment process, the migration process and post migration guidance. https://docs.microsoft.com/en-us/azure/migrate/migrate-services-overview

Azure Backup is a backup as a service resource, that you can use to protect VMs, File shares or On premises workloads, the Backup Services uses a Backup agent that computers in azure and outside requires to configure, the Backup Vault uses a unique endpoint that every computer can resolve an connect security with a password file, once the agent is sync with the Backup Vault you can control the backup and restore operations from the Cloud service. https://docs.microsoft.com/en-us/azure/backup/backup-overview

Update Management in Azure is the equivalent WSUS services that runs in the cloud, your subscription requires an automation account and a log analytics workspace to manage the updates to your VMs. https://docs.microsoft.com/en-us/azure/automation/update-management/overview

Desired State Configuration allows you to automate the process of configuring a VM and achieve the consistency in the state of the computer, files, services, software, etc. https://docs.microsoft.com/en-us/azure/virtual-machines/extensions/dsc-overview

Azure Governance is a combination of services and policies configurations and best practices to manage your subscriptions and control what users can o can´t perform based on your custom rules, some of the components for Governance are: -Management Groups -Azure Policy -Azure Blueprints -resource Graph https://docs.microsoft.com/en-us/azure/governance/

Access Review is commonly used to help you to make sure that "the right users has the right access" : https://docs.microsoft.com/en-us/azure/active-directory/governance/access-reviews-overview

An Azure policy is used to have control and consistency in your subscription for example, controlling to create azure resources in a specific azure datacenter region, apply name conventions to users, apply an effect to your policy that can be deny some deployment that is not compliant. https://docs.microsoft.com/en-us/azure/governance/policy/overview

With an Azure Blueprint we can re-create complete environments, a Blueprint is a package of JSON templates that represents resources configurations, Policies and Initiatives and Role delegation for users. https://docs.microsoft.com/en-us/azure/governance/blueprints/overview

Application Management with Azure AD, you can use it to outsource Azure AD and other populer Identity Service Providers to configure your custom apps with modern authentication: https://docs.microsoft.com/en-us/azure/active-directory/manage-apps/what-is-application-management

A Managed Service Identity is a special type of account that developers can use to perform automatic actions like pulling secrets from an Azure Key Vault, the MSI can be delegated with a specific permission. https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview

With Azure Key Vault you can protect Passwords, Keys and Certificates, each have its own granular role delegation, a key vault is commonly used for the purpose of securely authenticate resources or applications by using this secure storage, preventing developers to save authentication information in code, developers only use SDKs and libraries to reference in code the URI of the key vault in combination with a Managed Service Account that automatically pulls the secret from the vault. https://docs.microsoft.com/en-us/azure/key-vault/

An azure app service is a Platform as a Service (PaaS) service designed to run applications without the overhead of configuring, maintaining, and fixing a computer and its OS, developers have options to deploy to multiple frameworks and they can focus in the application, not in the infrastructure, support autoscaling, DevOps integration, Backup, SSL certificates, authentication options. https://docs.microsoft.com/en-us/azure/app-service/

App Service Plan is a resources that represents the compute for an App service, an App service plan can be used by more than one App Service at the same time considering the potential performance issue because the Plan has limited resources, is the representation of a VM size. https://docs.microsoft.com/en-us/azure/app-service/overview-hosting-plans

Azure Container Registry is a resource to store containerized applications, azure supports multiple options for running apps in container technologies, the images requires a registry services to host, maintain and pull the images from the container services. https://docs.microsoft.com/en-us/azure/container-registry/

A Logic App is another “serverless app” that allows you to create flows using 1 or multiple connectors, connector represents APIs from azure services or 3rd party services, you can extend the scope of a logic app to other clouds or the on premise, you have access to a designer to create your flows, no skills needed for coding. https://docs.microsoft.com/en-us/azure/logic-apps/

An azure function app is part of the azure “serverless apps” offering, works in the same App Service model with the difference that the function app can react on events, events can be other services or applications conditions or alerts, with a function app you can dynamically use compute to perform operations only reacting to an event, commonly used to automate processes without using virtual machines. https://azure.microsoft.com/en-us/blog/introducing-azure-functions/

Azure Kubernetes service (AKS) is a cluster technology designed to orchestrate multiple applications within a central cluster resource, supports for CI/CD pipelines integration, native security by using Node Isolation between apps, there is no need to manage and operate VMs with an AKS, that administration is taken care by the provider. https://docs.microsoft.com/en-us/azure/aks/

Azure Container Instance (ACI) is a resource to run containerized applications, there is no need to create or manage computers, you simply create an ACI instance, select it to run Windows or Linux as the OS reference, you reference the image that you want to pull from a registry that can be a Public or Private Container registry repository or using the ACR. https://docs.microsoft.com/en-us/azure/container-instances/

Azure Table storage is available from the General Purpose v1 and v2 storage account, provides you a NoSQL datastore to be accessed by Http/Https protocols and OData queries. https://docs.microsoft.com/en-us/azure/cosmos-db/table-storage-overview

Azure Cosmos DB is a NoSQL and globally turn-key distributed database supports multiple APIs for existing DB migrations and models like, Gremling, Cassandra, Mongo, SQL and Table APIs, supports up to five consistency levels for the global distribution and replicas consistency that goes from the loosest to the strongest. https://docs.microsoft.com/en-us/azure/cosmos-db/introduction

Azure SQL database is a PaaS service that allows you to host and manage relational databases without the need to configure and maintain a SQL server instance, supports security by using a SQL defender, Auditing and Vulnerability Assessment, Azure AD Authentication can be used, TDE encryption and Always Encryption support, Dynamic data masking, you can use it to migrate existing DBs or create new ones. https://docs.microsoft.com/en-us/azure/azure-sql/

Tuesday, May 7, 2019

Oracle Performance Tuning


How to Analyze the Performance History of a SQL Statement as Recorded in AWR Using Simple SQL (Doc ID 1580764.1)

http://www.nocoug.org/download/2008-08/a-tour-of-the-awr-tables.nocoug-Aug-21-2008.abercrombie.html

https://www.realdbamagic.com/script-finding-top-n-queries-user-awr/

https://blog.pythian.com/mining-the-awr-to-identify-performance-trends/

https://karlarao.wordpress.com/scripts-resources/

https://github.com/tanelpoder/tpt-oracle

https://blog.tanelpoder.com/posts/oracle-sql-monitoring-advanced-ash-usage-hacking-session/
https://blog.tanelpoder.com/2018/05/18/my-performance-troubleshooting-scripts-tpt-for-oracle-are-now-in-github-and-open-sourced/
https://github.com/tanelpoder/tpt-oracle
https://www.youtube.com/TanelPoder

EXTREME DETAILS ABOUT A SQL
https://mjsoracleblog.wordpress.com/2013/02/19/my-oracle_performance-github-repository/

https://github.com/khailey/ashmasters

http://bdrouvot.wordpress.com/real_time/ => script to get real time I/O


Thursday, October 5, 2017

Oracle GoldenGate GGSCI Commands



https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/ggsci_commands.htm#GWURF895


Wednesday, August 31, 2016

Database STARTUP_TIME History


set lines 1000
col STARTUP_TIME for a25
col platform for a40

select DBID, INSTANCE_NUMBER INST_NU,STARTUP_TIME,PARALLEL RAC, VERSION, DB_NAME,
INSTANCE_NAME,HOST_NAME ,LAST_ASH_SAMPLE_ID ASH_ID,PLATFORM_NAME PLATFORM
from dba_hist_database_instance order by STARTUP_TIME;

Tuesday, August 30, 2016

Golden Gate Lag Monitoring Script


http://sridharramireddy.blogspot.com/2014/09/script-to-monitor-goldengate-monitoring.html

Wednesday, July 8, 2015

Find Query Versions


SQL to find Query Versions based on a particular SQL_ID
=> you can remove the columns selected based upon your need.

Select inst_id, sql_id,address, hash_value, sql_text, version_count,loaded_versions,
executions,loads,invalidations
from gv$sqlarea
where version_count >= 1
and sql_id ='&Input_SQL_ID'
order by inst_id,version_count
/

Find historical data using SQL_ID




SAMPLE OUTPUT

Enter value for 1: 6ajkhumm78nrp





Friday, February 13, 2015

Troubleshooting 'log file sync'


Very Good Document on "log file sync" on My Oracle Support Site.
Troubleshooting: 'Log file sync' Waits (Doc ID 1376916.1)

http://www.pythian.com/blog/adaptive-log-file-sync-oracle-please-dont-do-that-again/

Tuesday, January 27, 2015

Oracle GoldenGate Limitations and Restrictions


Oracle GoldenGate supports two types of capture:

Classic Capture
Integrated Capture

Classic Capture
Oracle GoldenGate continues to support the existing Capture module, now referred to as Classic
Capture, which directly accesses the database redo logs looking for DML changes to capture for
distribution.
It is possible to capture from redo logs stored inside of ASM. Adjusting the read
size can improve Extract performance. In this mode, Extract can be integrated
with Oracle RMAN to manage log retention.


Excellent Post by another DBA - Credit goes to him.
http://sandeepnandhadba.blogspot.com/2014/12/oracle-golden-gate-12-bidirectional.html

Tuesday, August 26, 2014

Oracle ASM Commands







Identify the Disks you want to add and Disks you want to remove by using "oracleasm listdisks" as oracle user on the server.
The below command would add the disks you want to add and remove the older (existing disks) and do the re-balance. You can set
the rebalance power from 1 to 11. 11 would use more resources and would be the fastest way to rebalance.
NOTE: When adding Disk(s), you have to prefix the device name with "ORCL:"

Check the rebalance progress using the below script.