DEV Community: Abdelhak

Migrating from Oracle Discoverer to Oracle Analytics Server

Abdelhak — Tue, 05 Nov 2024 15:12:51 +0000

Migrating from Oracle Discoverer to Oracle Analytics Server

1. Objective

The objective of migrating from Oracle Discoverer to Oracle Analytics Server is to provide a more modern, robust, and scalable platform for analysis and reporting. This migration aims to enhance the capabilities and user experience for those relying on Oracle's tools for business intelligence and data analysis.

2. Migration Context

Oracle Discoverer has long been the go-to ad hoc operational reporting and analysis module for Oracle ERP customers. However, Oracle Discoverer reached its end-of-life status a few years ago, meaning Oracle has ceased offering support for this product. The implications of end-of-life status include:

Lack of Product Updates: No further updates, security patches, or data management enhancements will be released.
Security Concerns: The product will no longer obtain security certifications with new third-party products/versions or other Oracle products.
Compatibility Issues: Users may face problems with Oracle E-Business Suite (EBS) and Windows (IE) upgrades.

In contrast, Oracle Analytics Server offers an integrated suite of business intelligence tools designed for interactive reports, dashboards, and published reports. Key features include:

Advanced Visualization: Utilizes graphical grammar to render data using a variety of visualization techniques.
Multiple Data Sources: Capable of integrating data from multiple sources.
Predictive and Machine Learning Models: Built-in predictive analytics and machine learning models for enhanced data analysis.

3. Architecture and Configuration

3.1 Current Configuration

Component	Details
Application Server	DISCO
OS	AIX POWER 7.2
Architecture	Power PC 64 Bits
Database	12c Release 1 (EBS)

3.2 Target Configuration

Component	Details
Application Server	Latest version supported by Redhat
OS	Red Hat Enterprise Linux Server (latest certified version)
Architecture	X86 64 Bits
Database	19c (repo OAS)
Environments	Production, Recette

4. Oracle Analytics Server Features

Improved Flexibility: OAS offers a wider range of reporting options compared to Discoverer, including the ability to create interactive dashboards and visualizations.
Enhanced Security: OAS is a more secure solution than Discoverer, featuring role-based access control and data encryption.
Easier Integration with Other Oracle Products: OAS integrates seamlessly with other Oracle products such as Oracle E-Business Suite and Oracle Fusion Applications, facilitating data and report sharing within the organization.

Key Functionalities

Connect: Connect to multiple data sources.
Model: Merge heterogeneous sources to create a unified business semantic model.
Prepare: Transform, enrich, and exploit data based on specific analytical needs.
Explore: Create interactive visualizations and use natural language and Machine Learning to discover insights.
Experience: Develop and consume highly stylized interactive dashboards and reports.
Collaborate: Share content with others and collaborate to achieve optimal results.

Conclusion

Migrating from Oracle Discoverer to Oracle Analytics Server represents a significant transition to a new, more capable system. This move will empower users with cutting-edge tools for better data analysis and decision-making capabilities, ensuring that businesses can stay competitive in an increasingly data-driven world.

Migrating from Oracle Discoverer to Oracle Analytics Server Step by Step

1. Installation of Oracle Analytics Server

4.1 Prerequisites for Oracle Analytics Server

Java JDK1.8 or higher

[appoas@oasint01 root]$ java -version
openjdk version "1.8.0_392"
OpenJDK Runtime Environment (build 1.8.0_392-b08)
OpenJDK 64-Bit Server VM (build 25.392-b08, mixed mode)

JAVA PATH: /usr/jdk1.8.0_371/

Oracle Database 19C or higher

[appoas@oasint01 dbhome_1]$ pwd
/apps/oracle/product/19.0.0/dbhome_1
cat /etc/oratab
hostname           Instance name   Database pluggable name   port
OASPRD01.bkam.ma   OASPRD          OASPDB1                  1543

Minimum of 16 GB of memory and 50 GB of disk space

NOTE: A snapshot of this VM will be taken, which can be used in the future for deployment in production.

4.2 Verification of Prerequisites

After installing the RDA (Remote Diagnostic Agent) tool, execute RDA in diagnostic mode using the following command:

/orabkp/OASPRD/Tools/rda

This will start the prerequisite verification process for Oracle Analytics Server.

Directories:

/apps/OAS/
/apps/oracle/

Capacity Planning: Oracle Database Capacity Planning Report

Abdelhak — Mon, 23 Sep 2024 07:39:46 +0000

Oracle Enterprise Manager 13c OEM

This report provides a detailed overview of the replication and capacity planning for Oracle databases. It includes critical metrics such as the number of replicated databases, archive logs generated, and specific details for each instance.

Capacity Planning Details

Summary

Number of Replicated Databases	Archive Logs Generated (MB)
29	2254.74

Detailed Report

Timestamp	Instance Name	Primary Sequence	Max Transfer Sequence	Transfer Gap	Apply Gap	Gap Hours	Archive Generated (MB)	Error DG
2024-02-02T16:02:00.000+01:00	Instance1	11137	0	11137	11137	0	305.38	0
2024-02-02T15:58:00.000+01:00	Instance2	81	0	81	81	0	0	0
2024-02-02T16:02:00.000+01:00	Instance3	544556	544555	1	1	0	6.65	16
2024-02-02T17:01:00.000+01:00	Instance4	29325	29325	0	1	17	0	0
2024-02-02T17:01:00.000+01:00	Instance5	11942	11942	0	1	13	0	0
2024-02-02T17:01:00.000+01:00	Instance6	7113	7113	0	1	20	0	0
2024-02-02T17:01:00.000+01:00	Instance7	16612	16612	0	1	19	0	0
2024-02-02T16:02:00.000+01:00	Instance8	286147	286147	0	1	2	2.27	0

Behind the Scenes

Collaborating with the Database Team

Creating this report was a collaborative effort. The database team provided the necessary SQL queries for monitoring the database replication. My role was to tweak and optimize these queries to ensure they ran efficiently.

Transitioning to Oracle Analytics

A significant part of this project was transitioning the existing queries from Oracle BI Publisher to Oracle Analytics. The data sources for these reports were from Oracle Enterprise Manager (OEM), providing a robust foundation for our reporting needs.

Challenges Faced

One of the significant challenges was making the reports comprehensive yet easy to understand for various stakeholders. Balancing technical details with clear, concise summaries required careful planning and iteration. Additionally, integrating data from different sources and ensuring consistency across reports was a complex task that demanded meticulous attention to detail.

What's Next?

This Capacity Planning Report is just one part of our ongoing efforts to enhance our database management capabilities. In the coming weeks, I’ll be sharing more detailed reports, each focusing on different aspects of our database management system. These articles will delve into the specifics of each report, discussing the purpose, methodology, tools used, and the challenges faced.

Stay tuned for these detailed explorations, where I will share insights and tips that can help you in your database management endeavors.

HA SQL Report: SQL Server Database Replication Report

Abdelhak — Tue, 13 Aug 2024 15:20:11 +0000

This report provides an overview of the replication status of various SQL Server databases, helping to monitor and ensure the high availability of the databases.

SQL Server Database Replication Report

Server Name	Server Backup	Timestamp	Database Name	Gap (seconds)	State
SQLSERVER01	SQLSERVER02	17/07/24 14:01	DB1_Replication	3	SYNCHRONIZED
SQLSERVER02	SQLSERVER01	17/07/24 14:01	DB2_Reporting	5	SYNCHRONIZED
SQLSERVER03	SQLSERVER04	17/07/24 14:01	DB3_Analytics	8	SYNCHRONIZED
SQLSERVER04	SQLSERVER03	17/07/24 14:01	DB4_Sales	10	SYNCHRONIZED

Column Descriptions

Server Name: The name of the primary server hosting the database.
- Query Reference: Corresponds to the "SERVERNAME" column in the query.
Server Backup: The name of the backup server for the database.
- Query Reference: Corresponds to the "SRVBACKUPNAME" column in the query.
Timestamp: The date and time when the replication status was recorded.
- Query Reference: Corresponds to the "STAMP" column in the query.
Database Name: The name of the database being replicated.
- Query Reference: Corresponds to the "DATABASE_NAME" column in the query.
Gap (seconds): The time difference in seconds between the primary and backup servers.
- Query Reference: Corresponds to the "nvl(LAG_SECONDS,-10000)" column in the query.
State: The replication state of the database (e.g., SYNCHRONIZED, NOT SYNCHRONIZED).
- Query Reference: Corresponds to the "STATE" column in the query.

What's Next?

The HA SQL Report is an essential tool for monitoring the replication status of your SQL Server databases. In the coming weeks, I will be sharing more detailed reports, each focusing on different aspects of our database management system. These articles will delve into the specifics of each report, discussing the purpose, methodology, tools used, and the challenges faced.

Stay tuned for these detailed explorations, where I will share insights and tips that can help you in your database management endeavors.

Capacity Planning: Database Capacity Planning Report

Abdelhak — Mon, 05 Aug 2024 07:36:12 +0000

Oracle Enterprise Manager 13c OEM

This report provides a detailed analysis of the database capacity planning metrics, which are crucial for managing and optimizing database performance and resources. Each chart focuses on specific metrics essential for capacity planning.

Capacity Planning Charts

Chart 1: Allocated vs Used Space (GB)

Allocated Space (GB)
Used Space (GB)

Chart 2: CPU Utilization (%)

CPU Utilization (%)

Chart 3: Memory Utilization (%)

Used Logical Memory (%)
Swap Utilization (%)

Chart 4: Disk Response Time (ms)

Max Average Disk I/O Service Time (ms) amongst all disks
Total Disk I/O made across all disks (per second)

Chart 5: Number of Transactions per Second

Number of Transactions (per second)

Chart 6: Oracle Processes & Sessions (%)

Process Limit Usage (%)
Session Limit Usage (%)

What's Next?

This Capacity Planning Database Report provides a crucial overview of various performance metrics essential for effective database management. In the coming weeks, I’ll be sharing more detailed reports, each focusing on different aspects of our database management system. These articles will delve into the specifics of each report, discussing the purpose, methodology, tools used, and the challenges faced.

Stay tuned for these detailed explorations, where I will share insights and tips that can help you in your database management endeavors.

Flashback Report: Status of Databases in Flashback Mode

Abdelhak — Tue, 30 Jul 2024 17:35:10 +0000

This report provides a snapshot of the status of various databases in Flashback mode, helping to monitor and manage the Flashback capability effectively.

Flashback Status of Databases

Date	Database Name	Flashback Status
Wednesday, July 17, 2024 08:02 AM	PROD_DB1	YES
Wednesday, July 17, 2024 07:38 AM	TEST_DB2	YES
Wednesday, July 17, 2024 08:02 AM	DEV_DB3	YES
Wednesday, July 17, 2024 08:00 AM	STAGE_DB4	NO
Wednesday, July 17, 2024 08:02 AM	BACKUP_DB5	YES
Wednesday, July 17, 2024 09:01 AM	ARCHIVE_DB6	YES
Wednesday, July 17, 2024 08:00 AM	LIVE_DB7	NO
Wednesday, July 17, 2024 08:02 AM	TEST_DB8	YES
Wednesday, July 17, 2024 08:02 AM	DEV_DB9	YES
Wednesday, July 17, 2024 08:01 AM	STAGE_DB10	YES
Wednesday, July 17, 2024 08:01 AM	PROD_DB11	YES

Column Descriptions

Date: The date and time when the Flashback status was recorded.
- Query Reference: Corresponds to the "TIMESTAMP" column in the query.
Database Name: The name of the database.
- Query Reference: Corresponds to the "DATABASE_NAME" column in the query.
Flashback Status: Indicates whether Flashback is enabled for the database (YES/NO).
- Query Reference: Corresponds to the "FLASHBACK_STATUS" column in the query.

What's Next?

The Flashback Report is an essential tool for monitoring the Flashback capability of your databases. In the coming weeks, I will be sharing more detailed reports, each focusing on different aspects of our database management system. These articles will delve into the specifics of each report, discussing the purpose, methodology, tools used, and the challenges faced.

Stay tuned for these detailed explorations, where I will share insights and tips that can help you in your database management endeavors.

Oracle Tablespace Supervision Report With Oracle Analytics

Abdelhak — Thu, 25 Jul 2024 17:19:23 +0000

Oracle Enterprise Manager 13c OEM

Oracle Tablespace Supervision Report (Threshold: 85%)

This report provides a snapshot of the tablespace usage in Oracle databases, helping to monitor and manage space allocation effectively.

Report Details

Supervision of Tablespaces

Timestamp	Instance	Tablespace	Allocated Space (MB)	Used Space (MB)	Used Space (%)
17/07/2024 12:00 PM	DBPROD01	TBS_MAIN	2555000	2352000	92.01
17/07/2024 12:02 PM	APPPRD	TBS_INDEX	400000	355000	88.75
17/07/2024 11:01 AM	FINPRD	TBS_FINANCE	28000	25000	89.29
17/07/2024 12:02 PM	HRPRD	TBS_HR	190000	166000	87.37
17/07/2024 12:03 PM	SALESDB	TBS_SALES	900000	785000	87.22
17/07/2024 12:03 PM	MKTDB	TBS_MARKETING	420000	365000	86.90
17/07/2024 12:02 PM	SUPPLYPRD	TBS_SUPPLYCHAIN	410000	355000	86.59

Column Descriptions

Timestamp: The date and time when the data was collected.
Instance: The name of the database instance.
Tablespace: The name of the tablespace.
Allocated Space (MB): The total amount of space allocated to the tablespace in megabytes.
Used Space (MB): The amount of space currently used in the tablespace in megabytes.
Used Space (%): The percentage of the allocated space that is currently used.

SQL Server Backup Report : SQL Server Database

Abdelhak — Mon, 22 Jul 2024 12:41:00 +0000

This report provides a detailed overview of SQL Server backups conducted on July 17, 2024. It includes critical metrics such as server name, timestamp, database name, backup type, start and end times, duration, status, last log backup, and backup size.

Backup Details

Server Name	Timestamp	Database Name	Backup Type	Start Time	End Time	Duration (mins)	Status	Last Log Backup	Backup Size (GB)
Server1	17/07/24 07:49	DB1	Full Backup	17/07/24 00:05	17/07/24 00:10	5	Backup Complete	16/07/24 14:00	35.20
Server2	17/07/24 07:49	DB2	Full Backup	17/07/24 00:15	17/07/24 00:25	10	Backup Complete	16/07/24 14:00	1.50
Server3	17/07/24 07:49	DB3	Full Backup	17/07/24 00:30	17/07/24 00:45	15	Backup Complete	16/07/24 14:00	0.75
Server4	17/07/24 07:49	DB4	Full Backup	17/07/24 01:00	17/07/24 01:15	15	Backup Complete	16/07/24 14:00	2.80
Server5	17/07/24 07:49	DB5	Full Backup	17/07/24 01:30	17/07/24 01:45	15	Backup Complete	16/07/24 14:00	0.90
Server6	17/07/24 07:49	DB6	Full Backup	17/07/24 02:00	17/07/24 02:30	30	Backup Complete	16/07/24 14:00	3.50

Column Descriptions

Server Name: The name of the server where the backup was performed.
Timestamp: The date and time when the report was generated.
Database Name: The name of the database that was backed up.
Backup Type: Indicates the type of backup performed, such as a full backup.
Start Time: The timestamp when the backup operation started.
End Time: The timestamp when the backup operation ended.
Duration (mins): The total time taken for the backup operation, in minutes.
Status: The status of the backup operation, indicating if it was completed successfully.
Last Log Backup: The timestamp of the last log backup before this backup operation.
Backup Size (GB): The size of the backup file generated, in gigabytes.

RMAN Backup Report - Oracle Databases - Oracle Enterprise Manager 13c OEM

Abdelhak — Thu, 18 Jul 2024 14:33:52 +0000

RMAN Backup Report - Oracle Databases

Following my previous post on building comprehensive OEM-based database reports, I'm excited to dive into the first detailed report: the RMAN Backup Report for Oracle Databases. This report provides critical insights into the backup operations performed on our Oracle databases, ensuring data integrity and availability.

The RMAN Backup Report

This report gives a detailed overview of the RMAN backups for Oracle databases conducted on July 17, 2024. It includes essential metrics such as database name, version, type of backup, start and end times, status, duration, input and output sizes, and throughput.

Backup Details

Database Name	Version	Backup Type	Start Time	End Time	Status	Duration	Input Size	Output Size	Throughput (MB/s)
ORCLDB01	12.2.0.1.0	DB INCR	17/07/24 05:00	17/07/24 05:55	COMPLETED	00:55:00	30.00G	12.50G	3.60
FINDB02	19.0.0.0.0	DB FULL	17/07/24 01:30	17/07/24 02:45	COMPLETED	01:15:00	1.50T	50.00G	20.00
HRDB03	11.2.0.4.0	DB INCR	17/07/24 03:15	17/07/24 03:50	COMPLETED	00:35:00	25.00G	10.00G	5.00
SALESDB04	12.1.0.2.0	DB FULL	17/07/24 02:00	17/07/24 03:15	COMPLETED	01:15:00	2.00T	60.00G	15.00
TESTDB05	19.0.0.0.0	DB INCR	17/07/24 04:00	17/07/24 04:25	COMPLETED	00:25:00	10.00G	4.00G	4.50
PRODDB06	11.2.0.4.0	DB FULL	17/07/24 05:30	17/07/24 06:10	COMPLETED	00:40:00	500.00G	100.00G	12.00

Column Descriptions

Database Name: The name of the Oracle database.
Version: The version of the Oracle database.
Backup Type: Indicates whether the backup is a full backup (DB FULL) or an incremental backup (DB INCR).
Start Time: The timestamp when the backup operation started.
End Time: The timestamp when the backup operation ended.
Status: The status of the backup operation, indicating if it was completed successfully.
Duration: The total time taken for the backup operation to complete.
Input Size: The total size of the data read during the backup.
Output Size: The total size of the backup file generated.
Throughput (MB/s): The rate at which data was processed during the backup operation.

What's Next?

This RMAN Backup Report is just the beginning. In the coming days, I'll be sharing more detailed reports, each focusing on different aspects of our database management system. These articles will delve into the specifics of each report, discussing the purpose, methodology, tools used, and the challenges faced.

The next report in this series will cover the SQL Server Backup Report. Stay tuned for these detailed explorations, where I will share insights and tips that can help you in your database management endeavors. Feel free to ask any questions or share your thoughts in the comments below.

Empowering Insights: Using OEM Data with Oracle Analytics for Advanced Database Reports

Abdelhak — Wed, 17 Jul 2024 15:22:36 +0000

Transforming Data into Actionable Insights

Creating comprehensive database reports is crucial for maintaining and optimizing database performance. Recently, I undertook the task of developing a series of detailed OEM reports for both Oracle and SQL Server databases. This journey was both challenging and enlightening, and I’m excited to share my experiences.

The Reports

Here’s a brief overview of the reports I created:

Report Name	Description
RMAN Oracle Backup Report	Detailed backup report for Oracle databases
SQL Server Backup Report	Comprehensive backup report for SQL Server databases
RMAN Oracle Databases Backup	Overview report on backups for Oracle databases
Capacity Planning Report	Detailed capacity planning for databases
Oracle Data Guard Report	Replication status and health using Data Guard
Oracle Database Options Report	Detailed status of Oracle database options
Oracle Flashback Status Report	Flashback technology status for Oracle databases
SQL Server HA Replication Report	Status and synchronization of SQL Server databases
Oracle Tablespace Usage Report	Detailed usage and status of tablespaces

The Journey

Collaborating with the Database Team

This project was a focused effort with the database team, comprised of DBAs who knew exactly what they needed. They provided initial SQL queries, and my role involved refining and optimizing these queries to ensure optimal performance. It was a collaborative process with clear communication to meet the team's specific requirements.

Transitioning to Oracle Analytics

A significant challenge was navigating Oracle's extensive documentation, which proved dense and difficult to decipher at times. Despite this, transitioning the queries from Oracle BI Publisher to Oracle Analytics was crucial for leveraging the robust data sources from Oracle Enterprise Manager (OEM).

Building and Testing

The development of these reports involved extensive SQL scripting, shell scripting, and leveraging OEM’s reporting capabilities. Rigorous testing ensured that each report was not only accurate and timely but also provided actionable insights. Testing across multiple environments was essential to ensure reliability and effectiveness.

Challenges Faced

One of the major challenges was deciphering and effectively utilizing the vast data capabilities of Oracle Enterprise Manager. The complexity of Oracle’s documentation posed a hurdle, requiring careful navigation and understanding. Additionally, maintaining report consistency and integrating data from diverse sources were critical tasks that demanded meticulous attention.

Looking Forward

This project has been an enlightening journey, and I’m eager to delve deeper into each report in future articles. Stay tuned as I explore the specific purposes, methodologies, tools, and challenges of each report. These insights and practical tips will be invaluable for anyone involved in database management.

Feel free to share your thoughts or questions in the comments below.