Oracle DBA

1) What is a Redo Transport Service in Oracle Data Guard? The Redo Transport Service is responsible for transmitting redo data from the primary database to the standby database. It ensures that changes made to the primary database are replicated to the standby database, keeping them synchronized. The two modes of redo transport are synchronous and asynchronous. 2) How is log transport initiated from the Primary to Standby database?    Log transport from the Primary to Standby database in Oracle Data Guard is initiated through a series of steps and mechanisms that ensure the redo data (changes made to the database) is reliably transmitted and applied to the standby database. Here’s how the process works: A) Redo Generation:    As transactions are committed on the primary database, redo entries are generated and written to the online redo log files. B) Log Writer Process (LGWR):    The Log Writer process is responsible for writing redo entries from the redo log  buffer in memory to the o

 1. What is Oracle Data Guard? Oracle Data Guard is a high-availability and disaster recovery solution provided by Oracle Corporation for Oracle databases. It is designed to ensure data availability, integrity, and reliability by maintaining a synchronized copy of a primary database at one or more remote standby databases. 2. Explain the purpose of Oracle Data Guard. Oracle Data Guard is a high-availability, disaster recovery, and data  protection solution for Oracle databases. Its primary purposes are: 1. Data Protection: Data Guard ensures that data is consistently  and accurately replicated from a primary database to one or  more standby databases. This replication helps protect against  data loss due to hardware failures, human errors, or other  unexpected disruptions. 2. High Availability: By maintaining standby databases that can  take over in the event of a primary database failure, Data Guard  helps ensure that database services are continuously available.  This minimizes downt

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 &

Oracle GoldenGate Initial Load Methods Oracle GoldenGate Initial Load Methods Initial Load Direct Load Overview: Initial Load Direct Load is a method that streams the initial data from the source to the target in real-time. This approach utilizes the Extract and Replicat processes to capture and apply data changes continuously. Continuous Capture and Apply: The Extract process reads the data from the source and sends it directly to the target Replicat process, which applies the data immediately. Real-Time Data Streaming: It ensures that the data is streamed in real-time, which is beneficial for scenarios where minimizing latency is crucial. No Intermediate Files: Unlike some other methods, there are no intermediate files or staging areas; the data moves directly from source to target. Transaction Consistency: Ensures transactional consistency, making it suitable for environments where maintaining

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_HOME). 

Oracle Recovery Manager (RMAN) is a robust utility designed for the backup, restoration, and recovery of Oracle databases. RMAN simplifies database management by offering comprehensive backup options, automatic recovery features, and seamless integration with Oracle Database. It supports full and incremental backups, point-in-time recovery, and database cloning. RMAN can be executed through command-line, integrated with Oracle Enterprise Manager, and offers advanced features like block media recovery, encryption, and compression, ensuring efficient and secure database management. One of the most tedious processes is resetting all RMAN configurations in a single command. If you want to clear configurations, use " CONFIGURE..CLEAR" . Sometimes, restoring a standby or duplicate database can fail due to configurations from the source database. Therefore, it's essential to reset all configurations beforehand using the fo

Goldengate initial load options - File to Replicat This method allows the Extract process to write to a file on the target system, which the Replicat applies using SQL INSERT statements.  The Extract process generates a file in a universal format for the Replicat to load. This approach fundamentally resembles the standard GoldenGate data replication method used for change data capture (CDC). During the initial load, you can enable CDC. In a live environment, you should use the SQLPREDICATE key to specify the SCN# to maintain data integrity. Please follow the below steps to enable goldengate initial load using File to Replicat method On source: Add initial load extract process. Create a parameter file. Verify task(initia