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

INDEX REBUILD TIME SQL

select sql_text,sql_id from gv$sqlstats where sql_text like '%SCHEMA.INDEX_NAME%';

abfxhr2v2bqgf 

select sid,serial# from gv$session_longops where sql_id='abfxhr2v2bqgf ';

SID             SERIAL#

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

2200           44444

select round(time_remaining/60) time_remaining_mins from gv$session_longops where sid='2200' and serial# = '44444';

TIME_REMAINING_MINS

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

33

DBMS_METADATA package to extract objects DDL

-----Always set below for getting full query result

set long 5000;

set pages 0;

-- 1. Get DDL for all Tables in the HR schema

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, 'HR')

FROM ALL_TABLES

WHERE OWNER = 'HR';

-- 2. Get DDL for all Indexes in the HR schema

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, 'HR')

FROM ALL_INDEXES

WHERE OWNER = 'HR';

-- 3. Get DDL for all Constraints in the HR schema

(Primary Key, Unique, Foreign Key, Check)

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', CONSTRAINT_NAME, 'HR')

FROM ALL_CONSTRAINTS

WHERE OWNER = 'HR' AND CONSTRAINT_TYPE IN ('P', 'U', 'R', 'C');

-- 4. Get DDL for all Sequences in the HR schema

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', SEQUENCE_NAME, 'HR')

FROM ALL_SEQUENCES

WHERE SEQUENCE_OWNER = 'HR';

-- 5. Get DDL for all Views in the HR schema

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME, 'HR')

FROM ALL_VIEWS

WHERE OWNER = 'HR';

-- 6. Get DDL for all Synonyms in the HR schema

SELECT DBMS_METADATA.GET_DDL('SYNONYM', SYNONYM_NAME, 'HR')

FROM ALL_SYNONYMS

WHERE OWNER = 'HR';

-- 7. Get all Roles assigned to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'HR') FROM DUAL;

-- 8. Get all System Privileges granted to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'HR') FROM DUAL;

-- 9. Get all Object Privileges granted to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'HR') FROM DUAL;

-- 10. Remove storage, tablespace, and segment attributes from DDL output

BEGIN

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

END;

/

-- DDL for Tables

SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, 'SCHEMA')  

FROM dba_tables u  

WHERE owner = 'SCHEMA'

AND u.table_name = 'OBJECT';  

-- DDL for Indexes

SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, 'SCHEMA')  

FROM dba_indexes u

WHERE owner = 'SCHEMA'

AND u.index_name = 'OBJECT';  

-- DDL for Views

SELECT DBMS_METADATA.GET_DDL('VIEW', u.view_name, 'SCHEMA')  

FROM dba_views u

WHERE owner = 'SCHEMA'

AND u.view_name = 'OBJECT';  

-- DDL for Sequences

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name, 'SCHEMA')  

FROM dba_sequences u

WHERE owner = 'SCHEMA'

AND u.sequence_name = 'OBJECT';  

-- DDL for Procedures

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'PROCEDURE'

AND u.object_name = 'OBJECT';  

-- DDL for Functions

SELECT DBMS_METADATA.GET_DDL('FUNCTION', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'FUNCTION'

AND u.object_name = 'OBJECT';  

-- DDL for Packages

SELECT DBMS_METADATA.GET_DDL('PACKAGE', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'PACKAGE'

AND u.object_name = 'OBJECT';  

-- DDL for Triggers

SELECT DBMS_METADATA.GET_DDL('TRIGGER', u.trigger_name, 'SCHEMA')  

FROM dba_triggers u

WHERE owner = 'SCHEMA'

AND u.trigger_name = 'OBJECT';  

-- DDL for Synonyms

SELECT DBMS_METADATA.GET_DDL('SYNONYM', u.synonym_name, 'SCHEMA')  

FROM dba_synonyms u

WHERE owner = 'SCHEMA'

AND u.synonym_name = 'OBJECT';  

-- DDL for Materialized Views

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', u.mview_name, 'SCHEMA')  

FROM dba_mviews u

WHERE owner = 'SCHEMA'

AND u.mview_name = 'OBJECT';  

-- DDL for Constraints

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', u.constraint_name, 'SCHEMA')  

FROM dba_constraints u

WHERE owner = 'SCHEMA'

AND u.constraint_name = 'OBJECT';  

--Get DDL commands for all objects in database for a Schema --

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, 'SCHEMA') FROM

dba_tables WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, 'SCHEMA') FROM

dba_indexes WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('VIEW', view_name, 'SCHEMA') FROM

dba_views WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', sequence_name, 'SCHEMA') FROM

dba_sequences WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA' AND object_type = 'FUNCTION'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA' AND object_type = 'PACKAGE'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name, 'SCHEMA') FROM

dba_triggers WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('SYNONYM', synonym_name, 'SCHEMA') FROM

dba_synonyms WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', mview_name, 'SCHEMA')

FROM dba_mviews WHERE owner = 'SCHEMA';

DBMS_STATS.GATHER_STATS for tables schemas indexes columns

 

Action	Full Command	Description
Update statistics for a specific table	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');	Updates statistics for the EMPLOYEES table in the HR schema.
Update statistics for a table and its indexes	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);	Updates statistics for the EMPLOYEES table and all its indexes.
Update statistics for a specific index	EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_SALARY_IDX');	Updates statistics for the EMP_SALARY_IDX index in the HR schema.
Update statistics for all tables in a schema	EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');	Updates statistics for all tables and indexes in the HR schema.
Update statistics for the entire database	EXEC DBMS_STATS.GATHER_DATABASE_STATS;	Updates statistics for all schemas and tables in the database. (Time-consuming on large DBs)
Check last statistics update for a table	SELECT table_name, last_analyzed FROM user_tables WHERE table_name = 'EMPLOYEES';	Shows the last time statistics were gathered for the EMPLOYEES table.
Check last statistics update for an index	SELECT index_name, last_analyzed FROM user_indexes WHERE table_name = 'EMPLOYEES';	Shows the last time statistics were gathered for indexes of EMPLOYEES.
Update statistics in parallel for faster execution	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', degree => 4);	Runs statistics gathering in parallel for faster performance (using 4 CPU threads).
Update statistics for specific columns	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', columns => ('SALARY'));	Updates statistics only for the SALARY column.

---

Best Practices:

✔ Use cascade => TRUE to update both table and indexes.
✔ Use degree => 4 for faster execution on large tables.
✔ Schedule statistics updates during off-peak hours.

Would you like a PL/SQL script to automate this for multiple tables? 😊

Flush shared_pool & buffer_cache

 Manually flush  buffer cache & shared pool cache without bouncing the database:-

For Standalone Databases 

alter system flush buffer_cache;

alter system flush shared_pool;

For RAC Databases 

alter system flush buffer_cache global;

alter system flush shared_pool global;

------- only do in case of system performance issue or maintenance issue ----

Flush shared pool meaning flushing cached execution plan and sql Quries from memory.

Flush buffer cache meaning flushing cached data from memory.

Database restart which internally flush both