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;