ORA-38824 for trigger FLOWS_FILES.wwv_biu_flow_file_objects.

 ORA-38824 for trigger FLOWS_FILES.wwv_biu_flow_file_objects.

 

Issue Faced:

After installing apex 20.2 some of the APEX functions were not working as expected. For example,we are not able to import applications using the APEX GUI.

Below image shows the error faced while importing applications.

 

 

 

Root Cause:

 

 After analyzing we found that the trigger WWV_BIU_FLOW_FILE_OBJECTS becomes INVALID since there is a old version of apex already existed and installation is considered as upgrade.

 

Solution:

 

1. As SYS, drop the FLOW_FILES trigger WWV_BIU_FLOW_FILE_OBJECTS.

 

Sql> drop trigger FLOWS_FILES.wwv_biu_flow_file_objects;

 

2. Go to the apex/core directory of the APEX   19.1 installation media.

 

    This script is version dependent and you must use the script for the version of APEX installed in the database. If necessary, download a new copy of the installation media to the server.

$cd /apex/core s

 

3. Start sqlplus as SYS. 

$sqlplus / as sysdba

 

4. Recreate the trigger using the file flows_files_new2.sql (found in apex/core).

Execute below script

 

@flows_files_new2.sql

This script create a new trigger.

 

5. Validate APEX using below statement: 

 

exec sys.validate_apex

 

To avoid this type for error kindly check is there any apex schemas available in that database if it is there drop that old schema and upgarade the apex and do the fresh installation

 

DROP USER APEX_050000 CASCADE;

Oracle ASM standalone configuration

Oracle ASM standalone configuration

Standalone Database:
The Oracle Grid Infrastructure for a standalone server is the Oracle software that provides system support for an Oracle database including volume management, file system, and automatic restart capabilities.

ASM server:
          Automatic Storage Management (ASM) is an integrated, high-performance database file system and disk manager.

 Steps:

l Adding disk group in Virtual Machine

l Grid software Installation

l Oracle software installation

l ASM standalone DB using DBCA tool.

Adding Disk Group in Virtual Machine:

Step 1: Select virtual machine hardware menu and click add button:

Step 2:- Choose the independent option and click next:

Step 3:- Create new virtual disk and click next:

Step 4:- Choose allocate all disk space now option:

Step 5:-  Select the folder name for creating Disk

 

Finally ASM Disk added to the VM

Step 6:- Disk Partion in OS level

Logon to the VM  and check fdisk -l

Partition the disks:
fdisk/dev/sdb

Configure ASMLib using the following command.
oracleasm configure -i

create disk in OS level
/usr/sbin/oracleasm init
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1

GRID installation pre requests:

Step1 :Check pre-install
Yum search preinstall

yum install oracle-database-preinstall-19c.x86_64 -y

yum install kmod-oracleasm.x86_64

yum install oracleasm-support.x86_64

Create a grid home directory and unzip the grid home:

Add asm groups:
[root@asm ~]# groupadd -g 54327 asmdba
[root@asm ~]# groupadd -g 54328 asmoper
[root@asm ~]# groupadd -g 54329 asmadmin
usermod -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmdba,asmoper,asmadmin,racdba oracle

Install the package cvudisk :
rpm -ivh /u01/app/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm

Run the script:

 

 

 

 

 

 

 

 

 

Oracle Grid software installation:

Step1: Click oracle restart method:

 

Step 2: Give any disk group name and redundancy as external change the path as /dev/oracleasm/disks

Step 3: Select the disk path

Step 4: Set the password for ASM

Step 5: Check the installation path

Step 6 :Check the inventory path

Step 7: Give root password to run the script automatically:

Step 8:Summary of grid installation:

Step 9: Running the installation

Completed

 

 

 

Oracle Software installation

Unzip the oracle 19c software in home location

Run the ./runInstaller

Step1:- Choose set up software only option.

Step 2:- Select single instance database installation

Step 3: Choose enterprise edition:

Step 4:-Summary of installation

 

Step 5:- Oracle software installation completed

ASM Standalone Database creation using DBCA Tool:

Step 1: - Select create database:

Step 2: -Give sid name and create admin password also select container database and mention the db name

Step 3: Check the asm configuration

 

Step 4:-Summary of db creation

Step 5 :- DB creation complete

Step 6 - Check the ASM standalone database

ASM configured in 19c database.

 

 

 

 

 

 

 

 

Oracle database administration tools

Oracle database administration tools 

Oracle provides a variety of database administration tools designed to help Database Administrators (DBAs) manage, monitor, and optimize Oracle databases efficiently. Here are some of the key tools:

1. Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager (OEM) is a comprehensive management tool that provides a web-based interface for managing Oracle databases. Key features include:

· Database Monitoring: Real-time monitoring of database performance, availability, and security.

· Performance Tuning: Tools like the Automatic Database Diagnostic Monitor (ADDM) and SQL Tuning Advisor.

· Configuration Management: Managing database configurations and changes.

· Backup and Recovery: Integrated with RMAN for backup and recovery operations.

· Job Scheduling: Automating administrative tasks through job scheduling.

· Reporting: Generating detailed reports on various aspects of the database.

2. Oracle SQL Developer

Oracle SQL Developer is a free integrated development environment (IDE) that simplifies the management of Oracle databases. It includes features like:

· Database Administration: Managing users, roles, and storage.

· SQL Development: Writing, debugging, and executing SQL and PL/SQL scripts.

· Data Modeling: Designing and managing database schemas.

· Data Migration: Tools for migrating data from other databases to Oracle.

3. Oracle Recovery Manager (RMAN)

Oracle Recovery Manager (RMAN) is a powerful tool for backup and recovery of Oracle databases. Key functionalities include:

· Automated Backup and Recovery: Simplifies and automates the backup and recovery process.

· Incremental Backups: Efficient backup strategies using incremental backups.

· Block-level Recovery: Recovering individual data blocks instead of entire files.

· Database Duplication: Creating duplicate copies of databases for testing and development.

4. Oracle Data Pump

Oracle Data Pump provides high-speed data movement between databases. It includes:

· Export (expdp): Exporting data and metadata from the database.

· Import (impdp): Importing data and metadata into the database.

· Network Mode: Direct data transfer between databases over a network.

5. SQL*Plus

SQL*Plus is a command-line tool for executing SQL and PL/SQL commands. It is useful for:

· Scripting: Automating tasks through SQL scripts.

· Interactive Queries: Running ad-hoc queries and updates.

· Database Management: Performing administrative tasks like startup and shutdown.

6. Oracle ASM (Automatic Storage Management)

Oracle ASM simplifies storage management by automating the placement and management of database files. Features include:

· Striping and Mirroring: Enhances performance and data redundancy.

· Disk Group Management: Simplifies the management of disk groups.

· Automatic Rebalance: Automatically redistributes data when disks are added or removed.

7. Oracle Real Application Testing (RAT)

Oracle Real Application Testing helps test the impact of database changes. It includes:

· Database Replay: Captures production workload and replays it on a test system.

· SQL Performance Analyzer: Analyzes the impact of changes on SQL performance.

8. Oracle Cloud Infrastructure (OCI) Console

For cloud-based databases, the OCI Console provides management tools for Oracle Cloud databases. Features include:

· Instance Management: Provisioning and managing Oracle Database instances in the cloud.

· Performance Monitoring: Monitoring cloud database performance and health.

· Backup and Recovery: Cloud-based backup and recovery solutions.

9. Oracle Universal Installer (OUI)

Oracle Universal Installer (OUI) is used for installing Oracle software. It includes:

· Installation: Guiding through the installation process of Oracle databases and related software.

· Configuration: Setting up initial configuration parameters.

10. Oracle Enterprise Manager Command Line Interface (EM CLI)

EM CLI allows for command-line interaction with Oracle Enterprise Manager, enabling scripting and automation of management tasks.

The architecture of Oracle Database 19c

 The architecture of Oracle Database 19c 

1. Database Instance

A database instance consists of the Oracle software running on a server and the memory structures and background processes that manage the database.

Memory Structures

System Global Area (SGA): A shared memory region that contains data and control information for the Oracle database. Key components of the SGA include:

o Database Buffer Cache: Stores copies of data blocks read from the data files.

o Shared Pool: Caches SQL statements, PL/SQL code, and data dictionary information.

o Redo Log Buffer: Stores redo entries that contain a log of changes made to the database.

o Large Pool: Used for large memory allocations, such as backup and restore operations.

o Java Pool: Used for all session-specific Java code and data within the database.

o Streams Pool: Used by Oracle Streams for data replication and capture.

Program Global Area (PGA): A memory region that contains data and control information for a single server process or background process. The PGA is private to each process.

Background Processes

· Database Writer (DBWn): Writes modified blocks from the database buffer cache to the data files.

· Log Writer (LGWR): Writes redo log entries from the redo log buffer to the online redo log files.

· Checkpoint (CKPT): Signals DBWn to write data to disk and updates the control file and data file headers with checkpoint information.

· System Monitor (SMON): Performs crash recovery when the instance is started after a failure.

· Process Monitor (PMON): Cleans up failed user processes and releases resources.

· Archiver (ARCn): Copies redo log files to the archive location when they are full.

· Recoverer (RECO): Resolves distributed transaction failures.

· Shared Server (Snnn): Handles multiple user processes in a shared server environment.

· Queue Monitor (QMNn): Manages Oracle Streams and Advanced Queuing.

2. Physical Database Structures

The physical storage structures of an Oracle database include:

· Data Files: Store the actual data of the database.

· Control Files: Contain metadata about the database, including the structure of the database and the locations of data files.

· Redo Log Files: Store a log of changes made to the database to enable recovery in case of a failure.

· Archive Log Files: Copies of redo log files that can be used for media recovery and to keep a history of changes.

3. Logical Database Structures

Oracle organizes data into logical structures to facilitate data management and access:

· Tablespaces: Logical storage units that group related data files together. Each tablespace can contain multiple segments.

· Segments: Set of extents allocated for a specific logical storage structure, such as a table or index.

· Extents: Contiguous blocks of storage within a tablespace.

· Data Blocks: The smallest unit of storage in an Oracle database, corresponding to a specific number of bytes of physical database space on disk.

4. Data Access Structures

· Tables: Store data in rows and columns.

· Indexes: Provide fast access to rows in a table.

· Views: Virtual tables based on the results of a query.

· Synonyms: Aliases for database objects, providing a layer of abstraction.

5. Networking and Interoperability

· Oracle Net: The network layer that allows communication between the Oracle database and client applications. It handles connections, authentication, and data transfer.

6. Oracle Managed Files (OMF)

Oracle Managed Files simplifies database file management by automating the creation and deletion of database files. With OMF, the DBA specifies file system locations, and Oracle automatically names and manages the files.

7. Multitenant Architecture

Oracle 19c supports the multitenant architecture, which allows multiple pluggable databases (PDBs) to be managed within a single container database (CDB). This architecture provides efficient resource management and isolation of different databases while allowing them to share the same instance and infrastructure.

Summary

The architecture of Oracle Database 19c is designed to support efficient data management, high availability, and robust security. By understanding these key components and their interactions, DBAs can effectively manage and optimize their Oracle database environments.