Oracle Database 19c Installation On Fedora 33 (F33)

This article describes the installation of Oracle Database 19c 64-bit on Fedora 33 (F33) 64-bit. The article is based on a server installation with a minimum of 2G swap and secure Linux set to permissive.

◉ Download Software

Download the Oracle software from OTN or MOS depending on your support status.

- OTN: Oracle Database 19c (19.3) Software (64-bit).

- edelivery: Oracle Database 19c (19.3) Software (64-bit)

◉ Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

<IP-address>  <fully-qualified-machine-name>  <machine-name>

For example.

127.0.0.1       localhost localhost.localdomain localhost4 localhost4.localdomain4

192.168.56.141  fedora33.localdomain  fedora33

Set the correct hostname in the "/etc/hostname" file.

fedora33.localdomain

◉ Set Kernel Parameters

Add the following lines to the "/etc/sysctl.conf" file, or in a file called "/etc/sysctl.d/98-oracle.conf".

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

Run one of the following commands to change the current kernel parameters, depending on which file you edited.

/sbin/sysctl -p

# Or

/sbin/sysctl -p /etc/sysctl.d/98-oracle.conf

Add the following lines to a file called "/etc/security/limits.d/oracle-database-server-19c-preinstall.conf" file.

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

Stop and disable the firewall. You can configure it later if you wish.

# systemctl stop firewalld

# systemctl disable firewalld

Set SELinux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

The server will need a reboot for the change to take effect.

◉ Setup

The following packages are listed as required. Some are commented out as they are not present in the Fedora repository.

#dnf groupinstall "GNOME Desktop" -y

#dnf groupinstall "Development Tools" -y

#dnf groupinstall "Administration Tools" -y

#dnf groupinstall "System Tools" -y

dnf install -y bc    

dnf install -y binutils

#dnf install -y compat-libcap1

dnf install -y compat-libstdc++-33

#dnf install -y dtrace-modules

#dnf install -y dtrace-modules-headers

#dnf install -y dtrace-modules-provider-headers

#dnf install -y dtrace-utils

dnf install -y elfutils-libelf

dnf install -y elfutils-libelf-devel

dnf install -y fontconfig-devel

dnf install -y glibc

dnf install -y glibc-devel

dnf install -y ksh

dnf install -y libaio

dnf install -y libaio-devel

#dnf install -y libdtrace-ctf-devel

dnf install -y libXrender

dnf install -y libXrender-devel

dnf install -y libX11

dnf install -y libXau

dnf install -y libXi

dnf install -y libXtst

dnf install -y libgcc

dnf install -y librdmacm-devel

dnf install -y libstdc++

dnf install -y libstdc++-devel

dnf install -y libxcb

dnf install -y make

dnf install -y net-tools # Clusterware

dnf install -y nfs-utils # ACFS

dnf install -y python # ACFS

dnf install -y python-configshell # ACFS

dnf install -y python-rtslib # ACFS

dnf install -y python-six # ACFS

dnf install -y targetcli # ACFS

dnf install -y smartmontools

dnf install -y sysstat

# Added by me.

yum install -y unixODBC

# Required for Fedora.

dnf install -y libnsl2

dnf install -y libnsl2.i686

dnf install -y libxcrypt-compat

dnf install -y http://rpmfind.net/linux/fedora/linux/development/rawhide/Everything/x86_64/os/Packages/c/compat-libpthread-nonshared-2.34-1.fc35.x86_64.rpm

# Downgrade binutils.

dnf downgrade -y https://kojipkgs.fedoraproject.org//vol/fedora_koji_archive04/packages/binutils/2.32/33.fc31/x86_64/binutils-gold-2.32-33.fc31.x86_64.rpm \

                https://kojipkgs.fedoraproject.org//vol/fedora_koji_archive04/packages/binutils/2.32/33.fc31/x86_64/binutils-2.32-33.fc31.x86_64.rpm

#dnf update -y

Thanks to Nikolay Popov and Amadis for the suggestion of downgrading binutils to fix database creation with the DBCA.

Create the new groups and users.

groupadd -g 54321 oinstall

groupadd -g 54322 dba

groupadd -g 54323 oper

#groupadd -g 54324 backupdba

#groupadd -g 54325 dgdba

#groupadd -g 54326 kmdba

#groupadd -g 54328 asmdba

#groupadd -g 54328 asmoper

#groupadd -g 54329 asmadmin

useradd -u 54321 -g oinstall -G dba,oper oracle

passwd oracle

We are not going to use the extra groups, but include them if you do plan on using them.

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

mkdir -p /u02/oradata

chown -R oracle:oinstall /u01 /u02

chmod -R 775 /u01 /u02

Putting mount points directly under root without mounting separate disks to them is typically a bad idea. It's done here for simplicity, but for a real installation "/" storage should be reserved for the OS.

If you are using X Emulation, login as root and issue the following command.

xhost +<machine-name>

You will need to add the following symbolic links or the Oracle Universal Installer (OUI) will not start.

# Fix for Oracle on Fedora.

rm -f /usr/lib64/libnsl.so.1

rm -f /usr/lib/libnsl.so.1

ln -s /usr/lib64/libnsl.so.2.0.0 /usr/lib64/libnsl.so.1

ln -s /usr/lib/libnsl.so.2.0.0 /usr/lib/libnsl.so.1

Set up the environment for the "oracle" user. The "$" characters are escaped using "\". If you are not creating the file with the cat command, you will need to remove the escape characters.

mkdir -p /home/oracle/scripts

cat > /home/oracle/scripts/setEnv.sh <<EOF

# Oracle Settings

export TMP=/tmp

export TMPDIR=\$TMP

export ORACLE_HOSTNAME=fedora33.localdomain

export ORACLE_UNQNAME=cdb1

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=\$ORACLE_BASE/product/19.0.0/dbhome_1

export ORA_INVENTORY=/u01/app/oraInvenotry

export ORACLE_SID=cdb1

export PDB_NAME=pdb1

export DATA_DIR=/u02/oradata

export PATH=/usr/sbin:/usr/local/bin:\$PATH

export PATH=\$ORACLE_HOME/bin:\$PATH

export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib

EOF

echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile

chown -R oracle:oinstall /home/oracle/scripts

◉ Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=<machine-name>:0.0; export DISPLAY

Perform a software-only installation either using interactive mode (GUI) or silent mode and run the root scripts when prompted. Notice the setting of the CV_ASSUME_DISTID environment variable, so fake the OS.

# Unzip software.

cd $ORACLE_HOME

unzip -oq /path/to/software/LINUX.X64_193000_db_home.zip

# Fake OS.

export CV_ASSUME_DISTID=OEL7.6

# Interactive mode.

#./runInstaller

# Silent mode.

./runInstaller -ignorePrereq -waitforcompletion -silent                        \

    -responseFile ${ORACLE_HOME}/install/response/db_install.rsp               \

    oracle.install.option=INSTALL_DB_SWONLY                                    \

    ORACLE_HOSTNAME=${ORACLE_HOSTNAME}                                         \

    UNIX_GROUP_NAME=oinstall                                                   \

    INVENTORY_LOCATION=${ORA_INVENTORY}                                        \

    SELECTED_LANGUAGES=en,en_GB                                                \

    ORACLE_HOME=${ORACLE_HOME}                                                 \

    ORACLE_BASE=${ORACLE_BASE}                                                 \

    oracle.install.db.InstallEdition=EE                                        \

    oracle.install.db.OSDBA_GROUP=dba                                          \

    oracle.install.db.OSBACKUPDBA_GROUP=dba                                    \

    oracle.install.db.OSDGDBA_GROUP=dba                                        \

    oracle.install.db.OSKMDBA_GROUP=dba                                        \

    oracle.install.db.OSRACDBA_GROUP=dba                                       \

    SECURITY_UPDATES_VIA_MYORACLESUPPORT=false                                 \

    DECLINE_SECURITY_UPDATES=true

Run the root scripts when prompted.

As a root user, execute the following script(s):

        1. /u01/app/oraInvenotry/orainstRoot.sh

        2. /u01/app/oracle/product/19.0.0/dbhome_1/root.sh

You are now ready to create a database.

◉ Database Creation

You create a database using the Database Configuration Assistant (DBCA). The interactive mode will display GUI screens to allow user input, while the silent mode will create the database without displaying any screens, as all required options are already specified on the command line.

# Start the listener.

lsnrctl start

# Interactive mode.

# dbca

# Silent mode.

dbca -silent -createDatabase                                                   \

     -templateName General_Purpose.dbc                                         \

     -gdbname ${ORACLE_SID} -sid  ${ORACLE_SID} -responseFile NO_VALUE         \

     -characterSet AL32UTF8                                                    \

     -sysPassword SysPassword1                                                 \

     -systemPassword SysPassword1                                              \

     -createAsContainerDatabase true                                           \

     -numberOfPDBs 1                                                           \

     -pdbName ${PDB_NAME}                                                      \

     -pdbAdminPassword PdbPassword1                                            \

     -databaseType MULTIPURPOSE                                                \

     -memoryMgmtType auto_sga                                                  \

     -totalMemory 2000                                                         \

     -storageType FS                                                           \

     -datafileDestination "${DATA_DIR}"                                        \

     -redoLogFileSize 50                                                       \

     -emConfiguration NONE                                                     \

     -ignorePreReqs

◉ Post Installation

Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

cdb1:/u01/app/oracle/product/19.0.0/dbhome_1:Y

Source: oracle-base.com

Continue reading

Accessing Object Storage Via Oracle Analytics Cloud

With the advent of Data Lakehouse concept, the need is more than ever in trying to analyze & visualize data that is in raw format present in a Data Lake. Oracle Analytics Cloud is a single and complete platform that empowers your entire organization to ask any question of any data—across any environment, on any device. It fits into your ecosystem, enabling analysis in the cloud while also providing easy access to any data source.

However, currently there is no direct connectivity between Oracle Analytics Cloud & the Object Storage. I am sure this will be addressed in the future releases but the only way, as of now, to overcome this limitation is to have external tables created in ADW that points to the objects in the Object Storage bucket and then connect the OAC to ADW and have the visualizations created on these external tables.

In this blog, I am going to explain the steps to implement this. The architecture diagram would look like below:

As prerequisites, you need to have an ADW provisioned, an object storage bucket created, have a file containing the data uploaded (I am using the file Employee_Info.parquet) into that bucket and an OAC instance provisioned.

Note: Copy and save the object’s URL as highlighted in the third screenshot below.

Now, let us start the steps to get the connection created.

Step 1:

Firstly, you need to create an Auth Token.

Go to OCI Console à Profile (Top right corner)

Scroll down and click on Auth Tokens and then on Generate Token and give a description

Note: Save the resulting Auth Token to a notepad as this needs to be used at the next stage.

STEP 2:

Go to OCI Console à Oracle Database à Autonomous Datawarehouse à Database Actions à A new tab will open up

STEP 3: (On the newly opened tab)

Go to Data Tools à Data Load à Cloud Storage à Next

Step 4:

The newly opened page will ask you to set up a cloud storage. Once you click on the '+' icon, fill up the details as given below:

Click on NEXT.

Note: The Oracle Cloud Infrastructure User Name is your profile name. Auth Token is the token you had saved in the Step 1 and Bucket URL is the one you had saved in the Step 2.

Step 5:

Now, go back to Database Actions home page and select as below:

Select the connection name that you have used while configuring and you will be able to see the file name on the left hand side. Drag and drop the same to the pane on the right hand.

Click on the big friendly green play button and once the run finishes, this will set up an external table with the name EMPLOYEE_INFO in my case. Click Done and come back to the Database Actions page.

Now, establish a connection from SQL developer to the ADW and let us try to access this table created in the above step.

Voila! We can now access the data present in the parquet file on object storage via ADW just like we do any table in present in a Database. How cool is that!!!

However, our job isn’t done yet, is it?

Step 6:

Let us head to our OAC instance as below:

OCI Console à Analytics & AI à Analytics Cloud

Click on the Analytics instance and then on Analytics Home Page

Create a connection to your ADW instance, if not already done

Step 7:

Now, we need to create a dataset using the connection we created in the above step & find the dataset you had created earlier, EMPLOYEE_INFO in my case.

Step 8:

Drag and drop the dataset to the pane on the right side and save the same.

Now you can easily use this in your Workbooks to create visualizations or in the Data Flows as below:

Continue reading

Start Polyglot Development with Autonomous Database

Developers often choose their preferred language and development environment for developing applications.

◉ Developing artificial intelligence and machine learning applications: use Python

◉ Does your application need a lot of concurrent connections, or is it collecting data from multiple sources: use Node.js

◉ Corporate standards may dictate the Microsoft environment: use .NET

◉ Want to use the most popular language on the internet: use Java

A common afterthought in modern application development is where and how to persist the data in the cloud. The data is the most valuable part of most applications. Persisting the data in the most functional and robust database in the cloud, the Oracle Autonomous Database should be your first choice.

Very often, Various teams within an enterprise often develop applications using different technologies. Some might be legacy applications as well, understanding how various technologies and polyglot programming access the underlying database is essential for application developers.

Most new cloud based applications consist of front-end customer-facing web applications written in Node.js, Python, or PHP scripts, reading data from a database. A Java-based back-end application provides real-time data management.

Customers can use free low-code Oracle APEX-based web or mobile applications to develop and interact with the same database when they persist their data in the Oracle Autonomous Database. Data scientists can use Jupyter notebooks to test and develop machine learning algorithms with the data persisted in the database. Businesses are guaranteed 99.995% availability of their data. 

Ready to learn hands-on how to use the Oracle Autonomous Database with your development language of choice? Then run the Start Polyglot application development with Oracle Autonomous Database on our LiveLabs hands-on workshop platform. Our new LiveLabs workshop provides examples of connecting to the Autonomous Database and executing SQL queries using multiple programming languages like Python, Java, .NET and Node.js.

 

Experience how easy it is to establish a wallet-less database connection using Python. Just install the required python-oracledb driver, for example, using pip. Then use the connection string and replace the database username and password. You are all set to view the result of your SQL query.

# Sample python code to establish wallet-less connection with Oracle Autonomous Database

import oracledb

# database username and password

username = "<db_user>"

user_pwd = "<password>"

# connection string  

tlsconnstr = "<database connection string>"

connection = oracledb.connect(user=username, password=user_pwd, dsn=tlsconnstr)

with connection.cursor() as cursor:

       sql = "select * from customers where rownum < 10"

       for r in cursor.execute(sql):

               print(r) 

Similarly, you will see how we set up database connectivity & run SQL query from Java, Node.js and ODP.NET core

Why should you consider Oracle Autonomous Database?

As a developer, there are many reasons to use Oracle Autonomous Database:

- You can create in instance in a couple of minutes.

- It eliminates the complexity of operating and securing the Oracle Database.

- It gives you the highest levels of performance, scalability and availability.

- It is available on Oracle Cloud Infrastructure for shared or dedicated deployments and on-premises.

- It detects and automatically protects from system failures or user errors and provides failover to standby databases with zero data loss.

Start your workshop Polyglot application development with Oracle Autonomous Database here.

Source: oracle.com

Continue reading

2022: What’s new in the database world?

The big picture in the global database market is changing rapidly. It is an $80B market right now – the largest software market and growing in double digits year over year.

Gartner has recently published the software market revenue numbers for 2021.

In 2021, the revenue for managed cloud services is more than $39B – which means about half of al global DBMS revenue. Have a look at the drastic change since 2017:

Microsoft and AWS have right now about half of the global database market while Oracle have slipped down to having only about one fifth of the overall share. Google have entered for the first time the top 5 while IBM have just lost there 4th position. SAP are out of the top 5.

In terms of database popularity, Oracle are still #1 based on the DB engines rankings:

So, what is new and causing these turbulances?

Google have just announced AlloyDB for PostgreSQL. Compared with standard PostgreSQL, according to Google’s own performance tests, AlloyDB was more than four times faster for transactional workloads, and up to 100 times faster for analytical queries. AlloyDB was also two times faster for transactional workloads than Amazon’s comparable service. This makes AlloyDB a powerful new modernization option for transitioning off of legacy databases. Here are the other offerings from Google Cloud:

The latest from AWS is predominantly around RDS and Redshift (a database based on PostgreSQL). Amazon Redshift RA3 instances are now available in the Asia Pacific (Osaka), Europe (Milan), Middle East (Bahrain) and Africa (Cape Town) regions. Amazon Redshift now offers new enhancements for Audit Logging, which enables faster delivery of logs for analysis by minimizing latency while also adding Amazon CloudWatch as a new log destination. With the latest release, customers can choose to stream audit logs directly to Amazon CloudWatch, which enables customers to perform real-time monitoring. Amazon Redshift now also provides native integration with Microsoft Azure Active Directory (AD), which customers can use for authentication and authorization with tools like Microsoft Power BI. 

Amazon Relational Database Service (Amazon RDS) Performance Insights now makes it easier for you to see the database performance metrics for the exact timeframe you want to analyze, by choosing a custom time window within your retention period. Previously, you could only see metrics in Performance Insights by choosing relative time intervals such as the past 1 hour, the past 24 hours, etc. Amazon RDS for PostgreSQL, MariaDB and MySQL now supports M6i and R6i instances with new instance sizes up to 128 vCPUs and 1,024 GiB RAM. Also, Amazon Aurora Serverless v2 is now generally available.

The latest from Azure is around Change data capture: CDC lets you track all the changes that occur on a database. Though this feature has been available for SQL Server for quite some time, using it with Azure SQL Database is now generally available. When creating a Hyperscale database, you can choose your preferred storage type: read-access geo-redundant storage (RA-GRS), zone-redundant storage (ZRS), or locally redundant storage (LRS) Azure standard storage. The selected storage redundancy option will be used for the lifetime of the database for both data storage redundancy and backup storage redundancy. It is now possible to configure your Azure SQL Database to allow authentication only from Azure Active Directory.

IBM i 7.5, previously know as AS/400, is coming with some “interesting” features. There is a new BOOLEAN data type, the maximum size of a binary radix index is extended, up to 16 TB. Used t be 1.7TB. I do not know many DBAs who enjoy dealing with double digit TB indexes. The Db2 for i SQL Query Engine (SQE) provides a Query Supervisor which enables real-time monitoring of resource consumption by SQL and native queries. There is now a RESTRICT ON DROP attribute that can be added or removed using the ALTER TABLE (SQL) statement (ALTER TABLE TABLE_NAME ADD RESTRICT ON DROP;). When RESTRICT ON DROP is added, nobody will be allowed to delete or drop that file. Even users with *ALLOBJ user special authority will not be allowed to delete the file.

The only change I am aware of in Oracle Database Release 21c, Version 21.5 are the new AE Analytic Views. AE stands for “All Edition”. Each new AE analytic view corresponds to an existing non-AE analytic view. AE views have the same columns as their non-AE counterparts, plus a column that displays the name of the application edition where the editioned object is defined. For the complete list of changes in Oracle 21, check this link.

Source: juliandontcheff.wordpress.com

Continue reading

Announcing OCI Vision service

We’re excited to announce the general availability of Oracle Cloud Infrastructure (OCI) Vision, a computer vision service that allows customers to uncover insights in unstructured images powered by deep learning models.

What is OCI Vision?

OCI Vision is a serverless, cloud native service that provides deep learning-based, prebuilt, and custom computer vision models over REST APIs. OCI Vision helps you identify and locate objects, extract text, and identify tables, document types, and key-value pairs from business documents like receipts. No data science experience is required to use the prebuilt or custom features of OCI Vision.

You can access the service through the Oracle Cloud Console, OCI software developer kits (SDKs) in Python and Java, or the OCI CLI.

OCI Vision key capabilities

Image Analysis models available in the analyzeImage API help you understand images like photos of streets, retail goods, and transmission towers.

Image Analysis includes the following key features:

◉ Image classification: Assigns labels to the image based on the overall scene, such as "Sky,” “moisture,” and “textile”

◉ Object detection: Locates and identifies objects within an image, such as a bus, box, or person

◉ Text recognition and optical character recognition (OCR): Locates and digitizes text information from images, such as “Stop” from a stop sign or “XY3497” from a license plate

◉ Async and batch support on all image analysis APIs

Document AI models available in the analyzeDocument API help you understand document-based images like receipts, invoices, and contracts.

Document AI includes the following key features:

◉ Text recognition and OCR: Locates and digitizes text information from images at the word or line level

◉ Key-value extraction: Extract a predefined list of key-value pair information from receipts, such as fieldLabel: “TransactionDate” and fieldValue: “01/11/2022.”

◉ Table extraction: Extracts content in tabular format, maintaining row/column relationships of cells, such as cell text: “2,098,221” rowIndex: 14 columnIndex: 2.

◉ Document classification: Classifies documents into different types based on visual appearance, high-

level features, and extracted keywords, such as invoice, receipt, and resume

◉ Async and batch support on all document analysis APIs

Custom models

Customers without data science expertise can easily use OCI Vision to fit their industry or customer-specific use cases. With pretrained models for out-of-the-box use, OCI Vision also supports creating custom image classification and object detection models. Training and underlying model infrastructure are all managed through OCI Vision.

Using custom model features

To train a custom model using OCI Vision service, start with a labeled dataset. You can easily label raw images with OCI Data Labeling service.

After you select your model type and dataset, name your model and select a training duration. The default is “Recommended.”

After you kick off training a new model, model training progress, logs, and final quality metrics are available on the Model Details page. You also have an "Analyze" option to test a newly trained model on new images.

To call your custom vision model, include the model OCID as part of the modelID field in your input request. The following example shows a JSON request to call a custom image classification model:

{

"analyzeImageDetails": {

"compartmentId": "ocid1.tenancy.oc1..xxxx",

"image": {

"source": "INLINE",

"data": "......"

},

"features": [

{

"modelId": "ocid1.aivisionmodel.oc1.iad.amaaaaaapheaxxxxxxxxxxx",

"featureType": "IMAGE_CLASSIFICATION",

"maxResults": 5

}

]

}

}

Computer vision use cases

Use cases of computer vision exist in many industry verticals, including financial services, manufacturing, transportation, and retail.

◉ Automate back-office tasks: Classify documents, detect tables, and extract required information from documents like receipts to automate business workflows including employee expense reporting and reimbursement.

◉ Digital asset management: Enrich image-based files with metadata including document type, text, and objects for better indexing and retrieval in a digital asset management system or larger data warehouse.

◉ Detect visual anomalies: Classify products or equipment as standard or defective based on visual appearance like discoloration, tear, rust, deformity, or breaks. Automate the detection of defective materials to flag the need for repairs.

Computer vision customer momentum

As part of building OCI Vision, we partnered closely with customers across financial services, media, and large-scale Oracle software-as-a-service (SaaS) teams:

2RP is streamlining manual document scanning processes in the banking sector. "We were impressed with Oracle's performance on both good and less-than-ideal quality images, the latter being a common complaint in financial onboarding scenarios.” - Nicolas Borges, Product Owner.

SailGP is tagging objects in their race images. “We’re excited about Oracle Cloud Infrastructure Vision because of its ability to quickly process large amounts of visual data and the potential to increase the productivity of our teams.” - Aleksandar Kocic, Solution Architect.

Oracle Analytics Cloud is enabling business analysts the power to analyze visual data with OCI Vision service. “In 2022, Oracle Analytics is integrating with Oracle Cloud Infrastructure Artificial Intelligence services. This service detects objects and text and classifies them, making it possible for you to analyze images directly inside Oracle Analytics.”

Source: oracle.com

Continue reading

Automatic Segment Advisor in the Oracle Database

“From my experience, the best advisors help in three ways: encourage you to look at the problem or opportunity from multiple angles; help you balance the tug of the short-term with important long-term priorities; and ask the tough questions you need to know to reach the best solution.” Margo Georgiadis

The Oracle Segment Advisor identifies segments that have space which can be reclaimed. However, the Automatic Segment Advisor can be at times resource consuming and even slow down your database:

Why is this happening granted the Automatic Segment Advisor does not analyze every database object? Here is how it works internally: the advisor examines the database statistics, it samples segment data, and then selects the following objects to analyze:

◉ Tablespaces that have exceeded a critical or warning space threshold

◉ Segments that have the most activity

◉ Segments that have the highest growth rate

In addition, the Automatic Segment Advisor evaluates tables that are at least 10MB in size and have at least 3 indexes to determine the amount of space saved if the tables are compressed with advanced row compression.

Now, here is the important part: if a database object is selected for analysis by the advisor but the maintenance window expires before the advisor can process the object, the object is included in the next Automatic Segment Advisor run. So, at one point you may come to a situation where lots of objects have to be analyzed. During the maintenance window, the following clients/task are being run, these are the predefined automated maintenance tasks:

Sometimes, it makes sense to disable the auto space advisor as you cannot change the set of tablespaces and segments that the Automatic Segment Advisor selects for analysis. You can, however, enable or disable the Automatic Segment Advisor task, change the times during which the Automatic Segment Advisor is scheduled to run, or adjust automated maintenance task system resource utilization. Especially in a situation like this:

Often, also ADDM may point out to the problem with the Automatic Segment Advisor task:

Here is how to disable (and enable) the tasks individually. The main switch is controlled by DBMS_AUTO_TASK_ADMIN.DISABLE().

Even after disabling the entire autotask job by running DBMS_AUTO_TASK_ADMIN.DISABLE(), the DBA_AUTOTASK_TASK will still show autotask client as enabled. For this you need to disable the jobs individually as shown above. And for a multitenant environment, CDB and PDBs have their own autotasks, disabling CDB’s autotask will not affect the PDBs, so you will have to do for each pluggable database.

Staring with 12.2, there is a parameter called ENABLE_AUTOMATIC_MAINTENANCE_PDB that can be used to enable or disable the running of automated maintenance tasks for all the PDBs in a CDB or for individual PDBs in a CDB. Changing ENABLE_AUTOMATIC_MAINTENANCE_PDB in the CDB root from TRUE to FALSE, the new value FALSE takes effect in the root and in all the PDBs in the CDB.

If you get into a situation where the Automatic Segment Advisor is consuming lots of resource and slowing the database during the maintenance windows do one of the following:

1. Disable the autotask client for the segment advisor and run it manually on per need basis.

2. Increase the maintenance window from the default which starts at 10 p.m. on Monday to Friday and ends at 2 a.m. Often 4 hours in just not enough. The weekend window is 20h long and in most cases long enough.

In the autonomous database, you have access to dba_autotask_client, etc. and you can disable and enable the auto space advisor task however you do not have full visibility on dba_scheduler_window_groups, etc. Some columns just show as NULL. Still the performance task in ADB can give you some idea of what is going on:

And finally, here are some other situations that might require disabling the Auto Space Advisor Job:

◉ ORA-01555 While Running Auto Space Advisor Job on Object wri$_adv_objspace_trend_data (Doc ID 2576430.1)

◉ Auto Space Advisor job may sometimes cause deadlock (Doc ID 17234189.8)

◉ Auto Space Advisor is Taking More Time due to Recursive Query Taking a Long Time (Doc ID 2382419.1)

◉ In 12.2 Auto Space Advisor Job Fails With ORA-60 (Doc ID 2321020.1)

◉ SEGMENT ADVISOR not working as expected for LOB or SYS_LOB SEGMENT (Doc ID 988744.1)

Source: juliandontcheff.wordpress.com

Continue reading