Search Results

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home How to Check Oracle Instance Size Safely increase Instance size for improved Oracle database performance. Determining the size of Oracle Instance is important for performance reasons. You must allocate adequate RAM for instance to run smoothly. By just allocating more RAM can sometimes result in resource wastage. Find Memory Used by Oracle Instance The below query will give you breakdown of the memory used by your Oracle Instance select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb from ( select 'sga' nm, sum(value) val from v$sga union all select 'pga', sum(a.value) from v$sesstat a, v$statname b where b.name = 'session pga memory' and a.statistic# = b.statistic# ) group by rollup(nm); A very simple query to display SGA components size in Oracle database SELECT * FROM v$sgainfo; Oracle Automatic Memory Management From 11g onward, you do not need to manage SGA and PGA separately. You can allocate MEMORY_TARGET parameter and oracle will handle both SGA + PGA. Oracle MEMORY_TARGET parameter defines the total instance size. Example, If MEMORY_TARGET is set to 5 GB, oracle will manage PGA + SGA within 5 GB Show parameter memory_target; alter system set memory_target = 5G; If you see memory_target value blank, then separate SGA and PGA are configured The MEMORY_MAX_TARGET parameter defines the maximum RAM that Oracle can use in case of heavy workloads. MEMORY_MAX_TARGET is a static parameter show parameter memory_max_target; alter system set memory_max_target = 7G scope=spfile; If MEMORY_TARGET is set to 5 GB and MEMORY_MAX_TARGET is set to 8 GB, then during heavy workloads, Oracle instance can max take upto 8 GB RAM Things to remember: SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values. SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET). PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET). Separate SGA and PGA Some databases require separate SGA and PGA configuration for application to work smoothly. The SGA_TARGET defines the total SGA size show parameter sga_target; alter system set sga_target = 9G; The SGA_MAX_SIZE defines the maximum RAM SGA can take during heavy workloads. SGA_MAX_SIZE is a static parameter and cannot be changed immediately show parameter sga_max_size; alter system set sga_max_size = 12G scope=spfile; If SGA_TARGET is set to 9 GB and SGA_MAX_SIZE is set to 12 GB, then during heavy workloads, SGA can max take upto 12 GB of RAM The PGA_AGGREGATE_TARGET defines the PGA size and PGA_AGGREGATE_LIMIT is set to (2 * PGA_AGGREGATE_TARGET ). show parameter pga_aggregate_target; show parameter pga_aggregate_limit; Hence, Instance Size = SGA_TARGET + PGA_AGGREGATE_TARGET Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Check Oracle database size Calculate and monitor the total size of your Oracle database. There are several ways to measure the size of an Oracle database. As an Oracle DBA, you may face the requirement to get the current database size. Find below the queries which you can use to find the size of the Oracle database. Find Oracle Database Size For very big databases where the size run into multiple TBs, below command will help you get a bird’s eye view on the database size, used space and free space. Please Note: this query rounds off the output numbers col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p / Here is another query that finds the database size based on the physical space consumed on the disks select "Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)", "Free_Space(GB)" from( select (select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)", (select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)" from dual ); When you run the above query, you will see below output Reserved_Space(GB) Used_Space(GB) Free_Space(GB) ------------------ -------------- -------------- 1.43491124 1.34488439 .090026855 We can see that 1.4 GB is the allocated space across all the data files in the database. Out of the 1.4 GB allocated segments, 1.3 GB is used and 0.09 GB is free space. Find Space Used by Users Inside Database We can even check the amount of disk space used by individual users inside the database using below query select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner; Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

DBA Genesis Documentation Explore our articles, scripts and database administration guides. Get started with DBA Genesis Courses Browse eBooks Latest Articles Oracle GoldenGate 21c Initial Load Extract and Change Sync Oracle Golden Gate Credential Store Oracle GoldenGate 21c for Oracle on Linux Script to create JUSTLEE schema in Oracle Connect Linux Instance with SSH Keys SQL Project - Library Management SQL Project - Supermarket Billing Oracle Enterprise Manager 13c Release 5 (13.5) Installation on Linux 7 Linux Project - Monitor Server Disk Space Oracle 23ai Installation on Linux Install Oracle 19c with ASM Oracle 21c Installation on Linux

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Check FRA location utilization Monitor and manage Oracle Fast Recovery Area (FRA) usage effectively. Sometimes FRA runs our of space and a DBA must be able to gather FRA space utilization. It is very important to monitor space usage in the fast recovery area to ensure that it is large enough to contain backups and other recovery-related files. Find Oracle FRA Size Below script gives you Fast Recovery Area utilization details set linesize 500 col NAME for a50 select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)", round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)", round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" , (select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2) from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)" from V$RECOVERY_FILE_DEST; Estimate Flashback Destination Size Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashback logs. The flashback log size is same as archive log size generated in a database. Check archive generation size via below query select to_char(COMPLETION_TIME,'DD-MON-YYYY') Arch_Date,count(*) No#_Logs, sum((BLOCKS*512)/1024/1024/1024) Arch_LogSize_GB from v$archived_log where to_char(COMPLETION_TIME,'DD-MON-YYYY')>=trunc(sysdate-7) and DEST_ID=1 group by to_char(COMPLETION_TIME,'DD-MON-YYYY') order by to_char(COMPLETION_TIME,'DD-MON-YYYY'); Let us assume the average archive log files size generated per day is 5 GB. Your application team wants you to enable FLASHBACK for 4 days. In this case you will need Approx 20 GB (5GB X 4Days) of space for flashback logs Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Oracle Golden Gate Credential Store Securely manage credentials with GoldenGate's credential store feature. When you work with Oracle Golden Gate with Oracle Multi-Tenant, you have to deal with multiple containers, users and tns connections . Trying to remember all those details when you issue dblogin inside ggsci becomes challenging. Golden Gate Credential Store manages your local database user ID's along with their encrypted passwords so you don't have to specify plain text passwords in the parameter files. Add Credential Store Add User to Credential Store Login to Golden Gate Using Alias Add Credential Store With a simple command, you can add credential store within Golden Gate GGSCI> ADD CREDENTIALSTORE This creates a credential store file under dircrd sub-directory. Add User to Credential Store To add a new user ID and password with an alias name GGSCI> alter credentialstore add user c##ggadmin@AKS01_CDB alias ggadmin_cdb Where: c##ggadmin: is a database username @AKS01_CDB: is a TNS connection string to the root container alias ggadmin_cdb: is an alias that can be used in parameter files to login with specific user & password Note: If you don't specify alias, golden gate To view the User Id's stored in Credential Store, user INFO command GGSCI> INFO CREDENTIALSTORE Login to Golden Gate Using Alias You are all set with Golden Gate Credential store and login to database using the Alias. Instead of USERID , you will use USERIDALIAS and specify the credential store alias GGSCI> DBLOGIN USERIDALIAS ggadmin_cdb If there is a change in user password at DB level, you must update credential store GGSCI> ALTER CREDENTIALSTORE REPLACE USER c##ggadmin@AKS01_CDB ALIAS ggadmin_cdb To delete a user from credential store GGSCI> ALTER CREDENTIALSTORE DELETE USER ggadmin_cdb Enjoy!! Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Oracle Database Auditing Secure and monitor your database with Oracle auditing. Oracle database auditing allows you to monitor certain database actions happening inside the database. Auditing also helps in looking at the actions performed against a particular table, schema or certain specific rows. You can check database auditing is enabled via SHOW PARAMETER command Show parameter audit; The AUDIT_TRAIL parameter defines database auditing status. It can take any of the following values none - Database auditing is disabled os - Enabled, audit logs are stored at OS level, not inside the database db - Enabled, audit records are stored inside database (SYS.AUD$ table) db,extended - Same as db but populates SQL_BIND & SQL_TEXT too xml - Enabled, audit records are stored at OS level in XML format xml,extended - Same as xml but populates SQL_BIND & SQL_TEXT too Default is DB if you create database via DBCA, else its NONE The AUDIT_FILE_DEST parameter defines the OS level location of the audit trail files. By default it is set to adump location. The AUDIT_SYS_OPERATIONS parameter defines auditing is enabled or disabled for any user connecting to database as SYSDBA. This is enabled by default. All the SYS operations audit records goes to OS level into AUDIT_FILE_DEST location. Move AUD$ Table to Another Tablespace By default the SYS.AUD$ (stores db audit records) and SYS.FGA_LOG$ (stores fine grain audit records) tables resides under SYSTEM tablespace col owner for a10; col segment_name for a10; col tablespace_name for a15; select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 as MB from dba_segments where segment_name in ('AUD$','FGA_LOG$'); The DBMS_AUDIT_MGMT package allows you to change the default tablespace. Below query moves AUD$ & FGA_LOG$ tables to USERS tablespace BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, audit_trail_location_value => 'USERS'); END; / If you want to only move AUD$ table then replace audit_trail_type with audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD If you want to only move FGA_LOG$ table then replace audit_trail_type with audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD Enjoy! Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Top resource intensive SQL Identify and address the most resource-consuming SQL queries in Oracle. Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is running slow. You can run below query to get the top 5 resource intensive SQL with SQL ID and then give it to application team to optimize them. Find top 5 resource intensive SQL col Rank for a4 SELECT * FROM (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "Buffer Gets" + "Disk Reads" DESC) AS "Rank", i1.* FROM (SELECT TO_CHAR (hs.begin_interval_time, 'MM/DD/YY' ) "Snap Day", SUM (shs.executions_delta) "Execs", SUM (shs.buffer_gets_delta) "Buffer Gets", SUM (shs.disk_reads_delta) "Disk Reads", ROUND ( (SUM (shs.buffer_gets_delta)) / SUM (shs.executions_delta), 1 ) "Gets/Exec", ROUND ( (SUM (shs.cpu_time_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "CPU/Exec(S)", ROUND ( (SUM (shs.iowait_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "IO/Exec(S)", shs.sql_id "Sql id", REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) ), CHR (10), '' ) "Sql" FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht ON (sht.sql_id = shs.sql_id) INNER JOIN dba_hist_snapshot hs ON (shs.snap_id = hs.snap_id) HAVING SUM (shs.executions_delta) > 0 GROUP BY shs.sql_id, TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'), CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) ) ORDER BY "Snap Day" DESC) i1 ORDER BY "Snap Day" DESC) WHERE "Rank" <= 5 AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY'); Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home PostgreSQL 15 on Oracle Linux 7.9 Step-by-step guide to installing PostgreSQL 15 on Oracle Linux 7.9. PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance and is supported by a number of different programming languages. It is often used as a backend for web applications and has a strong reputation for reliability, data integrity, and correctness. To install Postgresql, visit the official PostgreSQL website . Select your operating system Select your Linux distribution as REDHAT Select the latest version as 15, and Select platform as OEL 7 You will the steps to Install the Postgresql # Install the repository RPM: sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm # Install PostgreSQL: sudo yum install -y postgresql15-server # Optionally initialize the database and enable automatic start: sudo /usr/pgsql-15/bin/postgresql-15-setup initdb sudo systemctl enable postgresql-15 sudo systemctl start postgresql-15 Copy the first to install the repository RPM and run it in the terminal yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm Now Copy the second step to Install PostgreSQL yum install -y postgresql15-server Now, install using YUM yum install -y https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/libzstd-1.5.2-1.el7.x86_64.rpm Now, run the second step yum install -y postgresql15-server Now, after installation, we will initialize the database and enable automatic start: /usr/pgsql-15/bin/postgresql-15-setup initdb systemctl enable postgresql-15 systemctl start postgresql-15 and after that, we'll check for the process using ps -ef | grep postgres As we can see that our Postgres service is running using Postgres user. Let's login to the Postgres user and login to the psql su - postgres psql let's check for the database \l We can see that 2 templates and 1 sample Postgres database in the list. Thank you ..! Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home ASM Related Background Process Understand the background processes supporting Oracle ASM. Oracle ASM instance is built on same Oracle database instance architecture. Most of the ASM background processes are same as the database background process. ASM Background Process in DB Instance ASM Background Process in ASM Instance ASM Background Process in DB Instance Oracle database that uses ASM disks, two new background processes exists RBAL ASMB ASMB performs communication between the database and the ASM instance. RBAL performs the opening and closing of the disks in the disk groups on behalf of Oracle database. This RBAL is the same process as in ASM instance but it performs a different function. To find asm background process in oracle db instance, connect to Oracle database and issue below query SQL> select sid, serial#, process, name, description from v$session join v$bgprocess using(paddr); Note the ASMB and RBAL processes in the above list. You can even query using the process id at OS level (ps -ef|grep ). ASM Background Process in ASM Instance Oracle introduced two new background processes first in their 10g version: RBAL ARBn The RBAL performs rebalancing when a disk is added or removed. The ARBn performs actual extent movement between the disks in the diskgroup. To find asm background process inside asm instance, connect to ASM instance and issue below query sqlplus / as sysasm SQL> select sid, serial#, process, name, description from v$session join v$bgprocess using(paddr); Note: The ARBn process is started only when there is rebalancing operating happening inside diskgroup. Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Gather Statistics in Oracle Improve query performance by gathering optimizer statistics. Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather stats periodically using DBMS_STATS package. Gather Table, Index and Schema Statistics Gather Other Database Objects Statistics Check Stale Statistics Linux Script to Gather Stats Stats gathering must be done on regular basis Gather Table, Index and Schema Statistics DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES'); EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE); Cascade will gather Index stats associated with the table DBMS_STATS.GATHER_INDEX_STATS is used to gather index stats EXEC DBMS_STATS.gather_index_stats('HR','EMPLOYEES_PK'); DBMS_STATS.GATHER_SCHEMA_STATS package is used to gather entire schema stats EXEC DBMS_STATS.gather_schema_stats('SCOTT'); Gather Other Database Objects Statistics DBMS_STATS.GATHER_DATABASE_STATS package is used to gather entire database stats EXEC DBMS_STATS.gather_database_stats; DBMS_STATS.GATHER_DICTIONARY_STATS package will gather dictionary statistics EXEC DBMS_STATS.gather_dictionary_stats; Gather System and Fixed Object Stats: There is no specific need to gather system (hardware, CPU, memory) and Fixed objects (X$ views) stats on a regular basis. You should only gather system & fixed objects stats when there is a major change on server hardware or major update to the database EXEC DBMS_STATS.gather_system_stats; EXEC DBMS_STATS.gather_fixed_objects_stats; Check Stale Statistics DBA_TAB_STATISTICS allows you to check stale statistics on a specific table SELECT owner, table_name, last_analyzed, stale_stats FROM dba_tab_statistics WHERE table_name='EMPLOYEES' and owner='HR'; DBA_IND_STATISTICS allows you to check stale statistics on specific index SELECT owner, table_name, index_name last_analyzed, stale_stats FROM dba_ind_statistics WHERE table_name='EMPLOYEES' and owner = 'HR'; Linux Script to Gather Stats Here is a Linux shell script to gather HR schema stats and generates a log file under /tmp location #!/bin/bash . /home/oracle/.bash_profile export ORACLE_HOME=/u01/app/oracle/product/19.3/db_home export ORACLE_BASE=/u01/app/oracle export ORACLE_SID=orcl export DATE=$(date +%y-%m-%d_%H%M%S) #### Gather HR schema stats #### sqlplus / as sysdba << EOF > /tmp/HR_stats_gather_$DATE.log EXEC DBMS_STATS.gather_schema_stats('HR'); EOF echo "Stats gathered succeeded" Further Read Scheduling Stats Gather Job with DBMS_SCHEDULER Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Generate Execution Plan in Oracle Visualize and optimize SQL execution plans in Oracle. While working on SQL performance tuning, you must know what execution plan optimiser is generating. This execution plan defines how the SQL statement will be executed. There are multiple methods to generate an SQL plan. You can choose the best method that suits you. AUTOTRACE – easy option EXPLAIN PLAN AUTOTRACE – easy option The autotrace option is used when you want to display the sql execution plan on your screen. The only problem with this option is that the sql query will execute first and then display the execution plan. This might typically be a problem if a SQL statement is taking longer to execute. You need wait until the poor performing sql completes and then will be able to look at the execution plan. set autotrace on; select * from emp; EXPLAIN PLAN The explain plan method does not require query to execute first. It will display the optimiser execution plan without even executing the sql statement explain plan for select * from emp; Run below to show the explain plan on the screen SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Shell Script to Ask User Favourite Games Create a script that interacts with users about their favorite games. In Linux bash shell script we can create a script to ask the user to input multiple entries and we can display a customized response after the user input. This will be achieved with a reading command to accept the entries and an echo command to display the entries with customizing options. vi favourite_game.sh # !/bin/bash # Favourite Games #Taking multiple inputs echo "Type your Favourite Games" read game1 read game2 read game3 read game4 read game5 read game6 echo "$game1 is your first choice" echo "$game2 is your second choice" echo "$game3 is your third choice" echo "$game4 is your fourth choice" echo "$game5 is your fifth choice" echo "$game6 is your sixth choice" Here is the sample output of the above script More ways to the user multiple entries You may use it where the user has more than one answers You can ask the user to enter more than one input and evaluate their inputs with if-else and respond accordingly. Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses