DATE AND TIME

 Oracle DB:

Function -------------------	Description ------------------------------------------------	Time Zone Used -------------------------------
SYSDATE	Server date/time (from OS)	❌ Not affected by session TZ
SYSTIMESTAMP	Server timestamp with time zone	❌ Not affected by session TZ
CURRENT_DATE	Session date/time (no TZ shown)	✅ Affected by session TZ
CURRENT_TIMESTAMP	Session timestamp with time zone	✅ Affected by session TZ
LOCALTIMESTAMP	Session timestamp (no TZ info, but adjusted)	✅ Affected by session TZ

----------------------------------------------------------------------------------------------------------------

Linux: 

export TZ=<Time_Zone_Name>

[root@localhost ~]# export TZ=Asia/Kolkata

[root@localhost ~]# date

Thu Aug  7 05:32:26 PM IST 2025

Region -------------	Time Zone Name -----------------------	UTC Offset -------------------------------	Description / Area -------------------------
Africa	Africa/Johannesburg	UTC+02:00	South Africa
	Africa/Cairo	UTC+02:00	Egypt
	Africa/Nairobi	UTC+03:00	Kenya
	Africa/Lagos	UTC+01:00	Nigeria
America	America/Toronto	UTC-05:00 / -04:00 DST	Canada – Eastern Time
	America/Vancouver	UTC-08:00 / -07:00 DST	Canada – Pacific Time
	America/Edmonton	UTC-07:00 / -06:00 DST	Canada – Mountain Time
	America/Winnipeg	UTC-06:00 / -05:00 DST	Canada – Central Time
	America/St_Johns	UTC-03:30 / -02:30 DST	Canada – Newfoundland
	America/New_York	UTC-05:00 / -04:00 DST	USA – Eastern Time
	America/Chicago	UTC-06:00 / -05:00 DST	USA – Central Time
	America/Denver	UTC-07:00 / -06:00 DST	USA – Mountain Time
	America/Los_Angeles	UTC-08:00 / -07:00 DST	USA – Pacific Time
	America/Bogota	UTC-05:00	Colombia
Asia	Asia/Kolkata	UTC+05:30	India
	Asia/Dubai	UTC+04:00	UAE
	Asia/Tokyo	UTC+09:00	Japan
	Asia/Singapore	UTC+08:00	Singapore
	Asia/Shanghai	UTC+08:00	China
	Asia/Karachi	UTC+05:00	Pakistan
	Asia/Tehran	UTC+03:30	Iran
Europe	Europe/London	UTC+00:00 / +01:00 DST	UK
	Europe/Berlin	UTC+01:00 / +02:00 DST	Germany
	Europe/Paris	UTC+01:00 / +02:00 DST	France
	Europe/Moscow	UTC+03:00	Russia
	Europe/Istanbul	UTC+03:00	Turkey
Australia	Australia/Sydney	UTC+10:00 / +11:00 DST	New South Wales
	Australia/Perth	UTC+08:00	Western Australia
	Australia/Melbourne	UTC+10:00 / +11:00 DST	Victoria
UTC/GMT	Etc/UTC	UTC+00:00	Coordinated Universal Time
	Etc/GMT+5	UTC-05:00	Fixed offset (reverse sign!)
Canada TZ	Canada/Eastern (alias)	UTC-05:00 / -04:00 DST	Alias for America/Toronto
	Canada/Pacific (alias)	UTC-08:00 / -07:00 DST	Alias for America/Vancouver

---------------------------------------------------------------------------------------------------------------

Upgrade and Patching oracle

Upgrade:  

Path -----------------------------------------------------------------	Description ----------------------------------------------------
$ORACLE_BASE/cfgtoollogs/dbua/<SID>/	Main directory for DBUA logs per DB SID
$ORACLE_BASE/cfgtoollogs/dbua/<SID>/upgrade*.log	Main upgrade logs
$ORACLE_BASE/cfgtoollogs/dbua/<SID>/trace.log	Detailed trace of DBUA steps
$ORACLE_BASE/cfgtoollogs/dbua/<SID>/postupgrade/*.log	Logs of post-upgrade steps (script output)
$ORACLE_HOME/cfgtoollogs/dbua/	May also contain general DBUA logs (older versions)

-----------------------------------------------------------------------------------------------------------------------------

OPatch: 

Tool -------------	Log Location --------------------------------------------------------------------------------------------	Description ------------------------------------------
OPatch	$ORACLE_HOME/cfgtoollogs/opatch/	Main directory for opatch logs.
	$ORACLE_HOME/cfgtoollogs/opatch/opatch<timestamp>.log	Log file for each opatch execution.
OPatchAuto	$ORACLE_HOME/cfgtoollogs/opatchauto/	Root directory for all opatchauto sessions.
	$ORACLE_HOME/cfgtoollogs/opatchauto/<timestamp>/	Subfolder for a specific opatchauto run.
	$ORACLE_HOME/cfgtoollogs/opatchauto/<timestamp>/opatchauto<timestamp>.log	Main log for the auto-patching operation.
	Other files in the same folder	Logs for root.sh, database patching, rollback, etc.

-----------------------------------------------------------------------------------------------------------------------------

Locking Unlocking oraInventory

 

Action ---------------------	Command ---------------------------------------------	Description ----------------------------------------
Find oraInventory path	Check from oraInst.loc file:cat /etc/oraInst.loc Look for inventory_loc=	Tells you where the oraInventory directory is located (e.g., /u01/app/oraInventory).
Check lock status	ls -l $ORACLE_INVENTORY/locks/inventory.lck	Checks if the lock file already exists. Replace $ORACLE_INVENTORY with the actual path.
Manually lock oraInventory	touch $ORACLE_INVENTORY/locks/inventory.lck	Creates the lock file manually to prevent concurrent OUI or patch operations.
Manually unlock oraInventory	rm -f $ORACLE_INVENTORY/locks/inventory.lck	Removes the lock file to release the lock (⚠️ only if no install/patch is running).
Check if Oracle install/patch process is running	`ps -ef	grep -i oraInst`

---------------------------------------------------------------------------------------------------------------------------

Dataguard standby views

View / Table ------------------------------	Purpose / Usage -----------------------------------------------------------------------------------
v$dataguard_stats	Shows Data Guard apply/transport lag statistics.
v$dataguard_status	Displays Data Guard error/status messages.
v$dataguard_config	❌ Not a standard Oracle view (possibly custom).
v$dataguard_process	Shows active Data Guard background processes (RFS, MRP, etc.).
v$bgprocess	Lists all background processes running in the instance.
v$active_instances	Lists active instances in RAC environments.
v$active_services	Shows currently active services in the instance.
v$active_session_history	ASH data; samples active sessions for performance diagnostics.
dba_activity_table	❌ Not standard — likely custom. Use DBA_AUDIT_TRAIL for DML audit.
dba_app_errors	Lists compilation errors for PL/SQL programs.
dba_applications	Lists applications registered (e.g., Workspace Manager).
dba_ddl_locks	Shows schema-level locks (DDL operations).
dba_dml_locks	Displays row-level DML locks (e.g., UPDATE, INSERT).
dba_db_links	Lists all defined DB links and their properties.
dba_db_link_sources	Shows which users/objects use DB links.
dba_dependencies	Shows object dependencies (views, packages, tables).
dba_directories	Lists Oracle Directory objects (for UTL_FILE, Data Pump, etc.).
dba_goldengate_privileges	Lists users with Oracle GoldenGate replication privileges.
v$dbfile	Lists datafiles and status info for current instance.
v$tempfile	Lists tempfiles used by temporary tablespaces.
v$logfile	Lists redo log files and their group/size/status.
dba_data_files	Lists all permanent datafiles and their details.
dba_temp_files	Lists all tempfiles in temp tablespaces.
v$rman_configuration	Displays RMAN configuration parameters (retention, device type).
v$result_cache_objects	Lists cached results; check for cache flushes here.
v$passwordfile_info	Shows info about Oracle password file usage.
v$pdbs	Lists PDBs (Pluggable Databases) in a CDB environment.
v$parameter_valid_values	Lists valid values for init parameters (for validation).
v$obsolete_backup_files	Shows obsolete backups eligible for deletion by RMAN.
v$obsolete_parameter	Lists deprecated/obsolete init parameters.
v$instance_ping	RAC view; shows block ping stats between instances.
v$session_connect_info	Shows connection protocol/service per session.

---------------------------------------------------------------------------------------------------------------

RMAN BACKUP FOR PATCHING AND UPGRADES

make sure:

1) database in archive log mode:

select log_mode from v$database; 

archive log list;

-- look for how to make db in archivelog mode 

https://kayyumoracledba.blogspot.com/2023/05/enable-archive-log-mode-in-oracle.html

2) database in flashback_on mode:

select flashback_on from v$database;

--look for how to make db in flashback_on mode 

https://kayyumoracledba.blogspot.com/2023/05/enable-archive-log-mode-in-oracle.html

-- RMAN BACKUP Before Upgrades or patching -- 

run {

  backup incremental level 0 database tag='BKP_DB_B4UPGRADE';

  backup archivelog all not backed up delete input tag='BKP_ARC_B4UPGRADE';

  backup current controlfile tag='BKP_CONTROLFILE_B4UPGRADE';

  backup spfile tag='BKP_SPFILE_B4UPGRADE';

}

--GRP before upgrades or patching:-

-- Run this as SYSDBA

create restore point GRP_B4UPGRADE guarantee flashback database;

COL name FORMAT A30;

COL guarantee_flashback_database FORMAT A10;

COL time FORMAT A30;

SELECT

  name,

  scn,

  guarantee_flashback_database,

  time

FROM

  v$restore_point;

NAME	SCN	GUARANTEE_FLASHBACK_DATABASE	TIME
GRP_B4UPGRADE	123456789	YES	25-JUL-25 07.10.05.000000 PM

--to see in indian time zone 

COLUMN name FORMAT A30

COLUMN guarantee_flashback_database FORMAT A10

COLUMN ist_time FORMAT A30

SELECT

  name,

  scn,

  guarantee_flashback_database,

  CAST(

    FROM_TZ(CAST(time AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Kolkata'

    AS TIMESTAMP

  ) AS ist_time

FROM

  v$restore_point;

SELECT

  object_name,

  object_type,

  CAST(

    FROM_TZ(CAST(created AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata'

    AS TIMESTAMP

  ) AS ist_created

FROM

  dba_objects;

--To see if your session is using a different time zone than the DB:

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM dual;

DBTIMEZONE     SESSIONTIMEZONE

-------------  ----------------

+00:00         Asia/Kolkata

SELECT

  SYSTIMESTAMP AS original_time,

  FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata' AS converted_to_ist,

  FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE DBTIMEZONE AS back_to_db_tz

FROM dual;

ORIGINAL_TIME                        CONVERTED_TO_IST                BACK_TO_DB_TZ

-----------------------------------  ------------------------------- -------------------------------

25-JUL-25 14.45.00.000000 +00:00     25-JUL-25 20.15.00.000000 +05:30 25-JUL-25 14.45.00.000000 +00:00

-- For setting session for IST time zone 

ALTER SESSION SET TIME_ZONE = 'Asia/Kolkata';

-- To convert startup_time to timestamp ist 

SELECT 

  TO_CHAR(

    FROM_TZ(CAST(startup_time AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata',

    'dd-mon-yy hh:mi:ss am'

  ) AS startup_ist

FROM v$instance;

Column/Function	Respects Session Time Zone?	Type
startup_time	❌ No	DATE
SYSTIMESTAMP	✅ Yes	TIMESTAMP WITH TIME ZONE
CURRENT_TIMESTAMP	✅ Yes	TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP	✅ Yes	TIMESTAMP

To convert a DATE value to IST (Indian Standard Time) in Oracle, you need to:

1. Cast the DATE to TIMESTAMP (because DATE does not store time zone info).

2. Attach the database time zone using FROM_TZ.

3. Convert it to IST using AT TIME ZONE 'Asia/Kolkata'.

---

✅ General Syntax

sql
SELECT 
  FROM_TZ(CAST(your_date_column AS TIMESTAMP), DBTIMEZONE) 
  AT TIME ZONE 'Asia/Kolkata' AS converted_to_ist
FROM your_table;

Grants read-only access on all schemas tables in oracle

 SQL> BEGIN
  FOR x IN (select table_name from dba_tables where owner='SCHEMA_OWNER') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON PUNE.'|| x.table_name ||' TO <role_name>';
  END LOOP;
END;
/ 

SQL> CREATE USER <username> IDENTIFIED BY "<password>"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE "DEFAULT";  2    3    4

User created.

SQL> create role pune_ro;
role created.

SQL> show user;
USER is "SYS"
SQL> BEGIN
  FOR x IN (select table_name from dba_tables where owner='PUNE') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON PUNE.'|| x.table_name ||' TO pune_ro';
  END LOOP;
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> alter user <username> default role all;

User altered.

SQL> grant app_user_dbpull to <username> ;

Grant succeeded.


SQL> revoke select on pune.alert from <username>;

Revoke succeeded.

SQL> grant pune_ro to <username>;

Grant succeeded.

---

1. create a user 

1.1 alter user <username> default role all;

2. create a role

3. grant all tables select on access on schemas tables

4. grant the role to <username>

Add & Drop ASM DISKS

 

export oracle environment shell script

What you can achieve ?

1. sqlplus / as sysdba

2. rman target /

3. expdp ...

4. impdp ...

5. srvctl 

Identify the Purpose of Patching

 

SQL styling and formatting !!!!!

-- Increase the size for long DDL outputs

SET LONG 10000;           -- Allows displaying long DDL statements

SET LONGCHUNKSIZE 1000;   -- Controls how LONG data is fetched and displayed

-- Formatting options

SET LINESIZE 200;         -- Adjusts output width for better readability

SET PAGESIZE 100;         -- Controls the number of lines per page (0 removes headers)

SET TRIMOUT ON;           -- Removes trailing spaces from output

SET TRIMSPOOL ON;         -- Removes trailing spaces when spooling output

SET WRAP OFF;             -- Prevents line wrapping in output

-- Improve output structure

SET COLSEP '|';           -- Adds a column separator for better visibility

SET UNDERLINE '-';        -- Adds an underline separator under column headers

-- Display settings

SET SERVEROUTPUT ON;      -- Enables DBMS_OUTPUT messages

SET VERIFY OFF;           -- Suppresses display of substitution variable values

SET FEEDBACK OFF;         -- Removes row count messages from the output

SET HEADING ON;           -- Ensures column headers are displayed

SET ECHO OFF;             -- Hides command execution in the output

-- If using spooling (saving output to a file)

SPOOL my_output.txt;

-- Your SQL queries here...

SPOOL OFF;

--For DDL Queries:

SET LONG 10000;

SET PAGESIZE 0;

SET LINESIZE 200;

SET TRIMOUT ON;

SET WRAP OFF;

--For Tabular query result

SET PAGESIZE 100;

SET LINESIZE 200;

SET COLSEP '|';

SET UNDERLINE = '-';

------------------------------------------

/* For exporting result to a file */

SET LONG 10000;

SET PAGESIZE 0;

SET LINESIZE 200;

SET TRIMOUT ON;

SET WRAP OFF;

SPOOL my_output.txt;

-- Your queries here...

SPOOL OFF;

--------------------------------------------

2025-03-12 00:11:11 ERROR OGG-01169 Encountered an update where all key columns for target table SCHEMA.TABLE_NAME are not present

 To find primary key of a table:

SQL>

select cols.column_name

from dba_constraints cons

join dba_cons_columns cols

on cons.sonstraint_name = cols.constraint_name

where cons.constraint_type in ('U','P')

and cons.owner = '&SCHEMA'

and cons.table_name = '&TABLE_NAME';

to get ddl for indexes for a table on source :

SQL>

set long 5000;

set pages 0;

select dbms_metadata.get_ddl('INDEX',u.index_name,'SCHEMA') from dba_indexes u where

table_name='&table_name' ;

STEPS TO PERFORME ON TARGET DB AND SOURCE DB:

1) target gg db:

GGSCI > sh cat dirrpt/REPLICAT_NAME.rpt

2025-03-12 00:11:11 ERROR OGG-01169 Encountered an update where all key columns for

target table SCHEMA.TABLE_NAME are not present

2) GGSCI > edit param REPLICAT_NAME

...

2.1 on target db checked for primary key and unique key columns

    then take the ddl for the indexes on the table SCHEMA.TABLE_NAME on source db

2.2 create source indexes on  target

2.3 then add below lines in REPLICAT_NAME.prm

    map SCHEMA.TABLE_NAME, target SCHEMA.TABLE_NAME, keycols (column_pk,column_uk) ;

2.4 start target abended REPLICAT REPLICAT_NAME

    GGSCI > start REPLICAT_NAME