Default Oracle accounts with default password

==========================================
Oracle accounts with default password

==========================================

SELECT * FROM DBA_USERS_WITH_DEFPWD ORDER BY 1;

USERNAME                       PRODUCT

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

APPQOSSYS

DBSFWUSER                      DB Service FireWall USER

DBSNMP

DIP

GGSYS

GSMCATUSER

ORACLE_OCM

OUTLN

REMOTE_SCHEDULER_AGENT         Oracle Scheduler

SYS$UMF                        Unified Manageability Framework

SYSRAC

WMSYS

XDB

=====================

Accounts Details

=====================

APPQOSSYS

Used for storing or managing all data and metadata required by Oracle Quality of Service Management.

DBSFWUSER

The account used to run the DBMS_SFW_ACL_ADMIN package.

DBMS_SFW_ACL_ADMIN package provides API for managing service-level Access Control Lists (ACLs). 

DBSNMP

Used by Management Agent of Oracle Enterprise Manager to monitor and manage the database.

DIP

Used by Directory Integration Platform (DIP) to synchronize the changes in Oracle Internet Directory with the applications in the database.

GGSYS

The internal account used by Oracle GoldenGate. It should not be unlocked or used for a database login.

GSMCATUSER

The account used by Global Service Manager to connect to the Global Data Services catalog.

ORACLE_OCM

This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.

OUTLN

Centrally manages metadata associated with stored outlines. Supports plan stability, which enables maintenance of the same execution plans for the same SQL statements.

REMOTE_SCHEDULER_AGENT

The account to disable remote jobs on a database. 

This account is created during the remote scheduler agent configuration. 

You can disable the capability of a database to run remote jobs by dropping this user.

SYSRAC

The account used to administer Oracle Real Application Clusters (RAC).

WMSYS

The account used to store the metadata information for Oracle Workspace Manager.

XDB

Used for storing Oracle XML DB data and metadata.

==========================================

These accounts, should be locked by default

==========================================

SELECT DBA_USERS.username, 

       DBA_USERS.account_status 

  FROM DBA_USERS, 

       DBA_USERS_WITH_DEFPWD

 WHERE DBA_USERS.username =  DBA_USERS_WITH_DEFPWD.username

 ORDER BY DBA_USERS.username;

USERNAME                       ACCOUNT_STATUS

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

APPQOSSYS                      EXPIRED & LOCKED

DBSFWUSER                      EXPIRED & LOCKED

DBSNMP                         EXPIRED & LOCKED

DIP                            EXPIRED & LOCKED

GGSYS                          EXPIRED & LOCKED

GSMCATUSER                     EXPIRED & LOCKED

ORACLE_OCM                     EXPIRED & LOCKED

OUTLN                          EXPIRED & LOCKED

REMOTE_SCHEDULER_AGENT         EXPIRED & LOCKED

SYS$UMF                        EXPIRED & LOCKED

SYSRAC                         EXPIRED & LOCKED

WMSYS                          EXPIRED & LOCKED

XDB                            EXPIRED & LOCKED

==========================================

To set account password to random password and lock account

==========================================

BEGIN 

  FOR r_user IN (SELECT username FROM DBA_USERS_WITH_DEFPWD WHERE username NOT LIKE '%XS$NULL%') LOOP 

    DBMS_OUTPUT.PUT_LINE('Password for user '||r_user.username||' will be changed.'); 

    EXECUTE IMMEDIATE 'ALTER USER ''||r_user.username||'' IDENTIFIED BY ''||DBMS_RANDOM.STRING('a',16)||''ACCOUNT LOCK PASSWORD EXPIRES'; 

  END LOOP;

END;

/

==========================================
Reference

==========================================

https://benchmarks.cisecurity.org/tools2/oracle/CIS_Oracle_Database_11g_R2_Benchmark_v2.2.0.pdf

Change Notification PL/SQL Example #2

Permissions

As SYS:
GRANT EXECUTE ON SYS.DBMS_CHANGE_NOTIFICATION TO XXX_YYYYY_ZZZZZ;

GRANT CHANGE NOTIFICATION TO XXX_YYYYY_ZZZZZ;

Objects to test Notification from PL/SQL:

CREATE TABLE NOTIFICATIONS (

  id NUMBER,

  message VARCHAR2(1000),

  notification_date DATE )

TABLESPACE IGT_TABLE;

CREATE SEQUENCE notifications_seq  NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE;

Firewall
Applicable if application is running on another server. 

Once registration was done, there should be an entry in DBA_CHANGE_NOTIFICATION_REGS

SELECT callback FROM DBA_CHANGE_NOTIFICATION_REGS

The result should be something like:

net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.40)(PORT=47632))?PR=0

net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.40)(PORT=47633))?PR=0

Each JVM would have is own port it is listening to.
Port numbers by default start at 47632, and incremented by 1

Ports 47632,47633, etc should be opened in Firewall.

From Oracle IP to the JVM IP (in this example, to IP 10.20.30.40)

Flow
1. Create Callback Procedure
2. Create registration between Callback Procedure and SQL that reflect a change.

3. Run DML + commit.
4. Check Notification took place, by checking Oracle DBA_CHANGE_NOTIFICATION_REGS and Application table (NOTIFICATIONS).

Callback Procedure

CREATE OR REPLACE PROCEDURE callback_proc (ntfnds IN SYS.CHNF$_DESC) IS
  regid NUMBER;
  tbname VARCHAR2(60);
  event_type NUMBER;
  numtables NUMBER;
  operation_type NUMBER;
  numrows NUMBER;
  row_id VARCHAR2(2000);
  l_message         VARCHAR2(4000) := NULL;
BEGIN
  regid := ntfnds.registration_id;
  numtables := ntfnds.numtables;
  event_type := ntfnds.event_type;
  --INSERT INTO nfevents VALUES(regid, event_type);
  l_message := 'Registration Level. regid : '||regid||', event_type: '||event_type;
  INSERT INTO NOTIFICATIONS (id, message, notification_date)
  VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
  COMMIT;

  IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN
    FOR i IN 1..numtables LOOP
      tbname := ntfnds.table_desc_array(i).table_name;
      operation_type := ntfnds.table_desc_array(i).opflags;
      --INSERT INTO nftablechanges VALUES(regid, tbname, operation_type);
      l_message := 'Table Level. regid: '||regid||', tbname : '||tbname||', operation_type: '||operation_type;
      INSERT INTO NOTIFICATIONS (id, message, notification_date)
      VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
      COMMIT;

      /* Optionally, send the table name and operation_type to a client-side listener using UTL_HTTP */
      /* If interested in ROWIDs, obtain them as follows */
      IF (BITAND(operation_type, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
        numrows := ntfnds.table_desc_array(i).numrows;
      ELSE
        numrows := 0;   /* ROWID INFO NOT AVAILABLE */
      END IF;
      /* The body of the loop is not executed when numrows is ZERO */
      FOR j IN 1..numrows LOOP
        row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
        --INSERT INTO nfrowchanges VALUES(regid, tbname, row_id);
        l_message := 'Row Level: regid: '||regid||', tbname : '||tbname||', row_id: '||row_id;
        INSERT INTO NOTIFICATIONS (id, message, notification_date)
        VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
        COMMIT;
        /* Optionally, send out ROWIDs to a client-side listener using UTL_HTTP */
      END LOOP;
    END LOOP;
  END IF;
  COMMIT;
END callback_proc;

Create the Registration with callback Procedure
DECLARE
  v_reg_info    SYS.CHNF$_REG_INFO;
  v_regid       NUMBER;
  v_upd_time    IPN_INVALIDATE_TRIGGER.ts_update_time%TYPE;
  qosflags      NUMBER;
BEGIN
  -- Create registration info: callback procedure, quality of service (include ROWIDs), operations filter (UPDATE only), timeout=0, purge_on_ntfn=0
  qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
  
  v_reg_info := SYS.CHNF$_REG_INFO('XXX_YYYYY_ZZZZZ.callback_proc', qosflags, 0, 0, 0);
 
  -- Begin registration
  v_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start(v_reg_info);
  -- Register the query (must return at least one row; limit to avoid multiple fetch errors)
  SELECT ts_update_time INTO v_upd_time FROM IPN_INVALIDATE_TRIGGER WHERE module_name = 'SUB_API';
  -- End registration
  DBMS_CHANGE_NOTIFICATION.reg_end;
  
  DBMS_OUTPUT.PUT_LINE('Registration ID for this query: ' || v_regid);
END;
/

PL/SQL procedure successfully completed

--Make the Update

UPDATE XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER SET ts_update_time = TO_CHAR(SYSDATE,'YYYYMMSS hh24:mi:ss') WHERE module_name = 'SUB_API';
1 row updated
COMMIT;

--Check Change Notification happened
SELECT * FROM DBA_CHANGE_NOTIFICATION_REGS
USERNAME         REGID REGFLAGS CALLBACK                                    OPERATIONS_FILTER CHANGELAG TIMEOUT    TABLE_NAME
--------------- ------ -------- ------------------------------------------
----------------- --------- ---------- ---------------------------------------
XXX_YYYYY_ZZZZZ   1520       13 plsql://XXX_YYYYY_ZZZZZ.callback_proc?PR=0                            0         0 4294967295 XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER

SELECT * FROM NOTIFICATIONS

        ID MESSAGE          
---------- --------------------------------------------------------------------------
NOTIFICATION_DATE
-----------------
         2 Registration Level. regid : 1520, event_type: 6                            20250809 16:16:28
         3 Table Level. regid: 1520, tbname : XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER,
20250809 16:16:28
         4 Row Level: regid: 1520, tbname : XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER, 20250809 16:16:28

If still not working, Debug with traces

Open trace files:

ALTER SYSTEM SET events '10867 trace name context forever, level 10';

 

Then: 

Recreate the activity

This should generate oracle tracing

Trace file would be generated under directory 'diagnostic_dest'

SELECT * FROM V$PARAMETER WHERE name = 'diagnostic_dest'

To turn of the trace files:

ALTER SYSTEM SET EVENTS '10867 trace name context off';

ORA-01041: internal error. hostdef extension doesn't exist. Change oracle to run in NOARCHIVEMODE

=================

Error
=================
SQL> SHUTDOWN IMMEDIATE;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>

=================

Issue

=================

Oracle cannot be started.

It was created in ARCHIVEMODE by mistake, with archive log dest folder missing.

Purpose: Change database to be NOARCHIVEMODE

SQL> STARTUP NOMOUNT;

SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0

Unsafe to proceed

ORA-03114: not connected to ORACLE

SQL> SHUTDOWN ABORT;

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

SQL> exit

=================

Solution
=================

Stop listener, which is preventing proper shutdown and start in NOMOUNT mode.

~> ps -ef | grep ora

oracle      1760       1  0 08:09 ?        00:00:00 /software/oracle/19c/bin/tnslsnr LISTENER -inherit

~> lsnrctl stop

~>  sqlplus / as sysdba

SQL> STARTUP NOMOUNT;

ORACLE instance started.

Total System Global Area 8589930736 bytes

Fixed Size                 13693168 bytes

Variable Size            4412407808 bytes

Database Buffers         4143972352 bytes

Redo Buffers               19857408 bytes

SQL> create pfile='/software/oracle/admin/igt/pfile/pfile.ora'

from spfile='/software/oracle/admin/igt/pfile/spfileigt.ora';

File created.

SQL> !cp /software/oracle/admin/igt/pfile/spfileigt.ora /software/oracle/admin/igt/pfile/spfileigt.ora_orig

SQL> !cp /software/oracle/admin/igt/pfile/pfile.ora /software/oracle/admin/igt/pfile/pfile.ora_orig

SQL> !vi /software/oracle/admin/igt/pfile/pfile.ora

Set log_archive_dest_1 to some valid path.

*.log_archive_dest_1='location=/some/valid/path'

SQL> CREATE SPFILE='/software/oracle/admin/igt/pfile/spfileigt.ora_new' FROM PFILE='/software/oracle/admin/igt/pfile/orig_pfile.ora';

File created.

SQL> SHUTDOWN IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> !mv /software/oracle/admin/igt/pfile/spfileigt.ora_new /software/oracle/admin/igt/pfile/spfileigt.ora

SQL>  STARTUP NOMOUNT;

ORACLE instance started.

Total System Global Area 8589930736 bytes

Fixed Size                 13693168 bytes

Variable Size            4412407808 bytes

Database Buffers         4143972352 bytes

Redo Buffers               19857408 bytes

SQL> ALTER DATABASE MOUNT;

Database altered.

SQL> ALTER DATABASE NOARCHIVELOG;

Database altered.

SQL> SHUTDOWN IMMEDIATE;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 8589930736 bytes

Fixed Size                 13693168 bytes

Variable Size            4412407808 bytes

Database Buffers         4143972352 bytes

Redo Buffers               19857408 bytes

Database mounted.

Database opened.

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE

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

NOARCHIVELOG

SQL> exit

Toyota Aygo Tyres Specs

Toyota Aygo The Tires Spec

155/65 R14 75 T

155 - The width of your tire in millimeters from sidewall to sidewall.

65 - The tire “profile”. The aspect ratio is a percentage that indicates the tire’s height vs. its width. 65, meaning that this tire’s height is 65% of its width

A lower aspect ratio is often indicative of a sportier, more performance-oriented tire.

A higher aspect ratio tends to indicate the tire was designed for other priorities like comfort or rugged durability.

R - Next you will find a single letter, either a “D”, a “B”, or an “R” which indicates the construction type for the tire. 

“R” indicates that the tire is a Radial tire 

“D” is a Diagonal (also known as “Bias”) ply tire 

“B” means it is a Belted-Bias ply tire.

Today, tires featuring Radial construction are by far the most common type. They are made with layers of fabric cords positioned at a 90’ angle to the center line of the tread. Radials became the dominant type of construction because of their superior fuel economy, traction, ride comfort and especially tread life when compared to earlier types of construction. 

14 - Rim diameter—sometimes also noted as the wheel diameter.  This measurement is in inches.

75 - Load index. The load index symbol indicates how much weight a tire can support.

   There is a table that translates code to weight.

    For example: 75 stands for 853 lbs / 387 kg

T - Speed Rating

   There is a table that translates code to speed.

   This indicates the top speed it’s safe to travel for a sustained amount of time. 

   A tire with a higher speed rating can handle heat better and provide more control at faster speeds. 

   T - Stands for 118 mph (189 kmh)

DBMS_SCHEDULER by example

==============================

Examples of using DBMS_SCHEDULER

==============================

Create Job

BEGIN 

 DBMS_SCHEDULER.CREATE_JOB ( 

  job_name => 'REFRESH_GSM_ROAMING_INFO_MV', 

  job_type => 'PLSQL_BLOCK', 

 job_action => 'BEGIN DBMS_MVIEW.REFRESH(''GSM_ROAMING_INFO_MV''); END;', 

 start_date => SYSDATE+1/1440, 

 repeat_interval => 'FREQ=HOURLY;INTERVAL=4', -- Run every 4 hours 

 enabled => TRUE, 

 comments => 'Refresh materialized view GSM_ROAMING_INFO_MV every 4 hours' ); 

END; 

/

Update Property

--Update job_action

BEGIN

    DBMS_SCHEDULER.SET_ATTRIBUTE(

        name      => 'MV_RF$J_0_S_326',

        attribute => 'job_action',

        value     => 'DBMS_REFRESH.REFRESH(''GSM_ROAMING_INFO_MV'');'

    );

END;

/

--Update repeat_interval

BEGIN

    DBMS_SCHEDULER.SET_ATTRIBUTE(

        name      => 'REFRESH_GSM_ROAMING_INFO_MV',

        attribute => 'repeat_interval',

        value     => 'FREQ=MINUTELY;INTERVAL=5'

    );

END;

/

Drop Job

DECLARE 

  CURSOR job_remove_cur IS

  SELECT job_name FROM USER_SCHEDULER_JOBS

   WHERE UPPER(job_action) LIKE '%GSM_ROAMING_INFO_MV%';

BEGIN

  FOR job_remove_rec IN job_remove_cur LOOP

    dbms_scheduler.drop_job(job_name => job_remove_rec.job_name);

    COMMIT;

  END LOOP;

END;

/

Monitor Job

SELECT job_action, 

       last_start_date,

       next_run_date, 

       failure_count, 

       run_count 

  FROM USER_SCHEDULER_JOBS 

 WHERE job_name = 'REFRESH_GSM_ROAMING_INFO_MV';

See difference in Oracle Profiles

==================

See current status

==================

COL default_profile FOR A10

COL app_prof_profile FOR A10

COL resource_name FOR A30

COL default_limit FOR A20

COL app_prof_limit FOR A40

set LINESIZE 160

SELECT DBA_PROFILES_DEFAULT.profile default_profile, 

       DBA_PROFILES_APP_PROF.profile app_prof_profile, 

       DBA_PROFILES_DEFAULT.resource_name resource_name,

       DBA_PROFILES_DEFAULT.limit default_limit, 

       DBA_PROFILES_APP_PROF.limit app_prof_limit

  FROM DBA_PROFILES DBA_PROFILES_DEFAULT,

       DBA_PROFILES DBA_PROFILES_APP_PROF

 WHERE DBA_PROFILES_DEFAULT.profile='DEFAULT'

   AND DBA_PROFILES_APP_PROF.profile='APP_PROF'

   AND DBA_PROFILES_DEFAULT.resource_name = DBA_PROFILES_APP_PROF.resource_name

   AND DBA_PROFILES_DEFAULT.limit <> DBA_PROFILES_APP_PROF.limit

ORDER BY 3,1,2;

DEFAULT_PR APP_PROF_P RESOURCE_NAME            DEFAULT_LIMI APP_PROF_LIMIT

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

DEFAULT    APP_PROF   IDLE_TIME                UNLIMITED    480

DEFAULT    APP_PROF   PASSWORD_LOCK_TIME       1            UNLIMITED

DEFAULT    APP_PROF   PASSWORD_ROLLOVER_TIME   0            DEFAULT

DEFAULT    APP_PROF   PASSWORD_VERIFY_FUNCTION NULL         ORA12C_STIG_VERIFY_FUNCTION

==================

Change settings:

==================

ALTER PROFILE APP_PROF LIMIT IDLE_TIME UNLIMITED;

ALTER PROFILE APP_PROF LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Details:

PASSWORD_ROLLOVER_TIME - DEFAULT - Is same as zero

PASSWORD_LOCK_TIME - When user becomes unlocked, after the specified number of consecutive failed login attempts

==================

Theory

==================

Password-Specific Settings in the Default Profile

Parameter: INACTIVE_ACCOUNT_TIME 

Default Setting Description: UNLIMITED

Description: Locks the account of a database user who has not logged in to the database instance in a specified number of days.

Parameter: FAILED_LOGIN_ATTEMPTS

Default Setting Description: 10

Description: Sets the maximum times a user try to log in and to fail before locking the account.

Note: You can set limits on the number of times an unauthorized user (possibly an intruder) attempts to log in to Oracle Call Interface (OCI) applications by using the SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter.

Parameter: PASSWORD_GRACE_TIME

Default Setting Description: 7

Description: Sets the number of days that a user has to change their password before it expires.

Parameter: PASSWORD_LIFE_TIME

Default Setting Description: 180

Description: Sets the number of days the user can use their current password.

Parameter: PASSWORD_LOCK_TIME

Default Setting Description: 1

Description: Sets the number of days an account will be locked after the specified number of consecutive failed login attempts.

After the time passes, then the account becomes unlocked. 

This user's profile parameter is useful to help prevent brute force attacks on user passwords but not to increase the maintenance burden on administrators.

Even after the value set by PASSWORD_LOCK_TIME shows that the password has expired, the DBA_USERS data dictionary view will show that the account is locked. 

However, after the user connects, the information in DBA_USERS is updated with the correct OPEN status.

Parameter: PASSWORD_REUSE_MAX

Default Setting Description: UNLIMITED

Description: Sets the number of password changes required before the current password can be reused.

Parameter: PASSWORD_REUSE_TIME

Default Setting Description: UNLIMITED

Description: Sets the number of days before which a password cannot be reused.

Parameter: PASSWORD_ROLLOVER_TIME

Default Setting Description: 0

Description: Enables the gradual database password rollover time.

Unable to obtain current patch information, Latest xml inventory is not loaded into table, RawInventory gets null OracleHomeInfo

==================

Error

==================

Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table

ORA-06512: at "SYS.DBMS_QOPATCH", line 2327

ORA-06512: at "SYS.DBMS_QOPATCH", line 854

ORA-06512: at "SYS.DBMS_QOPATCH", line 937

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at "SYS.DBMS_QOPATCH", line 932

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400: data cartridge error

KUP-04095: preprocessor command /software/oracle/19c/QOpatch/qopiprep.bat encountered error "LsInventorySession failed: RawInventory gets null OracleHomeInfo

"

ORA-06512: at "SYS.DBMS_QOPATCH", line 919

ORA-06512: at "SYS.DBMS_QOPATCH", line 2286

ORA-06512: at "SYS.DBMS_QOPATCH", line 817

ORA-06512: at "SYS.DBMS_QOPATCH", line 2309

==================

Debug

==================

oracle@hostname:/software/oracle/19c/OPatch>%./opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.41

Copyright (c) 2025, Oracle Corporation.  All rights reserved.

Oracle Home       : /software/oracle/19c

Central Inventory : /software/oracle/oraInventory

   from           : /software/oracle/19c/oraInst.loc

OPatch version    : 12.2.0.1.41

OUI version       : 12.2.0.7.0

Log file location : /software/oracle/19c/cfgtoollogs/opatch/opatch2025-05-25_08-12-01AM_1.log

List of Homes on this system:

  Home name= OraDB19Home1, Location= "/software/oracle/1910"

LsInventorySession failed: RawInventory gets null OracleHomeInfo

OPatch failed with error code 73

oracle@hostname:/software/oracle/19c/OPatch>%

==================

Fix

==================

Edit OraDB19Home1 to correct value /software/oracle/19c

vi /software/oracle/oraInventory/ContentsXML/inventory.xml

<HOME_LIST>

<HOME NAME="OraDB19Home1" LOC="/software/oracle/1910" TYPE="O" IDX="1"/>

</HOME_LIST>

<HOME_LIST>

<HOME NAME="OraDB19Home1" LOC="/software/oracle/19c" TYPE="O" IDX="1"/>

</HOME_LIST>