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';