Oracle DBA

Automating DBA Tasks with Oracle GoldenGate In this post, we will explore how to automate DBA jobs with Goldengate. If you're reading this directly, I recommend checking out Part 1 first to understand how it works. If you've already read Part 1, feel free to continue from here! Or you are already familiar with goldengate you are good enough to continue. Steps to Automate User Management in Goldengate Configure Goldengate parameters and place the required shell and SQL scripts. Add the necessary input into GG_USERMGMT_TB, such as the username, database name, and granted roles. Verify that the user has been successfully created in the database. This setup is sufficient to bring your bright idea to life using your environment. Here, the E_E01_1 extract acts as the master process to automate DBA job user management operations. I have two databases, orclpdb01 and orclpdb02, with the master extract process configured and running on or...

Automating DBA Tasks with Oracle GoldenGate Oracle GoldenGate is widely recognized as one of the best tools for real-time data replication. It allows seamless database migrations, schema or table replication across homogeneous and heterogeneous environments, and enhances database performance by offloading reporting workloads. Additionally, it ensures high availability by enables Bi-directional or cascading database replication configurations. Beyond these well-known benefits, GoldenGate can also be leveraged for automating routine DBA tasks, reducing manual intervention and improving efficiency by Streamline Your DBA Tasks with EVENTACTIONS. Let's explore how you can leverage its EVENTACTIONS parameter to automate routine DBA tasks, reduce manual effort, and optimize workflows. In this post, I will demonstrate how GoldenGate can assist in automating key DBA operations, such as: Starting or stopping GoldenGate processes – Automate the m...

https://kcsdba.blogspot.com/2015/06/oracle-performance-tuning-interview.html https://kcsdba.blogspot.com/search/label/Interview%20Questions-DBA https://dbakeeda.blogspot.com/2016/05/performance-tuning-interview-questions.html https://ittutorial.org/performance-tuning-and-sql-tuning-tutorial-in-the-oracle-database/ http://www.dbvidya.com/oracle-performance-tuning-interview-questions-developers-dbas/ https://www.youtube.com/watch?v=A78c5Ywl7ow https://www.youtube.com/watch?v=6uXqH0f3aG4&list=PLtVgy5UPCu-eWY8ZwxksTZuxE8UC1GFML&index=6&t=1965s https://www.youtube.com/watch?v=Zd_0BNBsXwA https://datahubinsight.com/oracle-performance-tuning-interview-questions-p1.html https://www.freelists.org/post/oracle-l/Questions-for-a-Jr-DBA https://mindmajix.com/oracle-performance-tuning-interview-questions https://dbaclass.com/article/oracle-performance-tuning-interview-questions-and-answers/ https://www.linkedin.com/posts/aditya-chandak-24692825_here-are-the-sql-performance-tuning-intervi...

Copy Code -- ************************************************************************************************************ -- * Filename : user_privs.sql -- * Author : Saravanakumar K -- * Description : This script with show all system,table privs and granted role infor for a user -- * Usage : @user_privs.sql -- * Version : v1 -- ********************************************************************************************************** set echo off set define '&' define username = &1 set tab off verify off col USERNAME for a25 col ACCOUNT_STATUS for a15 col PROFILE for a30 col CREATED heading User|created_dt for a18 col PTIME heading Password|last|changed_dt for a18 col LTIME heading Password|last|locked_dt for a18 col EXPIRY_DATE heading Password|expire|date for a18 col default_tablespace heading Tablespace for a25 col "User account status" for a80 col ...

Copy Code -- ************************************************************************************************************ -- * Filename : db_size_details.sql -- * Author : Saravanakumar K -- * Description : This script with show all database physical size and average growth in Day and Week -- * Usage : @db_size_details.sql -- * Version : v1 -- ************************************************************************************************************* COL "Database Size" FORMAT a13 COL "Used Space" FORMAT a11 COL "Used in %" FORMAT a11 COL "Free in %" FORMAT a11 COL "Database Name" FORMAT a13 COL "Free Space" FORMAT a12 COL "Growth DAY" FORMAT a15 COL "Growth WEEK" FORMAT a15 COL "Growth DAY in %" FORMAT a18 COL "Growth WEEK in %" FORMAT a20 SELECT ( SELECT MIN(creation_tim...

Copy Code -- ************************************************************************************************************ -- * Filename : db_size.sql -- * Author : Saravanakumar K -- * Description : This script with show all database physical file info -- * Usage : @db_size.sql -- * Version : v1 -- ************************************************************************************************************* set lines 200 pages 999 COL "Database Total Size" FORMAT a20 COL "Free space" FORMAT a20 COL "Used space" FORMAT a20 SELECT name.name,round(SUM(used.bytes) / 1024 / 1024 / 1024) || ' GB' "Database Total 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 ( ...

Copy Code -- ************************************************************************************************************ -- * Filename : db_files.sql -- * Author : Saravanakumar K -- * Description : This script with show all database physical file info -- * Usage : @db_files.sql -- * Version : v1 -- ************************************************************************************************************* SET TERMOUT OFF; CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES SET TERMOUT ON; SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 250 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF SET TAB OFF COLUMN ft_sort NOPRINT COLUMN tablespace FORMAT a30 HEADING 'Tablespace Name / File Class' COLUMN filename FORMAT a100 HEADING 'Filename' COLUMN filesize FORMAT 9999999999999 HEADING 'File Size GB' COLUMN autoextensible FORMAT a4...

Copy Code -- ************************************************************************************************************ -- * Filename : datapump_info.sql -- * Author : Saravanakumar K -- * Description : This script with show progress of export/import -- * Usage : @datapump_info.sql -- * Version : v1 -- ************************************************************************************************************* set lines 200 pages 9999 col "SID,SERIAL#,Username" for a25 col message for a75 col STATE for a15 col JOB_MODE for a12 col OPERATION for a12 SELECT s.sid || ',' || s.serial# || ',' || dp.owner_name "SID,SERIAL#,Username", sl.sofar, sl.totalwork, dp.state, dp.job_mode, dp.operation, sl.message FROM gv$session s, gv$session_longops sl, dba_datapump_jobs dp WHERE s.sid = sl.sid A...

Copy Code -- ************************************************************************************************************ -- * Filename : dbuptime.sql -- * Author : Saravanakumar K -- * Description : This script with show db/instance uptime -- * Usage : @dbuptime.sql -- * Version : v1 -- ************************************************************************************************************* col inst for a80 heading 'Instance Info' SELECT rpad('Instance ID',15) || ' : '|| inst_id || CHR(10) || rpad('Instance Name',15)|| ' : '|| instance_name|| CHR(10) || rpad('Hostname',15)|| ' : '|| host_name|| CHR(10) || rpad('Version',15)|| ' : '|| version|| CHR(10) || rpad('Started At',15)|| ' : '|| TO_CHAR(startup_time,'DD-MON-YYYY HH24:MI:SS') || CHR(10) || rpad('Uptime',15)|| ...

Copy Code -- ************************************************************************************************************ -- * Filename : asm_candidate_disks.sql -- * Author : Saravanakumar K -- * Description : This script with list out all candidate disks -- * Usage : @asm_candidate_disks.sql -- * Version : v1 -- ************************************************************************************************************* set line 250 set pages 49999 col path format a50 SELECT b.name, b.group_number, b.header_status, b.path, b.os_mb/1024 size_disk_GB from v$asm_disk b where b.group_number=0; clear col

Copy Code -- ************************************************************************************************************ -- * Filename : asm.sql -- * Author : Saravanakumar K -- * Description : This script with show space utilization -- * Usage : @asm.sql -- * Version : v1 -- ************************************************************************************************************* SELECT name, round(total_mb/1024) total_gb, round(used_mb/1024,2) used_gb, round(free_mb/1024,2) free_gb, round(free_mb * 100 / total_mb,2) free_pct, round(used_mb * 100 / total_mb,2) used_pct, '[' || rpad( nvl( rpad( '#', round(round(used_mb * 100 / total_mb) / 10), '#' ), '_' ), 10, '_' ) || ']' || lpad( to_char( round(used_mb * 100 / total_mb) ...

Copy Code -- ************************************************************************************************************ -- * Filename : arch.sql -- * Author : Saravanakumar K -- * Description : This script with show archivelog dest values from parmeter -- * Usage : @arch.sql -- * Version : v1 -- ************************************************************************************************************* set echo off SET LINES 250 PAGES 49999 FEEDBACK OFF ECHO OFF COL value FOR a180 COL name FOR a30 SELECT inst_id, name, value FROM gv$system_parameter WHERE name IN ( 'log_archive_dest_1', 'log_archive_dest_2', 'log_archive_dest_3', 'log_archive_dest_state_1', 'log_archive_dest_state_2', 'log_archive_dest_state_3' ) ORDER BY 1,2 / clear col

Copy Code -- ************************************************************************************************************ -- * Filename : alert_path.sql -- * Author : Saravanakumar K -- * Description : This script with show alert logfile path and second session with give code to ssh in RAC -- * Usage : @alert_path.sql -- * Version : v1 -- ************************************************************************************************************* col Alert_Path for a120 select inst_id,value from gv$diag_info where name like '%Diag Trace%' order by 1; SELECT 'ssh ' || i.host_name || ' ' || 'cat ' || d.value || '/alert_' || i.instance_name || '.log > alert_' || i.instance_name || '.log' as "Alert_Path" FROM gv$diag_info d, gv$instance i WHERE d.name LIKE '%Diag Trace%' ...

Copy Code -- ************************************************************************************************************ -- * Filename : ses_all.sql -- * Author : Saravanakumar K -- * Create : 13-Aug-2018 -- * Last Update : 13-Aug-2018 -- * Description : It will provide database all session information exclueding background process -- * Usage : start ses_all.sql -- * Version : v1 -- ************************************************************************************************************* set pagesize 9999 set lines 700 tab off feedback on col ses_info for a12 heading 'SID,SER#|SesInfo' col program for a25 heading Program trunc col LOGON-TIME for a17 heading 'Logon-Time' col machine for a15 heading 'Machine/|Server Name' col username for a15 heading 'User Name' col osuser for a10 ...

This script will help you find database user information such as account status, creation date, password expiration date, and more. Copy Code set echo off set define '&' define username = &1 set tab off verify off col USERNAME for a25 col ACCOUNT_STATUS for a15 col PROFILE for a30 col CREATED heading 'User|created_dt' for a18 col PTIME heading 'Password|last|changed_dt' for a18 col LTIME heading 'Password|last|locked_dt' for a18 col EXPIRY_DATE heading 'Password|expire|date' for a18 col default_tablespace heading Tablespace for a25 col "User account status" for a80 set lines 250 pages 49999 SELECT d.name DB_NAME, du.username, du.default_tablespace, du.account_status, du.profile, to_char(du.created,'DD-MON-YY HH24:MI:SS') created, to_char(u.ptime,'DD-MON-YY HH24:MI:SS') ptime, to_char(du.expiry_date,'DD-MON-YY HH24:MI:SS') expiry_date,...

In Oracle, glogin.sql is a script that SQLPlus executes when it starts up. It stands for "global login script." This script is typically used to set environment variables, define aliases, customize SQLPlus settings, and perform other tasks that you want to happen automatically each time you start SQL*Plus.   Location: The glogin.sql script is usually in the SQL*Plus system-wide initialization directory. The exact location depends on your Oracle installation and environment settings. Common locations include $ORACLE_HOME/sqlplus/admin or $ORACLE_HOME/sqlplus/admin/glogin.sql.  Execution: When you start SQL*Plus, it automatically executes the glogin.sql script if it exists in the specified directory.  Usage: You can customize the glogin.sql script to perform various tasks such as:  Setting default environment variables (e.g., NLS_LANG, ORACLE_H...