Posts

Showing posts from April, 2023

Shell scripting cheatsheet

 SED :  it is used to replace a word  Replace a word in a file:sed 's/old_word/new_word/' file.txt Replace all occurrences of a word in a file:sed 's/old_word/new_word/g' file.txt Delete lines matching a pattern:sed '/pattern/d' file.txt AWK: Print specific columns from a CSV file:awk -F ',' '{print $1, $3}' file.csv Calculate the sum of a column in a numeric file: awk '{sum += $1} END {print sum}' file.txt Filter lines based on a condition:awk '$3 > 50 {print $1, $3}' file.txt Shebang: The shebang, also known as a hashbang, is the first line of a script and specifies the interpreter to use. For example, #!/bin/bash Variables: Variables store data for later use. $name. To declare a variable as global, use the export keyword. Command substitution: For example, files=$(ls) stores the output of the ls command in the files variable. Control structures: if-else: Conditionally execute code based on a condition. It has the form: if co...

logical standby database

 An Oracle Logical Standby Database is a type of standby database that provides transaction-level replication and is designed for high availability and disaster recovery purposes. It is a feature of Oracle's Data Guard technology. In a logical standby database configuration, changes made to the primary database are captured in the form of SQL statements, which are then transformed and applied to the logical standby database. Unlike a physical standby database that replicates data at the block level, a logical standby database replicates data at the SQL level. Here are some key features and characteristics of an Oracle Logical Standby Database: Read-Write Access Different Database Structures Data Filtering DML and DDL Replication Delayed Apply Switchover and Failover steps to create logical standby  Prepare the Primary Database:ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Enable force logging: ALTER DATABASE FORCE LOGGING; Create a Standby Initialization Parameter File:DB_UNIQUE_N...

ZDLRA

  RPO focuses on the maximum acceptable data loss  RTO focuses on the maximum acceptable downtime. Oracle DG - RTO Active DG - RTO RAC  - RTO Golden Gate - RPO Flashback - RPO ZDRLA - RPO Oracle Zero Data Loss Recovery Appliance (ZDLRA) deliver to you the capacity to improve the reliability of your environment in more than one way. You can improve the RPO. It has  lot of new cool features  virtual backups Real-time redo Tape and cloud DG/MAA integration Incremental forever backup strategy ZDLRA is one Engineered System built over Exadata. Contains Exadata Storage and Database nodes, InfiniBand network, SAS drivers Exadata, it is not just hardware, it is software too. ZDLRA solve problems in more than one topic, it is not just a backup appliance where you redirect your backups, it is an appliance that provides zero data loss. To do that the key features are: Virtual backups: Based on the input backups (incremental) generate a virtual full backup for you. Real-tim...

Oracle database 19C new features

Installation and upgrades: Simplified image based installation of client  RPM Based Installation  install Oracle 19c Database using RPM method with Vagrant Virtual box and via ansible playbook Docker Container for Oracle 19c Auto Upgrade Utility for Oracle Database DryRun mode for GridSetup in Clusterware Installation  Auto upgrade: Download latest auto upgrade  Install the auto upgrade ./auto_upgrade.sh -config /path/to/config_file Review the report and fix the issues Run the auto upgrade script  RPM based installation  Install VirtualBox on your machine. Install Vagrant on your machine. Create a new directory for your Vagrant environment: mkdir oracle19c Navigate to the newly created directory: cd oracle19c Initialize the Vagrant environment: vagrant init General Clear flashback logs periodically Multimodel partitioning with Hybrid partitioning allowing some partitions in the database and some as external partitions even in HDFS Passwords removed from sch...

database Auditing

Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang. init parameters Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file. From Oracle 11g, auditing is enabled for some system level privileges. SQL> show parameter audit NAME TYPE VALUE ---------------------- ------------ ------------- audit_file_dest string ?/rdbms/audit audit_sys_operations boolean FALSE audit_syslog_level string NONE audit_trail string DB transaction_auditing boolean TRUE AUDIT_TRAIL can have the following values....

what is data redaction

 What is Oracle Data Redaction with Examples (Mask your sensitive Data) Oracle Data Redaction is one of the new features introduced in Oracle Database Oracle 12c .This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real-time, without requiring changes to the application. Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by applications. You can redact column data by using one of the following methods: Full redaction. You redact all of the contents of the column data. The redacted value returned to the querying application user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a single space. Partial redaction. You redact a portion of the column data. For example, you can redact a Social Security number with asterisks (*), except for the last 4 digits. Regular expressions. Y...

Oracle Label Security

Oracle Label Security (OLS) is a feature provided by Oracle Database that enables the implementation of fine-grained access control policies based on data labels. It is designed to enhance data security by enforcing data confidentiality and access restrictions at a more granular level. OLS operates by assigning security labels to rows in database tables, as well as to individual columns or specific portions of data. These labels categorize data based on sensitivity, classification, or any other criteria relevant to the organization's security requirements. Examples of labels could include "Confidential," "Internal Use Only," or "Public." With OLS, administrators can define security policies that control access to data based on these labels. The policies can specify which users or roles are allowed to view, modify, or delete data with specific labels. This fine-grained access control ensures that only authorized individuals or groups can access sensitiv...

what is VPD in oracle

 VPD stands for virtual private database in oracle. Using VPD we can enable security at the row and column level for a table  to user. For example . I have an employee table with a salary column , using VPD we can restrict users having employee privileges to access only their salary and users with manager privileges can access salary column data of all users. With VPD, you can define security policies based on user attributes such as their identity, role, or session context. These policies are implemented through the use of database functions called "policy functions" that are associated with tables or views. These policy functions are invoked automatically by the database whenever a user attempts to access the data. When a user queries a table or view that is protected by VPD, the associated policy function evaluates the user's session attributes and dynamically adds WHERE clauses to the SQL statement, limiting the result set to only the rows that the user is authorized ...

Database Vault

Database vault  is a licensed feature in oracle which will help us to impose security at the database to the next level by eliminating oracle super user to access sensitive application data. For example I have an employee table having sensitive columns of SSN and salary . We can restrict sys user to access employee table and also its sensitive columns and also can authorize uses to access the table to perform DML operations and to  restrict sensitive columns to read only access and also can set time bound restrictions to the authorized uses to perform any actions on the table  database vault has been introduced from 10g onwards but the features have been enhanced for each release. In 19C DB vault feature has been extended to standby database as previously DB vault feature is not auto applicable to standby server upon enabling at the primary site. DB vault is integrated with database security assessment tool (DBSAT) From 21c onwards there is no need to disable the DB vault...

DB Wait events

  CPU Time: Scenario: A SQL query is taking a long time to execute, causing the CPU Time wait event to occur. The query is using a full table scan instead of an index, which is causing a high CPU load and slowing down other processes. fixes: ===== Adding indexes Query rewrites  limiting number of sessions  I/O Related Wait Events: Scenario: A database application is experiencing slow I/O operations, causing the I/O Related Wait Event to occur. The storage systems are not optimized and there is heavy contention for storage resources. Fixes: =====  Review your Storage system  Check for disk usage  Check for storage replacement for a high end hardware Locks: Scenario: A database application is experiencing slow performance due to excessive locking. The application is using table-level locking, causing a high contention for resources and slowing down other processes. fixes : ==== switch to row level locking Make sure to use frequent commits in the code  Ne...

How to perform Fleet pacthing on Cloud using Ansible?

  Ansible is a popular automation tool that can be used for fleet patching in a cloud environment.  With Ansible, you can create playbooks that specify which instances to patch, which patches to apply, and how to handle errors or rollbacks. Ansible can also be used in combination with other automation tools such as AWS Systems Manager, Azure Update Management, OCI System management Service, Google Cloud Operations  Here are the high level steps to use Ansible for patching instances: Install Ansible  Configure Cloud Authentication Install Ansible Collection  Create Inventory File --> you can use pythin to automate the file  Create a Playbook using Yaml code  run the Playbook  Verify the patching  eg: patching for AWS Install Ansible  Configure Cloud Authentication Install Ansible Collection  [ec2_instances] 10.0.0.1 10.0.0.2 10.0.0.3 ... python script  to automate inventory file  import boto3 # Create an EC2 client ec2 =...

what is server side load balancing in RAC and how does it happen?

 In a Real Application Clusters (RAC) environment, server-side load balancing refers to the ability to distribute client connections across multiple database instances (or nodes) in the cluster. This ensures that the workload is evenly distributed among the nodes, which can improve performance and scalability. Server-side load balancing is achieved through the use of a load balancing advisory, which is a set of algorithms that determine the most appropriate node for a particular client connection. The advisory takes into account various factors such as the current workload, available resources, and the location of the data being accessed. When a client connects to the RAC cluster, the connection request is intercepted by the Oracle Net listener, which then forwards the request to one of the available nodes based on the load balancing advisory. The node chosen by the advisory becomes the connection's primary instance, which is responsible for managing the client's session and co...

My Query is running Slow , but it has stats up to date . Can we expect fragmentation issue

 Yes, a table can still become fragmented even if the statistics are up-to-date. Fragmentation can occur when data is inserted, updated or deleted from a table. When data is modified, it may not fit into the same space as the original data, leading to fragmentation of the table. To fix table fragmentation issues in Oracle, you can use one or more of the following methods: Fragmentation is a common issue in Oracle databases that occurs when a table or index has allocated space that is not contiguous or is not efficiently used. Fragmentation can lead to performance issues, such as slower query response times and increased disk I/O. There are several types of fragmentation that can occur in an Oracle database: Extent fragmentation: This occurs when a table or index has free space that is not contiguous. This can happen when rows are deleted from the table, or when the table is resized. Extent fragmentation can cause performance issues because the database has to search multiple extent...

what is high water mark contention and how to fix it

 High water mark contention is a type of contention in Oracle databases that occurs when multiple sessions try to insert data into a table or partition simultaneously. It happens because the database needs to find an available block to insert the data, and this requires updating the high water mark of the table or partition. If multiple sessions try to do this at the same time, they can conflict with each other and cause contention. To find high water mark contention, you can use the Oracle wait event "enq: HW - contention" in the V$SESSION_WAIT view. This wait event indicates that a session is waiting for access to the high water mark of a table or partition. To fix high water mark contention, there are several possible solutions depending on the specific scenario. Here are some general recommendations: Increase the PCTFREE value of the table or partition. This reserves more space for updates and reduces the need to update the high water mark frequently. Use partitioning to ...

what is a cursor , cursor sharing, adaptive cursor sharing in oracle?

what is a cursor , cursor sharing, adaptive cursor sharing in oracle? A cursor is referred to as a work area  in the database memeory for a sql statement executed by the user. For every sql statement execution  oracle will generate 2 cursors called as parent cursor and a child cursor.  A parent cursor will hold the sql statement information and a child cursor will hold information about bind values,literals,schemas and statistics information which  essentially makes a child cusrosr as deciding factor to go for hard or soft parsing. You may come across a situation where 2 sql statement which is  executed by the user has same patrent cursors , but the child cursor is not shareable to SQL goes for hard parse (re-compile) Views: ====  Parent cursor info can be viewed in v$sqlarea version_count column in sql area will tell about number of child cursors for a parent cursor  In order to check whether a child cursor is shared or not we can use the view V$SQL_S...

explain ITL (Interested Transaction List) wait events

 ITL (Interested Transaction List) wait events occur when a transaction is waiting for a row that is currently locked by another transaction. When a transaction modifies a row in an Oracle database, it acquires a lock on that row to prevent other transactions from modifying it at the same time. The number of transactions that can lock a row is determined by the INITRANS storage parameter for the table or index. If a transaction attempts to acquire a lock on a row that is already locked by another transaction, it will be added to the Interested Transaction List (ITL) of the block containing the locked row. The ITL is a list of transactions that have shown an interest in a particular data block, either because they hold a lock on a row in that block or because they have performed a read operation on a row in that block. When a transaction is waiting for a row that is currently locked by another transaction, it will be in the ITL wait state. The time spent in ITL wait state can contri...

Query rewrite examples

Materialized view query rewrite: Consider the following tables: CREATE TABLE orders (  order_id NUMBER PRIMARY KEY,   customer_id NUMBER,   order_date DATE,   order_total NUMBER ); CREATE TABLE customers (   customer_id NUMBER PRIMARY KEY,   customer_name VARCHAR2(50),   customer_city VARCHAR2(50) ); We can create a materialized view that aggregates the orders table by customer and stores the results in a separate table: CREATE MATERIALIZED VIEW mv_order_totals BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE AS SELECT customer_id, SUM(order_total) as total_orders FROM orders GROUP BY customer_id; Once this materialized view is created and enabled for query rewrite, Oracle can automatically use it to rewrite queries that involve the orders table and aggregate by customer. For example, the following query can be rewritten to use the materialized view instead of querying the orders table directly: SELECT c.customer_name, m.total_orders FRO...

what are different optimizer modes

 OPTIMIZER_MODE: If you have a large table with millions of rows and you want to retrieve all the rows, you should set the OPTIMIZER_MODE parameter to "ALL_ROWS". For example: SELECT * FROM large_table WHERE condition = value ORDER BY column_name; If you want to retrieve a small number of rows quickly, you should set the OPTIMIZER_MODE parameter to "FIRST_ROWS". For example: SELECT /*+ FIRST_ROWS */ column1, column2 FROM small_table WHERE condition = value ORDER BY column_name; If you want to retrieve a specific number of rows quickly, you should set the OPTIMIZER_MODE parameter to "FIRST_ROWS_N". For example: SELECT /*+ FIRST_ROWS(n) */ column1, column2 FROM small_table WHERE condition = value ORDER BY column_name; OPTIMIZER_INDEX_COST_ADJ: If you want to encourage the optimizer to use an index, you can set the OPTIMIZER_INDEX_COST_ADJ parameter to a higher value. For example: ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100; If you want to discourage the...

what is cursor sharing and adaptive cursor sharing in oracle , do we need to define using commands or any parameters

 In Oracle, a cursor is a database object that allows you to fetch data from a database table. Cursor sharing is a feature in Oracle that determines how Oracle will handle similar SQL statements that have different literal values. When two SQL statements are identical except for their literal values, they are called similar statements. For example, consider the following SQL statements: SELECT * FROM employees WHERE employee_id = 100; SELECT * FROM employees WHERE employee_id = 200; Although these two SQL statements are similar, they have different literal values (100 and 200). In Oracle, cursor sharing determines whether these two SQL statements will share the same cursor or not. There are two types of cursor sharing in Oracle: Implicit Cursor Sharing: This type of cursor sharing is automatic and requires no explicit configuration. Oracle automatically determines whether to share cursors based on the hash value of the SQL statement. Adaptive Cursor Sharing: This type of cursor sha...

MY query is picking bad execution plan suddenly , which used to run with low latency since one year

  Note: to add to the below context , we have to look for the best execution plan from the past history usually available under sysaux tablespace . For a temporary solution or volatile solution you can use SQL profiling to fix the plan and later run the SQL trace 10053 to get to know the detailed analysis of the bad execution of sql statement. Let's say you have a query that has been running well for a long time. However, after a recent database upgrade or patch, the query starts to run slowly. You check the statistics and confirm that they are up to date, and you also verify that there are no changes in the query syntax or parameters. You suspect that the issue is caused by a change in the optimizer's behavior after the database upgrade or patch. In this case, you can use SQL plan baselines to force the optimizer to use a previously known good execution plan. To create a SQL plan baseline for the query, you can use the following steps: Use the DBMS_SQLTUNE.CREATE_SQLSET proced...