Oracle Exadata. Current Exadata Machine 19.1.0. Features and capabilities.

Oracle Exadata. Current Exadata Machine 19.1.0. Features and capabilities.
Nov 25, 2019.
Prakash.

1. OS. Oracle Linux 7.5
The Oracle Linux kernel UEK4 that has been used in previous releases continues to stay the same.

2. Oracle Database Software Minimum Required Version.
The following Oracle Database and Oracle Grid Infrastructure software releases are supported with Oracle
Exadata System Software release 19.1.0 and Oracle Linux 7.5.

* Oracle Database and Oracle Grid Infrastructure 19c
* Oracle Database and Oracle Grid Infrastructure 18c
   Minimum version 18.3.0.0.180717
* Oracle Database and Oracle Grid Infrastructure 12c Release 2 (12.2.0.1.0)
   Minimum version 12.2.0.1.180717
* Oracle Database and Oracle Grid Infrastructure 12c Release 1 (12.1.0.2.0)
   Minimum version 12.1.0.2.180831
* Oracle Database 11g Release 2 (11.2.0.4.0)
   Minimum version 11.2.0.4.180717
   Requires Oracle Grid Infrastructure release 12.1.0.2.180831 or higher
* Oracle Database 11g Release 2 (11.2.0.3.0)
   Minimum version 11.2.0.3.28
   Requires Oracle Grid Infrastructure release 12.1.0.2.180831 or higher
   Installation of the Oracle Database software is a manual task for this release and is not covered by OEDA.

3. Automated Performance Monitoring.
Oracle Exadata System Software can automatically detect performance issues and figure out the root cause without
human intervention. Examples of how this feature operates include:

* If a spinning process is taking up all the resources on the system and impacting database performance, Oracle Exadata
   System Software automatically detects the CPU spin, pinpoints the exact process that is causing the spin, and generates an alert.

* If the Oracle database is not properly configured with huge pages according to the best practice recommendation, Oracle Exadata
   System Software automatically detects the misconfiguration and generates an alert for the affected database instances.
   There is no configuration required for this feature. To receive alerts, you must configure the notification mechanism. See Monitoring
   Requests and Alerts for Oracle Exadata Storage Server and ALTER DBSERVER.

As a part of this feature, Management Server (MS) is enabled on user domains (domU) in addition to storage servers,
database servers (bare metal configuration), and management domains (dom0).

Minimum requirements:
Oracle Exadata System Software 19.1.0 

4. Faster Smart Scans Using Column-Level Checksum.
Checksum computation and validation helps to detect errors that may happen during storage or retrieval of data.
A checksum is computed when a piece of data is written to Exadata Flash Cache and the checksum is verified on a subsequent read.

The key benefits of the column level checksum approach are:
* Selective checksum computation: When smart scan reads a flash block containing the columns of interest, checksum verification is performed
   only for those specific column   CUs even though there are many other columns present in the cache line. This reduces the amount of data on
   which the checksum is performed resulting in lower CPU usage. For example, consider the predicate A < 5 and Z < 10. The flash block where
   column A resides could contain columns B, C, and D. However, because B, C, and D are not referenced in the query, checksums are not performed for B, C, and D.

* Just in time checksum computation: Checksums are performed only when a column is processed. For example, consider the predicate A < 5 and Z < 10.
   Checksum is computed on the column CU for column A and the predicates are evaluated. If there are no rows satisfying the predicate A < 5, then there is
   no reason to evaluate Z < 10. Checksum computation is not performed on the column CU for column Z. This can greatly reduce the amount of data on which
   the checksum is performed resulting in lower CPU usage.

This feature is automatically enabled when you configure the INMEMORY_SIZE database initialization parameter and upgrade to Oracle Exadata System
Software release 19.1.0; no further configuration is required to use this new feature.

Minimum requirements:

* Oracle Exadata System Software release 19.1.0
* Exadata Smart Flash Cache
* INMEMORY_SIZE database initialization parameter

5. Enhanced OLTP High Availability During Cell Outages and Flash Failures.
If a cell goes offline or a flash device fails, then the databases redirect IOs to use secondary mirrors for data read operations because
the primary mirrors are unavailable due to the outage. However, the secondary mirrors might not be in the flash cache of the respective cell,
so the databases must read the data from hard disks. This can negatively impact application performance due to flash cache misses.

With this new feature, Oracle Exadata System Software prefetches the secondary mirrors of the OLTP data that is most frequently accessed into
the flash cache. This prefetching is done as a background task. Oracle Exadata System Software automatically manages the secondary mirrors in the flash
cache in an optimal way so that newer or more active secondary mirrors replace the cold data in the cache. In addition, when a flash device is replaced, this
feature also ensures that the newly replaced flash device is made more current (warmed up) before redirecting database read operations to the new flash device.
Thus, this feature provides better high availability for application performance by greatly reducing the secondary mirror flash cache misses during cell or flash
device failures and flash device replacements.

This feature is useful for OLTP workloads only. Oracle Exadata System Software does not cache the secondary mirrors for scan data.
Also, this feature is only enabled for write-back Flash Cache. No secondary mirror caching is done for write-through Flash Cache.

Minimum requirements:

* Oracle Exadata System Software release 19.1.0
* Oracle Database 19c
* Exadata Write Back Flash Cache on High Capacity storage servers
* Exadata Database Machine X6 or later (due to flash cache size requirements)

6. Support for Host and Integrated Lights Out Manager (ILOM) on Seperate Network.
This feature helps improve security by enabling you to configure a separate network for ILOM so that the Exadata servers and ILOM are
completely separate from each other.

Minimum requirements.
* Oracle Exadata System Software release 19.1.0

7. DB_UNIQUE_NAME support for Multiple Clusters Sharing Exadata Storage.
You can now use the same DB_UNIQUE_NAME value for databases that share the same storage. This feature enables Oracle Multitenant
clustered databases sharing the same storage cells to have the same DB_UNIQUE_NAME.

This feature eliminates the need for different clusters that share Exadata storage to coordinate the DB_UNIQUE_NAME assignments to avoid a name conflict.
Each cluster is free to choose any DB_UNIQUE_NAME value, without having to coordinate with other clusters, as long as ASM-scoped security is configured
for each cluster.

When ASM-scoped security is configured, the ASM cluster name is used to qualify the DB_UNIQUE_NAME for access to the storage servers.
The metrics and stats collected for each database also use the asmcluster.database name qualification in various areas including I/O Resource
Management (IORM), Flash Cache, and cell offloading.

** Note. There are limitations for Oracle Zero Data Loss Recovery Appliance.

Minimum requirements.
* Oracle Exadata System Software release 19.1.0
* Each database with the same DB_UNIQUE_NAME must be in a different Oracle ASM cluster
* ASM-scoped security configured for each Oracle ASM cluster

8. Secure Eraser Updates.
With Oracle Exadata System Software release 19.1.0, various improvements were made to Secure Eraser.
* Automatic Upgrading of Multi-pass Disk Erasure to Secure Eraser
* Automatic Secure Eraser as Part of Imaging
* Secure Eraser Improvements and New Features

Minimum requirements.
* Oracle Exadata System Software 19.1.0
* Oracle Exadata Database Machine X5-2 or later

9. Server Time Synchronization Uses Chrony.
Minimum requirements:
* Oracle Exadata System Software release 19.1.0

10. Security Improvements.
With Oracle Exadata System Software release 19.1.0, various security improvements were introduced.
* Access Control for RESTful Service
* Advanced Intrusion Detection Environment (AIDE)
* Password Expiration for Remote Users
* Implementing the Principle of Least Privilege to Improve Security
* Increased Security for Storage Server Processes
* SSHD ClientAliveInterval Changed to 600 Seconds
* Stronger Password Requirements
* Upload DIAGPACK to Oracle ASR Manager using HTTPs

ORA-00258: Manual Archiving in NOARCHIVELOG mode must identify log.

ORA-00258: Manual Archiving in NOARCHIVELOG mode must identify log.
May 6, 2019

Problem:
Trying to do full backup of an Oracle database using RMAN.
Get the above error.

Here is the entire seq of errors before RMAN kicks out.
RMAN>
Starting backup at 14-APR-18
ORACLE error from target database:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 14-APR-18

Solution:
Basically the DB is in a NOARCHIEVLOG mode and we cannot backup the DB without specifying Log Seq Number.

Restarting the Database in a ARCHIEVLOG mode.

Using SQL*Plus to Change the Database Archiving Mode

1: Connect to DB as SYSDBA
SQLPLUS ‘/as sysdba’

2:SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3: Start the instance and mount the database:
    SQL> STARTUP MOUNT
ORACLE instance started.
Database mounted.

4: Place the database in ARCHIVELOG mode
SQL> ALTER DATABASE ARCHIVELOG;
Database altered.

5: Open Database
SQL> ALTER DATABASE OPEN;
Database altered.

6: Verify your changes:
SQL>ARCHIVE LOG LIST

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u05/oradata/flash_recovery_area/xxxx/archivelog/
Oldest online log sequence xxxxxx
Next log sequence to archive xxxxxx
Current log sequence xxxxxxx

As you can see the Archievlog is enabled.
No we try to do a backup again using RMAN.
The backup, using RMAN, works.

Prakash

Oracle: pfile and spfile notes.

Oracle: pfile and spfile notes (Tested on Oracle 11g on Oracle Linux)
May 5, 2019

By default Oracle will start using the spfilesid.ora if there is one present in:
$ORACLE_HOME\dbs

If there is no spfile then it starts up using pfilesid.ora in:
$ORACLE_HOME\dbs

If we wanted to to startup oracle using pfile other that the default names then the full path command would be:
startup pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/XXXXXX.ora’;
‘XXXXX’ being one time use pfile name that we want to use.

To make a pfile from a working spfile:
create pfile=’/$ORACLE_HOME/dbs/xxxxxxx.ora’ from spfile=’/$ORACLE_HOME/dbs/xxxxxxxxx.ora’;

To make a spfile from a working pfile:
CREATE SPFILE=’$ORACLE_HOME/dbs/xxxxxxx.ora’ FROM PFILE=’$ORACLE_HOME/dbs/xxxxxxxx.ora’;

Changing an SPFILE Parameter While the DB is UP:

ALTER SYSTEM SET smtp_out_server=’mail.hallmarkbusiness.com’
                 COMMENT=’mail server change.’
                 SCOPE=BOTH;

examples:
create pfile=’/$ORACLE_HOME/dbs/initbsid.ora’ from spfile=’/$ORACLE_HOME/dbs/spfilebsid.ora’;
create SPFILE=’/$ORACLE_HOME/dbs/spfilebsid.ora’ from pfile=’/$ORACLE_HOME/dbs/initbsid.ora’;

STARTING DB WITH PFILE:
STARTUP PFILE = /$ORACLE_HOME/dbs/initbsid.ora

Prakash

Oracle: Resolving Blocking.

Finding Blocked Sessions In Oracle (All scripts verified in 11g running under Oracle Linux)
May 5, 2019

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second until the first one has done its work. It mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.

Oracle provides several views to find the sessions that are the cause of blocking, like DBA_BLOCKERS, V$LOCK.

1: Here is query that will try to find locking sessions using V$LOCK view.

SELECT vs.username,
vs.osuser,
vh.sid locking_sid,
vs.status status,
vs.module module,
vs.program program_holding,
jrh.job_name,
vsw.username,
vsw.osuser,
vw.sid waiter_sid,
vsw.program program_waiting,
jrw.job_name,
‘alter system kill session ‘ || ””|| vh.sid || ‘,’ || vs.serial# || ”’;’  “Kill_Command”
FROM v$lock vh,
v$lock vw,
v$session vs,
v$session vsw,
dba_scheduler_running_jobs jrh,
dba_scheduler_running_jobs jrw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)

AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.sid = vs.sid
AND vw.sid = vsw.sid
AND vh.sid = jrh.session_id(+)
AND vw.sid = jrw.session_id(+);

The output, if there is any blocking activity, will give us session information, blocking and blocked SID. The last column
 gives the actual kill command to kill a hung session.  

2:  Find blocking sessions with v$session

SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
  
v$session s
WHERE
   blocking_session IS NOT NULL

3 Blocking sessions, a comprehensive query to get all available information. Run this query and you can immediately place a call to the user who is causing the blocking.

SELECT s1.username || ‘@’ || s1.machine
    || ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘
    || s2.username ||
‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2

These queries should help you to identify the cause of your blocking sessions!

To Kill a session:

Alter system kill session ‘23,7955’;   (example only)
Alter system kill session ‘sid, serial’;

4: Finding a session and killing a session
SELECT SID, SERIAL#, STATUS, SERVER, USERNAME
FROM V$SESSION

—–Where clause
–WHERE USERNAME = ‘tomf’

ALTER SYSTEM KILL SESSION ‘sid, serial’;

Prakash