Oracle Database Scripts : August 2025

Thursday, August 7, 2025

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`

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

Wednesday, August 6, 2025

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.