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

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;

Create and Delete Oracle 19c database in Silent mode using DBCA

The example of rsp file you will find under Create and Delete commands!!

Create Database:

dbca -silent -createDatabase -responseFile /home/oracle/dbca_alex.rsp

Delete Database:

dbca -silent -deleteDatabase -sourceDB alex -sysDBAUserName sys -sysDBAPassword oracle

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