Gentlemen:
We have a single 19c database running on unix (AIX 7.2). We do not have OEM.
A third-party scheduling tool (Autosys) is used to call a shell script on the unix host which calls SQL*Loader four times, each time using control, parameter, and log file settings.
This combination has been running for years.
We recently began migrating the SQL*Loader task to PL/SQL (external tables) to replace the shell scripting and are running the old (shell) and new (PL/SQL) processes in parallel for testing.
During testing, I checked the Autosys error log output for the shell script process, for which Autosys indicated success, and discovered it was reporting the following errors:
SQL*Loader-128: unable to begin a session
ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
SQL*Loader-128: unable to begin a session
ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
SQL*Loader-128: unable to begin a session
ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
SQL*Loader-128: unable to begin a session
ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
SQL*Loader-128: unable to begin a session
ORA-01033: ORACLE initialization or shutdown in progress
SQL*Loader-128: unable to begin a session
ORA-01033: ORACLE initialization or shutdown in progress
SQL*Loader-128: unable to begin a session
ORA-01033: ORACLE initialization or shutdown in progress
SQL*Loader-128: unable to begin a session
ORA-01033: ORACLE initialization or shutdown in progress
Naturally, I was rather surprised to see the rather alarming messages about an archiver error and began investigating the state of the archive logging.
The SQL*Loader message is a false alarm as the shell script, including the SQL*Loader bit, executes as intended.
How much space do we have?
SQL> show parameter DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /rman/HPRDN/FRA
db_recovery_file_dest_size big integer 8000G
SQL>
I increased the size from 8000G to 10000G out of caution - there is ample available disk space:
SQL> show parameter DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /rman/HPRDN/FRA
db_recovery_file_dest_size big integer 10000G
SQL><
Clearly, Oracle is comparing db_recovery_file_dest_size to some value or values to determine if there is a space issue.
Being a novice Oracle DBA, I began looking for a dynamic view or views that would reveal the calculation, or at least the values used in such, but my efforts are yielding no answers.
Can you enlighten me as to how I can check, and thus monitor, these values?
Thank you.
By default, the database will store archivelogs in the Fast Recovery Area if you've set those parameters, so they're probably there. You can use these views to see what's in there currently:
select * from V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
+RECO 273804165120 8286896128 8264876032 233 0
select * from V$RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
CONTROL FILE 0.01 0 1 0
REDO LOG 0 0 0 0
ARCHIVED LOG 3.02 3.02 232 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
You can see details of each file by querying v$archived_log.
The SQL*Loader message is a false alarm as the shell script, including the SQL*Loader bit, executes as intended.There's an archiver error
somewhere, which either means the script connects to a different database or there are indeed some serious issues with your database.