Blog

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/06/2017 12:02:20

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile ‘+RECO_DAL/DWPREPRD/ARCHIVELOG/2017_07_06/thread_1_seq_85714.395.948615485’

ORA-00283: recovery session canceled due to errors

ORA-00600: internal error code, arguments: [3020], [219], [709377], [919261953], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 219, block# 709377, file offset is 1516249088 bytes)

ORA-10564: tablespace ODS

ORA-01110: data file 219: ‘+DATA_DAL/DWPREPRD/DATAFILE/ods.501.948545309′

ORA-10560: block type ’58’

This is a known Oracle Bug where RMAN creates corrupted free blocks during recovery and causes related errors. There is no fix to this issue and work around is to use “blocks all” in RMAN backup script.

Note: For next execution, use below change in the rman backup script to take the backup:

backup as compressed backupset incremental level 0 database tag ‘edw_edwbkp’ plus archivelog tag ‘edw_edwbkp’ delete all input;

to:

backup blocks all as compressed backupset incremental level 0 database tag ‘edw_edwbkp’ plus archivelog tag ‘edw_edwbkp’ delete all input;

Security Team executed their inbuilt security scans and provides the security scan report to the IT team to take appropriate actions.  Below are few custom vulnerabilities for NTP, SNMP, ICMP, TCP protocols and VNC software as stated in our security scan report for ODAs. Please perform careful discussions, planning and impact analysis before implementing any of the below changes.

NTP

Network Time Protocol (NTP) is a networking protocol for clock synchronization between computer systems over packet-switched, variable-latency data networks.

Below are the details from security scan report for NTP vulnerability.

Vulnerability Title	Vulnerability Solution
NTP clock variables information disclosure	Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Traffic amplification in clrtrap feature of ntpd	Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Traffic Amplification in reslist feature of ntpd	Disable NTP queries  – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Information disclosure in reslist feature of ntpd (CVE-2014-5209)	Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Traffic Amplification in listpeers feature of ntpd	Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Traffic Amplification in peers feature of ntpd	Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.

As per the vulnerability solution to disable NTP queries, add the following restrictions in /etc/ntp.conf and restart ntp service.

restrict default kod nomodify notrap nopeer noquery

restrict 127.0.0.1 – (restricts local clock)

server  iburst

ICMP

The Internet Control Message Protocol (ICMP) is a supporting protocol in the Internet protocol suite. It is used by network devices, like routers, to send error messages and operational information indicating, for example, that a requested service is not available or that a host or router could not be reached. ICMP differs from transport protocols such as TCP and UDP in that it is not typically used to exchange data between systems, nor is it regularly employed by end-user network applications (with the exception of some diagnostic tools like ping and traceroute).

Below are the details from security scan report for ICMP vulnerability.

Vulnerability Title	Vulnerability Solution
ICMP timestamp response	* Linux – Disable ICMP timestamp responses on Linux

As per vulnerability solution to disable ICMP timestamp responses on Linux, use the below command to create a RULE to reject ICMP timestamp response:

ipchains -A input -p icmp –icmp-type timestamp-request -j DROP ipchains -A output -p icmp –icmp-type timestamp-reply -j DROP

 

SNMP

Simple Network Management Protocol (SNMP) is an Internet-standard protocol for collecting and organizing information about managed devices on IP networks and for modifying that information to change device behavior. Devices that typically support SNMP include routers, switches, servers, workstations, printers, modem racks and more.

Below are the details from security scan report for SNMP vulnerability.

Vulnerability Title	Vulnerability Solution
Default or Guessable SNMP community names: public	Secure the SNMP installation    * If you do not absolutely need SNMP, disable it.
SNMP credentials transmitted in cleartext	Secure the SNMP installation    * If you do not absolutely need SNMP, disable it.

As per vulnerability solution to disable SNMP, Issue below commands to disable snmpd packets from server.

To stop service: Service snmpd stop

To disable service at boot time: Chkconfig snmpd off

TCP

The

Transmission Control Protocol (TCP) is one of the main protocols of the Internet protocol suite. It originated in the initial network implementation in which it complemented the Internet Protocol (IP). Therefore, the entire suite is commonly referred to as TCP/IP. TCP provides reliable, ordered, and error-checked delivery of a stream of octets between applications running on hosts communicating by an IP network.Below are the details from security scan report for TCP vulnerability.

Vulnerability Title	Vulnerability Solution
TCP timestamp response	Disable TCP timestamp responses

As per vulnerability solution to disable TCP, update below parameters to mitigate the risk.

sysctl -w net.ipv4.tcp_timestamps=0

(OR)

vi /etc/sysctl.com –> add net.ipv4.tcp_timestamps=0

run sysctl -p /etc/sysctl.conf

VNC

In computing, Virtual Network Computing (VNC) is a graphical desktop sharing system that uses the Remote Frame Buffer protocol (RFB) to remotely control another computer. It transmits the keyboard and mouse events from one computer to another, relaying the graphical screen updates back in the other direction, over a network.

Below are the details from security scan report for VNC vulnerability.

Vulnerability title	Vulnerability Solution
VNC remote control service installed	Fix VNC remote control service installed. Remove or disable this service. If it is necessary, be sure to use well thought out (hard to crack) passwords

 

Testing Overview

This is a through analysis of the Oracle Database Appliance (ODA) System IO performance. The data collected during testing was carefully reviewed.

Test Scenario

There is a total of sixteen (16) HDD Data disks in the Production Primary X5-2 ODA device. During testing, the HDD Disk IOPS Stats were captured for each disk. The test aimed at collecting performance statistics for Individual Disk Wise IO during each of the planned Test Run(s).

Test Objective(s)

The main objective for the test was to collect performance statistics for Individual Disk Wise IO per HDD Disk and then compare the performance of the X5.2 MI-CXT database against the Oracle Database Appliance Standard Performance Ratings for the main data processing metrics:

1. IO Per Second (HDD/Flash)
2. MB Per Second (HDD/Flash)
3. Log Generation Rate (MB/Sec)

 

For the purposes of these performance tests, the Oracle Database Appliance X5-2 test results were measured against the Oracle Database Appliance Sizing Templates (OLTP)[1] where:

 

Configuration Parameter	Where Value Equals
CPU Count	16 (vCPU)
SGA (Gb)	27 (Gb)
PGA (Gb)	10 (Gb)
Flash (Gb)	12 (Gb)
Log Buffer Size (Mb)	16 (Mb)
Processes Redo Log	2 (Gb)
Processes	3000 (Max)

 

Test Standards

 

Pass/Fail Standards

Standard Performance Ratings: Main Data Processing Metrics	Optimal Threshold Value for HDD/Flash
A. IO Per Second (HDD/Flash)	750/75000
B. MB Per Second (HDD/Flash)	1500/1500
C. Log Generation Rate (MB/Sec)	27.3

 

PASS/FAIL Criteria

PASS= Where System IO < Optimal Threshold

FAIL = Where System IO > Optimal Threshold

 

Test Summary of Findings            

System IO Overall Performance

Disk IO per second during each Test Run fluctuated between minimum 150 to maximum 400 ops.

Test Results Outcome = Pass 

For details per Test Run, please see attached.

Test Conclusion(s)

The X5.2 IOSTAT results from OS watcher reported Disk IO per second fluctuations between minimum 150 to maximum 400 ops for each Test Run.

With the Read & Write (RW) per second performance tested using 8k blocks at different intervals, maximum 2500 MB per second during read operation(s) and 653 MB per sec during write operation(s) was observed.

Therefore, based on these outcomes, X5-2 ODA HDD Disks clearly demonstrated to have enough System IO capacity for the task as per the ODA performance standards mentioned in the table shown below[1].

In fact, the X5-2 IO Per Second and MB Per Second can be scaled up to max 1500 IO per second/3000 MB per second with ONE X5-2 storage shelf and can be doubled with TWO X5-2 storage shelfs.

AWR Findings for DBA Consideration

Manual Data Read & Write (RW) Tests

1. 1 GB of Data Write per seconds with 8k block is 350Mb/s to 650 Mb/s (approx).

 

[root@nasodacxt0201d1 datastore]# time dd if=/dev/zero of=testfile bs=8k count=128000

128000+0 records in

128000+0 records out

1048576000 bytes (1.0 GB) copied, 1.60589 s, 653 MB/s

 

real    0m1.700s

user    0m0.030s

sys     0m1.599s

[root@nasodacxt0201d1 datastore]#

 

1. 1 GB of Data Read per seconds with 8k block is 1 to 2 Gb/s (approx)

 

[root@nasodacxt0201d1 datastore]# time dd if=testfile of=/dev/null bs=8k

128000+0 records in

128000+0 records out

1048576000 bytes (1.0 GB) copied, 0.405152 s, 2.6 GB/s

 

real    0m0.407s

user    0m0.016s

sys     0m0.382s

[root@nasodacxt0201d1 datastore]#

 

db file sequential read	379,827	8438.3	22.22	52.4	User I/O
db file parallel read	86,190	6872	79.73	42.7	User I/O

 

I/O Wait Definitions

The “I/O Wait” is an administrative wait class such as log files and it originates from Oracle Operations. User I/O is an application wait class such as a database file (dbf) and it originates from User (Application) SQL Queries.

 

• User IO is an application related wait and NOT system IO.

          AWR report shows User IO as Top Wait Classes by Total Wait Time consuming                      95.9% of DB Time during 30-Jan-18 01:00:19 to 30-Jan-18 02:00:28 and 51.3% of DB              time during 30-Jan-18 02:00:28 and 30-Jan-18 03:00:38

• With User IO events listed as the top two (2) out of the Top 10 Foreground Events by  Total Wait Time, it appears that read operations on the datafiles lying on HDD maybe behind the high database IO we have observed. It is our suggestion for our CHC CXT Logical DBA to inspect the Application SQL scripts as ordered by User I/O Wait Time to determine if further SQL Tuning may be needed.

 

db file sequential read	379,827	8438.3	22.22	52.4	User I/O
db file parallel read	86,190	6872	79.73	42.7	User I/O

 

When a DBA sees “User I/O” as a major wait event, SQL tuning is always the best answer. Industry standards best practices for SQL tuning usually involve adding missing indexes, hints, SQL profiles, etc.

————————————————————————————————————

Best Practices For Disk IO Improvement(s) for Database on ODA

Below are the some Oracle DBA industry best practice recommendations for Improved disk I/O.

 

I: Enable Smart Flash Cache below

ALTER SYSTEM SET DB_FLASH_CACHE_FILE = ” SCOPE=SPFILE;

ALTER SYSTEM SET DB_FLASH_CACHE_SIZE = 30G SCOPE=SPFILE;

 

II: Database Auditing Configuration

If auditing needs to be enabled, move aud$, fga$ out of SYSAUX tablespace to a custom tablespace.

 

III: ODA recommendations

DB parameter ‘db_lost_write_protect’ to NONE

 

 

[1] http://www.oracle.com/technetwork/database/database-appliance/documentation/oda-eval-comparing-performance-1895230.pdf.