The script to start observer

StartObserver.sh

nohup /oracle/app/oracle/product/19.0.0/clienthome_1/bin/dgmgrl -logfile /tmp/observer.log sys/*****@myconndb "start observer file='/oracle/app/oracle/dgbroker19c/observer/myconndb.dat';" &

nohup ./StartObserver.sh &

RMAN Shell Script to backup to the disk

#!/bin/bash

BACKUP_DATE=$(date +%Y%m%d_%H%M%S)

BACKUP_DIR="/oracle/FRA/FULL_BKP_20250826"

ORACLE_HOME=/oracle/app/oracle/product/19.0.0/dbhome_1

ORACLE_SID=MYDB

export ORACLE_HOME ORACLE_SID RMAN_DIR PATH=$ORACLE_HOME/bin:$PATH

rman target / log=${BACKUP_DIR}/backup_${BACKUP_DATE}.log <<EOF

RUN {

  ALLOCATE CHANNEL C1 TYPE DISK FORMAT '${BACKUP_DIR}/%U';

  CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

  BACKUP TABLESPACE USERS FORMAT '${BACKUP_DIR}/tablespace_%U.bkp';

  BACKUP CURRENT CONTROLFILE FORMAT '${BACKUP_DIR}/control_before_patch_%U.ctl';

  #BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '${BACKUP_DIR}/db_full__before_patch_%U.bkp';

  #BACKUP ARCHIVELOG ALL FORMAT '${BACKUP_DIR}/archive__before_patch_%U.bkp';

  RELEASE CHANNEL C1;

}

EOF

Understanding Patches

Oracle Database 19c Proactive Patch Information (Doc ID 2521164.1)

Critical Patch Updates, Security Alerts and Bulletins

RU - Release Update

RUR - Release Update Revision

Can't take screenshots of a udemy course video in Windows

 Solution: To resolve the issue, please follow these steps:

1. Open the browser.

2. Go to "Settings" menu.

3. In the Settings menu, type "acceleration" in the search bar.

4. Disable this option.

5. Once disabled, relaunch the browser.

Check IPs routing

ggate:poctest_1:~/alex>netstat -rn

Kernel IP routing table

Destination     Gateway         Genmask         Flags   MSS Window  irtt Iface
0.0.0.0         10.197.20.1     0.0.0.0         UG        0 0          0 bond0
10.190.36.0     0.0.0.0         255.255.255.0   U         0 0          0 bond2
10.190.208.0    0.0.0.0         255.255.252.0   U         0 0          0 bond1
10.197.20.0     0.0.0.0         255.255.252.0   U         0 0          0 bond0
169.254.0.0     0.0.0.0         255.255.224.0   U         0 0          0 bond2

 

How to SSH Without a Password with Putty

https://www.youtube.com/watch?v=4jakCV5JYx0 

Export/Import - EXPDP/IMPDP

EXPDP

expdp sys/pass@"ggate-scan.srv.bmogc.net:1521/ggatedb as sysdba" attach=SYS_EXPORT_FULL_01

expdp  \"/ as sysdba\"@"ggate-scan.srv.bmogc.net:1521/ggatedb" attach=SYS_EXPORT_FULL_01

Attach at the server to the job

expdp  \"/ as sysdba\" attach=SYS_EXPORT_FULL_01

IMPDP

nohup impdp \"/ as sysdba\" parfile=test.par &

impdp system/*****  attach=SYS_IMPORT_FULL_01

impdp \"/ as sysdba\" attach=SYS_IMPORT_SCHEMA_01

How to validate A DataPump Export (EXPDP) Dump File?

impdp \"/ as sysdba\" DIRECTORY=alex_temp DUMPFILE=GGATE.expdp_ggtab_54tables_20231006_%U.dmp SQLFILE=test.sql 

Monitoring Scripts

::::::::::::::
ASM_check.sql
::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col db_unique_name for a10
spool 1_asm_data.log appendSELECT d.db_unique_name,v.name
diskgroup,round((total_mb/1024),1) "Total_GB",round((free_mb/1024),1) "FREE_GB",round(((total_mb-free_mb)/total_mb*100),1) as Percentage FROM V$asm_diskgroup v, v$database d;
spool off

exit

::::::::::::::
DB_check_PDB.sql
::::::::::::::

set line 220 pages 100 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col OPEN_MODE for a10
col OPEN_TIME for a40
col name for a10
col RESTRICTED for a12

spool pdb_1_highlevel.log append
select inst_id,name, OPEN_MODE,RESTRICTED,OPEN_TIME,(select round(sum(bytes)/1024/1024/1024,0) from dba_segments) size_gb from gv$PDBS;

spool off

spool 4_outstandingalerts.log append
col reason for a70
col creation_time for a35
col suggested_action for a70
select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;
spool off

spool 5_tablespace.log append
select name Database,tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

exit

::::::::::::::
DB_check_RO.sql
::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

col name for a10
col instance_name for a15
col host_name for a35
col open_mode for a21
col database_role for a17
col SWITCHOVER_STATUS for a20
col startup_time for a20
col flashback_on for a12
col processes for a15
col sessions for a15

spool 1_highlevel.log append

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,

(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value)||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions,(select round(sum(bytes)/1024/1024/1024,1) from dba_segments) "Size(GB)" from v$database a,v$instance b;

set heading off

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions 
from gv$database a,gv$instance b where a.inst_id=b.inst_id and a.inst_id=2;

spool off

spool 2_standbylag.log append

col source for a10
col target for a10
col primary_time for a20
col standby_TIME for a20

SELECT source, target, primary_log, standby_log,primary_log-standby_log LOG_GAP, sysdate primary_time,standby_TIME,round((sysdate-standby_TIME)*1440,0) Time_gap_min
FROM
(
SELECT db_unique_name source
FROM v$database
),
(
SELECT MAX(SEQUENCE#) primary_log
FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT name target,MAX(SEQUENCE#) standby_log,MAX(COMPLETION_TIME) standby_TIME
FROM v$ARCHIVED_LOG WHERE name='DSNASB' AND APPLIED='YES' group by name
)
where standby_TIME>sysdate-30
/

spool off

spool 3_otherinfo.log append

col spfile for a65
col last_DB_bkp for a20
col last_arc_bkp for a20
col MMON for a44

select
(select instance_name from v$instance) name,
(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and STATUS='COMPLETED' and OBJECT_TYPE='DB FULL' or OBJECT_TYPE='DB INCR') last_DB_bkp,(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and OBJECT_TYPE='ARCHIVELOG' and STATUS='COMPLETED') last_arc_bkp,(select DISPLAY_VALUE from v$parameter where name='spfile') spfile
from dual;

spool off

spool 4_outstandingalerts.log append

col reason for a70
col creation_time for a35
col suggested_action for a70

select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;

spool off

spool 5_tablespace.log append

select name Database,tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

spool 6_recoveryarea.log append

col RECOVERY_AREA_USAGE for a30

select i.instance_name as DB_NAME,flashback_on, (select count(*) from v$restore_point) RESTORE_POINTS, round((f.space_used/1024/1024/1024),0)||'/'||(f.space_limit/1024/1024/1024)||' ('||round((f.space_used/space_limit*100),1)||'%)' "RECOVERY_AREA_USAGE" from v$database d, v$instance i,v$recovery_file_dest f;

spool off

exit

::::::::::::::

DB_check.sql

::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col name for a10
col instance_name for a15
col host_name for a35
col open_mode for a21
col database_role for a17
col SWITCHOVER_STATUS for a20
col startup_time for a20
col flashback_on for a12
col processes for a15
col sessions for a15

spool 1_highlevel.log append

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions,(select round(sum(bytes)/1024/1024/1024,1) from dba_segments) "Size(GB)" from v$database a,v$instance b;

set heading off

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes, (select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions from gv$database a,gv$instance b where a.inst_id=b.inst_id and a.inst_id=2;

spool off

spool 2_standbylag.log append

col source for a10
col target for a10
col primary_time for a20
col standby_TIME for a20

SELECT source, target, primary_log, standby_log,primary_log-standby_log LOG_GAP, sysdate primary_time,standby_TIME,round((sysdate-standby_TIME)*1440,0) Time_gap_min
FROM
(
SELECT db_unique_name source
FROM v$database
),
(
SELECT MAX(SEQUENCE#) primary_log
FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT name target,MAX(SEQUENCE#) standby_log,MAX(COMPLETION_TIME) standby_TIME
FROM v$ARCHIVED_LOG WHERE DEST_ID in (select dest_id from v$archive_dest where destination is not null and dest_id!=1) AND APPLIED='YES' group by name
)
where standby_TIME>sysdate-30
/

spool off

spool 3_otherinfo.log append

col spfile for a65
col last_DB_bkp for a20
col last_arc_bkp for a20
col MMON for a44

select
(select instance_name from v$instance) name,(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and STATUS='COMPLETED' and OBJECT_TYPE='DB FULL' or OBJECT_TYPE='DB INCR') last_DB_bkp, (select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and OBJECT_TYPE='ARCHIVELOG' and STATUS='COMPLETED') last_arc_bkp,(select DISPLAY_VALUE from v$parameter where name='spfile') spfile 
from dual;

spool off

spool 4_outstandingalerts.log append

col reason for a70
col creation_time for a35
col suggested_action for a70
select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;

spool off

spool 5_tablespace.log append

select name Database, tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

spool 6_recoveryarea.log append

col RECOVERY_AREA_USAGE for a30

select i.instance_name as DB_NAME,flashback_on, (select count(*) from v$restore_point) RESTORE_POINTS, round((f.space_used/1024/1024/1024),0)||'/'||(f.space_limit/1024/1024/1024)||' ('||round((f.space_used/space_limit*100),1)||'%)' "RECOVERY_AREA_USAGE" from v$database d, v$instance i,v$recovery_file_dest f;

spool off

exit 

Statistics - DBMS_STATS

Table statistics:

DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SCOTT', tabname => 'DEPT', estimate_percent => NULL, method_opt =>'FOR ALL INDEXED COLUMNS SIZE AUTO', granularity =>'ALL', NO_INVALIDATE => TRUE, Cascade => TRUE);

Dynamic query

SELECT 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => '||CHR(39)||owner||CHR(39)||', tabname =>'||CHR(39)||object_name||CHR(39)||', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => '||CHR(39)||'FOR ALL COLUMNS SIZE AUTO'||CHR(39)||', cascade => TRUE);'
from dba_objects 
where object_name IN ('DEPT', 'EMP') and object_type = 'TABLE';

Database Statistics:

EXEC DBMS_STATS.gather_dictionary_stats;
EXEC DBMS_STATS.gather_system_stats;
EXEC DBMS_STATS.gather_fixed_objects_stats;
EXEC DBMS_STATS.gather_database_stats;

Schema statistics:

dbms_stats.gather_schema_stats( ownname => 'SCOTT', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',degree => DBMS_STATS.AUTO_DEGREE);

dbms_stats.gather_schema_stats( ownname => 'SCOTT_ADM', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',degree => DBMS_STATS.AUTO_DEGREE);

BEGIN 
DBMS_STATS.gather_schema_stats ( 
ownname => 'SCOTT', 
cascade => TRUE, 
options => 'GATHER STALE');
END; 
/ 

Recompile objects:

@?/rdbms/admin/utlrp.sql

Move non-OMF datafiles to OMF

SYS@poctest_1> sho parameter db_create_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
db_create_file_dest                  string

SYS@poctest_1> alter system set db_create_file_dest='+DATA01' scope=both;

System altered.

SYS@poctest_1> sho parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA01

col file_name for a70
set linesize 150 pages 150
select file_id, file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA01/POCTEST/system01.dbf
+DATA01/POCTEST/sysaux01.dbf
+DATA01/POCTEST/undotbs02.dbf
+DATA01/POCTEST/users01.dbf
+DATA01/POCTEST/test_data01
+DATA01/POCTEST/undotbs01.dbf

select 'PROMPT Moving file '||file_id||CHR(10)||'alter database move datafile '||file_id||';' from dba_data_files where file_name like '%.db%';

'ALTERDATABASEMOVEDATAFILE'||FILE_ID||';
---------------------------------------------------------------------alter database move datafile 1;
alter database move datafile 3;
alter database move datafile 5;
alter database move datafile 7;
alter database move datafile 2;
alter database move datafile 4;
alter database move datafile 8;

7 rows selected.

Elapsed: 00:00:00.03

SYS@poctest_1> alter database move datafile 1;

Database altered.

Elapsed: 00:00:15.43

SYS@poctest_1> alter database move datafile 3;

alter database move datafile 5;
alter database move datafile 7;
alter database move datafile 2;
alter database move datafile 4;
alter database move datafile 8;

Database altered.

SYS@poctest_1> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------------------
+DATA01/POCTEST/DATAFILE/system.2558.1152873171
+DATA01/POCTEST/DATAFILE/sysaux.1127.1152873193
+DATA01/POCTEST/DATAFILE/undotbs2.1169.1152873221
+DATA01/POCTEST/DATAFILE/users.1181.1152873225
+DATA01/POCTEST/DATAFILE/test.1157.1152873227
+DATA01/POCTEST/DATAFILE/undotbs1.2126.1152873379
+DATA01/POCTEST/DATAFILE/test1.1171.1152873393

7 rows selected. 

How to generate days, weeks, or months between two dates

select date'2021-06-08' + level - 1 dt
from   dual
connect by level <= (
  date'2023-12-14' - date'2021-06-08' + 1
);

 

Find table last modification date

select X.*  , Y.timestamp Last_updated_date from (
select     t.owner , table_name,     num_rows, TABLE_SIZE_IN_GB 
 from     dba_tables t
Inner join (select OWNER, segment_name,segment_type, sum(bytes/1024/1024/1024) TABLE_SIZE_IN_GB
from dba_segments
where OWNER in ('SCOTT', 'SCOTT_ADM')
group by OWNER,segment_name,segment_type ) s
  On table_name =segment_name and t.owner =s.owner ) X
  join dba_tab_modifications Y on X.owner = Y.TABLE_OWNER  and X.table_name = Y.table_name
  order by 4 desc , 3 desc ;

 

Find Resource Limit

select * from gv$resource_limit 
where RESOURCE_NAME in ( 'processes','sessions') ;

 

Generate - Rebuild Indexes

select 'alter index '||owner||'.'||index_name||' rebuild tablespace scott_index_tbs;'
from dba_indexes
where owner = 'SCOTT' and tablespace_name <> 'SCOTT_INDEX_TBS';

Rebuild LOB Index:

select 'alter table '||owner||'.'||table_name||' move tablespace SCOTT_DATA_TBS '||chr(10)||'LOB ('||'"'||column_name||'"'||') store as '||segment_name||chr(10)||'(tablespace SCOTT_INDEX_TBS);'
from dba_lobs where owner = 'SCOTT' and tablespace_name <> 'SCOTT_INDEX_TBS';

 

Generate "Drop all the objects in schema"

undefine owner
set pages 0
set lines 300
set heading off

spool /tmp/qwe123.sql

select 'drop table '||owner||'.'||table_name||' cascade constraints purge;'
from dba_tables
where owner = upper('&&owner')
union all
select 'drop '||object_type||' '||owner||'.'||object_name||';'
from dba_objects
where object_type not in ('TABLE','INDEX','PACKAGE BODY','TRIGGER','LOB')
and object_type not like '%LINK%'
and object_type not like '%PARTITION%'
and owner = upper('&&owner')
order by 1;

spool off

@/tmp/qwe123

PROMPT Count of Objects =

select object_type,count(*) from dba_objects where owner = upper('&&owner') group by object_type;

 

Generate Grant User privileges

select 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO '||grantee||';' 

from dba_tab_privs

where grantee = 'SCOTT'; 

Find Oracle Maintained (internal) users

 SYS @ GGATE1 >select username from dba_users where ORACLE_MAINTAINED ='Y' order by username;

USERNAME
--------------------------------------------------
ANONYMOUS
APPQOSSYS
AUDSYS
CTXSYS
DBSFWUSER
DBSNMP
DIP
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
MDSYS
OJVMSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
REMOTE_SCHEDULER_AGENT
SI_INFORMTN_SCHEMA
SYS
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM
MSYS
XDB
XS$NULL

30 rows selected.

How to define Oracle Prompt in SQL*Plus

cd $ORACLE_HOME/sqlplus/admin

vi glogin.sql

set sqlprompt "_user'@'_connect_identifier> " 

sqlplus / as sysdba

SYS @ GGATE1>

Enable/Disable Restricted Session

 SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          ALLOWED
         2 GGATE12          ALLOWED

-- Run the following command on each RAC node if applicable

SYS @ GGATE1>Alter system enable restricted session;

System altered.

SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          RESTRICTED
         2 GGATE12          ALLOWED

SYS @ GGATE1>Alter system disable restricted session;

System altered.

SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          ALLOWED
         2 GGATE12          ALLOWED

Define Unified Audit in Oracle 19c

Step 1: Run the following query. Value for below query should be TRUE.

select value from v$option where parameter = 'Unified Auditing';

Step 2 :  As sysdba  run the below

CREATE AUDIT POLICY SCOTT_DEPT_POL
  ACTIONS DELETE on SCOTT.DEPT,
          INSERT on SCOTT.DEPT,
          UPDATE on SCOTT.DEPT;

AUDIT POLICY SCOTT_DEPT_POL;