Tag Archives: database

Elastic Block Store throughput testing with Orion

Posted on by
Reply

By Brian Fitzgerald

Introduction

ORacle I/O Numbers (Orion) results for EBS volumes are presented here. Multiple configurations of EC2 instance type, number of devices, IOPS configuration, and throughput configuration are tested. The objective is to identify the best performing and most economical system for use in Oracle Automatic Storage Management (ASM).

Amazon AWS EC2 EBS

AWS EC2 offers I/O capacity for a price. You get a slice of the underlying hardware’s full bandwidth depending on how much you pay. The actual throughput depends on the EC2 instance’s device controller and the underlying volume. EC2 device controller capacity depends on instance class (m5, r5b, etc.), and size (large, 4xlarge, etc.). EBS volume capacity depends on volume type (gp3, io2, etc.) and configuration (throughput and IOPS). Maximum I/O capacity will be the lesser of EC2 capacity and EBS volume capacity, provided that you get the layout right.

The I/O capabilities of the EBS volume types that were tested are summarized here:

Type gp3 gp2 st1 io2
Purpose General General Low cost High IOPs, low latency
Medium ssd ssd magnetic ssd
Configurable throughput Yes No No No
Maximum throughput configuration (Mbps) 1000 – – –
Configurable IOPS Yes Yes
Max IOPS configuration 160000 256,000

The EBS test volumes were not encrypted.

This article places emphasis on optimizing throughput, but some IOPS and latency observations are presented here as well. As a limiting case, we are interested in 100% write I/O throughput capacity.

I/O throughput rating on EC2

We are mainly concerned with the modern, Nitro EC2 m5, c5, r5, and r5b instance types. Throughput is defined as the rate, in Mbps for large (128 KiB) I/O, and could refer to a read, write, or mixed (read-write) workload.

EC2 throughput rating can be found by running “aws ec2 describe-instance-types”, and is divided into two ranges, according to instance size, as shown in the following figure. The number of CPUs in EC2 maps to instance size thus:

Number of CPUs Instance size
2 large
4 xlarge
8 2xlarge
16 4xlarge
32 8xlarge
48 12xlarge
64 16xlarge
96 24xlarge

At the low end, up to 4xlarge, throughput is capped at a single value, regardless of instance size. That cap depends on instance class. In r5b, the cap is 1250 Mbps, but for c5, m5, and r5, throughput is capped at 593.75 Mbps. Other instance classes have lower throughput caps. At the high end, 8xlarge and up, throughput depends on the instance size. The r5b instance class tops out at 7500 Mbps. m5, c5, and r5 classes have throughput that ranges from 850 Mbps to 2375 Mbps, depending on instance type.

By now we have described the available volume types and instance types. So let’s pose this question. If we configure 8 gp3 volumes at 1000 Mbps each, then the system should deliver 8000 Mbps throughput, right?

Wrong.

No EC2-EBS-Linux system today delivers 8000 Mbps throughput. You will get, at best, the throughput offered by the EC2 instance type. For example, if you build an m5.4xlarge instance, then the highest possible throughput will be 593.75 Mbps, as I mentioned. Also, 593.75 Mbps is the most you will get. Actually achieving that requires configuring multiple volumes.

Operating system description

The test system is an Amazon AWS EC2 instance running Red Hat Linux 7.9. The test software is orion, which is found in the Oracle home. The number of CPUs, the amount of memory, and the device controller interface depend on the EC2 instance type. For example, the m5.xlarge has 4 CPUs, 16 GB RAM, and a non-volatile memory express (NVMe) device controller interface.

Test description

100% write I/O was tested with orion. General purpose volumes (gp2 and gp3), and the lower cost st1 storage were tested with large writes. I/O optimized storage (io2) was tested at small I/O. Each test ran from 4 to 20 minutes, depending on the number of volumes. For the throughput tests, “Maximum Large MBPS” was extracted from the summary.txt file. In the small I/O tests, “Maximum Small IOPS” and “Minimum Small Latency” were extracted.

Instance type and volume type

In this section, the influence of instance type and volume type in investigated. The new I/O-optimized r5b instance type was tested with gp3 storage. General purpose m5 instance class was tested with gp2, gp3, and io2 storage. Additionally, the lower cost, burstable t3 instance class, and the previous generation m4 instance class were tested. The number of CPUs ranged from 2 to 96. As a rule of thumb, the amount of memory for the m5, t3, and m4 instance classes, in GB, is four times the number of CPU. Eight 125-GB volumes were tested in every run. In all, there were 31 test runs.

Throughput testing

General  purpose storage was tested for throughput. gp3 storage was configured for 1000 Mbps throughput per volume (the maximum) and 4000 IOPS The results are displayed here.Each point represents the maximum throughput obtained in a single test. The dark blue line, for example, displays Orion throughput results for 6 tests on r5b.large, r5b.4xlarge, r5b.8xlarge, r5b.12xlarge, r5b.16xlarge, r5b.24xlarge, The test points above 24 are results of separate tests on r5b.4xlarge, m5.4xlarge on gp3 volume type, m5.4xlarge on gp2 volume type, t3.4xlarge, m4.4xlarge, and m5.4xlarge on st1 volume type. Again, each point is a separate test, not Orion data points from a single test.

The figure shows actual system throughput for 8 volumes. We already said that just because you configure 8 1000 Mbps volumes does not mean that the observed throughput will be 8000 Mbps. Instead, the throughput is limited by the instance type.

The key points to notice in the throughput testing are:

  • The observed throughput is level across instance types up to 4xlarge (24 CPUs), just like the documentation says.
  • Starting at 8xlarge, (25 CPUs), gp3 throughput scales up with the instance size, reaching 7330 Mbps on the r5b.24xlarge instance type, and 2330 Mbps on m5.24xlarge, again, just like the documentation says.
  • Like gp3 on the m5, gp2 throughput is level at 580 Mbps for instances up to 4xlarge.
  • From 12xlarge and up, gp2 throughput is level at 1015 MPBs.
  • For t3.xlarge and up, throughput plateaus at 335 Mbps.
  • In older instance class m4, throughput ranges from 55 Mbps to 240 Mbps.

Notice that in high-end instance types, r5b throughput is better than 3x the m5 throughput. This is one example where actual observations match the marketing materials: New – Amazon EC2 R5b Instances Provide 3x Higher EBS Performance

By using the number of CPUs on the horizontal axis, we do not mean to imply that CPUs have a significant influence on system throughput in EC2. In fact, during ORION testing, CPU workload tends be low, and the same can be said for memory footprint. Plotting vs “Number of CPUs” is just meant to be a convenient method for having plotting I/O numbers vs instance size. AWS throttles throughput depending on instance type and size, separately from the number of CPUs.

IOPS testing

I/O Optimized storage io2 configured for 7000 IOPS per volume on the m5 class was also tested. 8 volumes were tested.Again, to clarify, configured IOPS for each volume was 7000. You might think that configuring 8 volumes are 7000 IOPS per volume should deliver 56000 IOPS. As you can see, actual IOPS is less.

The key IOPS testing observations are:

  • IOPs is flat at 18905.
  • Latency is flat at 600ms.

Conclusion

  • r5b throughput outperforms m5 by better than 3x.
  • I/O performance is level up to 4xlarge.
  • Starting at 8xlarge, throughput ramps up with instance class.
  • 24xlarge delivers the highest throughput.
  • t3 write throughput lags m5, and m4 write throughput lags t3.
  • The observed system I/O rate is much less than you would expect if you calculated I/O rate based on the volume configuration.

This section was an overview of the capabilities of various EC2 instance types and volume types with eight volumes. As we are about to see, tuning the number of volumes is crucial to optimizing instance performance.

Number of volumes

The influence of the number of volumes on I/O was tested for various instance types. The number of volumes ranged from 1 to 16. Throughput was tested on general purpose volume types gp2 and gp3. The gp3 storage was configured for 1000 Mbps and 4000 IOPs per volume. The results are shown in the following figure:Additionally, IOPS was measured on on I/O optimized volume type io2. IOPS was set at 7000 per volume. The results are shows here:The key findings are

  • For improved throughput, I/O should be distributed across multiple volumes.
  • In r5b.24xlarge, throughput increases all the way out to 7300 Mbps at 16 volumes.
  • In r5b8xlarge and m5.24xlarge, throughput levels off around 2400 Mbps at 8 volumes.
  • On m5.large, gp2 and gp3 throughput levels off at 580 Mbps at four volumes.
  • On m5.large, io2 IOPs plateaus at 18905 IOPs at four volumes.
  • io2 latency tends to remain below 625ms.

Be aware that the EC2 device attachment limit is 28, including the network adapter and up to 27 volumes, including the system disk. A practical Oracle ASM system could consist of two diskgroups and four or more disks per group.

In conclusion, one can expect improve I/O by implementing four volumes. In high end systems, configuring eight or more volumes is beneficial. Exercise prudence on the number of volumes, because throughput and IOPS charges are per-volume.

Configured volume throughput

So far, we have tested volumes that are over-provisioned, meaning that measured throughput turned out to be much less than rated throughput. You don’t get what you pay for. So how much throughput should you actually be configuring?

In this section, we investigate the effect of configured throughput versus actual throughput on gp3 volumes. gp3 volumes were configured at 3000 IOPs and a throughput range from 125 Mbps to 1000 Mbps. Various m5 and r5b instance types were tested. The results are shown here.In the m5.large, m5.24xlarge, and r5b.8xlarge cases, setting volume throughput to 375 Mbps achieves the maximum possible system throughput. Setting volume throughput higher than that leads to no further gains in performance. In r5b.12xlarge and r5b.24xlarge, performance can be maximized by setting throughput to 500 Mbps. In conclusion, although gp3 throughput can be adjusted to 1000 Mbps, there is no benefit to setting gp3 throughput above 500 Mbps, and only in rare cases is it beneficial to set gp3 throughput above 375 Mbps.

Intel vs. AMD

AWS offers a choice of Intel or AMD processor on Red Hat EC2. AMD EC2 prices are 10% lower than Intel. Rated EBS throughput on the AMD systems is less than on Intel.

AMD
 Intel
instance type throughput (Mbps) instance type throughput (Mbps)
t3a.medium t3.medium
m5a.large 2880 m5.large 4750
m5a.24xlarge 13570 m5.24xlarge 19000

The test system configurations were:

Instance type CPUs Volumes Storage Throughput IOPS
t3[a].medium 2 4 st1 –
m5[a].large 2 8 gp3 375 3000
m5[a].24xlarge 96 8 gp3 375 3000

“t3[a].medium” refers to a comparison of a t3a.medium (AMD) system to a t3.medium (Intel) system, and so on, for m5[a].large and m5[a].24xlarge.At the low end instance types, AMD and Intel performed equally. At the high end, Intel edged AMD.

Systems for different purposes

Recommendations for three different type of systems are presented here. The target system is Oracle Database on top of Grid Infrastructure on Red Hat. Only systems with at least 2 CPUs and 4 GB RAM are considered. A “system” could refer to an ASM diskgroup, such as DATA, RECO, or REDO.

Low cost

You might need a low cost system for light development, well-tuned reports, archiving, etc. Use the t3a instance class and st1 storage.

General purpose

For general purpose production systems, use the m5 class. If you want to halve the memory, substitute c5 and save 9% to 11%. To double the system memory, use r5 and pay 24% to 30% more. Configure four or more gp3 volumes per ASM diskgroup at 125 Mbps to 375 Mbps throughput and 3000 IOPS.

High throughput

For highest large write throughput on EBS, use r5b.24xlarge, and at 16 gp3 volumes configured at 500 Mbps.

Example systems and cost

Example of the three types of system are presented in this table. This time, we present CPU and storage cost, assuming Red Hat Linux and on demand pricing in region us-east-1, as of 7/24/2021.

Purpose Instance Type Vol type Num CPU Vol Size Num Vols Thr IOPS Actual Mbps EC2 EBS Total
Low cost t3a.medium st1 2 125 8 – – 245 $71 $45 $116
General purpose m5.large gp3 2 250 4 125 3000 497 $114 $80 $194
High throughput r5b.24xlarge gp3 96 1000 16 500 3000 7330 $5316 $1522 $6838

Do not waste money

Follow this guidance to avoid wasting money

  • Avoid io2 EBS for general purpose Oracle databases. The cost is prohibitive.
  • In gp3, configure throughput at 500 Mbps or less, and configure 3000 IOPS.
  • For good, economical performance, configure gp3 at 125 Mbps and 3000 IOPS.

Workload

This article offers guidance on configuring the AWS EBS at 100% write. The findings apply to any diskgroup, particularly to redo diskgroups and to databases with high throughput requirements, such as ETL systems. The information is among many factors to consider when specifying a practical database system. Orion is also capable of simulating a 100% read workload, and a mixed workload. Mixed workload Orion results could be used to look for further cost reductions. Actual database application performance depends not only on storage throughput, but also on processor design, amount and speed of memory, and table design.

ASM diskgroup layout

Oracle makes ASM diskgroup layout recommendations: RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) (Doc ID 810394.1).

Oracle recommends a minimum 4 disks per diskgroup:My own test results show that a minimum of 4 LUNs per diskgroup lead to optimal throughput. Configuring 4 or more LUNs per diskgroup is extremely important.

Oracle recommends no more than 2 ASM diskgroups:Benefits of no more than 2 ASM diskgroups:

  • Simplified administration.
  • High throughput in all diskgroups.
  • Avoid approaching the EC2 attachment limit.

If you configure more than 2 diskgroups, you could find yourself making design compromises at exactly the wrong places. For example, online redo logs are critical, high throughput components. If you configure separate REDO diskgroups in EC2, then you may find it difficult to keep to 4 disks per diskgroup, and still allow for expansion and remain within the attachment limit. Or you may find that so many diskgroups leads to a manageability issue.

Conclusion

The r5b instance class with gp3 storage delivers the highest performing I/O. m5, c5, and r5 instance classes make well-performing, general purpose systems with high throughput. The t3a instance class with st1 storage makes a fair-performing, low cost system. Each ASM disk groups should be configured with a minimum of 4 disks and more than 4 disks for high throughput r5b systems. For optimal throughput, gp3 EBS should be configured at 3000 IOPS and no more than 500 Mbps.

Connecting across VPCs using Peering

Posted on by
Reply

By Brian Fitzgerald

Introduction

The requirement to connect applications across regions is ubiquitous. In Amazon Web Services (AWS), applications are deployed to a Virtual Private Cloud (VPC), but a VPC is specific to a single AWS region — to connect across regions, it is necessary to connect across VPCs. For speed and security, it is preferable to connect VPCs across Amazon’s internal networks, not across the public internet. We are going to establish our cross-VPC connection using peering. For this peering to succeed, planning is necessary to avoid overlapping IP address ranges. Peering across AWS accounts will also be demonstrated.

Virtual Private Clouds

An Amazon AWS Virtual Private Cloud (VPC) is an isolated network in a single region. A VPC covers all availability zones in the region and can have multiple subnets. A VPC covers a specific CIDR (Classless Inter-Domain Routing) IP address range, or “block”. In this section, we’re going to cover VPC IP address ranges, which is going to lead into the next section on VPC peering.

Networks

IP network configuration in AWS VPCs is quite flexible. The network number can reflect almost any legal IPv4 address range. IPv6 CIDR ranges are also available. A VPC CIDR block size can range from /16 netmask (65534 IP addresses) to /28 netmask (14 IP addresses).

Amazon recommends that you specify a CIDR block from the private IPv4 address ranges as specified in RFC 1918:

  • 10.0.0.0 – 10.255.255.255 (10/8 prefix)
  • 172.16.0.0 – 172.31.255.255 (172.16/12 prefix)
  • 192.168.0.0 – 192.168.255.255 (192.168/16 prefix)

If you use an AWS tool to automatically create a VPC, you will find that the generated CIDR follows that guidance. Here are some examples of VPC CIDR blocks generated by AWS tools:

  • 10.0.0.0/16
  • 172.31.0.0/16

You may create a VPC with a CIDR block outside the RFC 1918 ranges, but most users will refrain from doing so on aesthetic grounds, or to avoid misunderstandings. You may not create an AWS VPC CIDR block beginning with 0. or 127.

The default limit on VPCs is 5 = per region. To get a higher limit, you have to open a support case and submit a limit increase request. To avoid needing a higher VPC limit, you might decide to create your VPCs as large as allowable, i.e. netmask /16, and to avoid inadvertently creating ones. Some AWS only work by creating a new VPC. For example, if you use the Getting Started menu to setup an Elastic Container Service with load balancing, you will have no option but to create a new VPC. Other AWS tools offer to create a new VPC For example if you create a new a new EC2 or RDS instance in the AWS console, the menu offers creating a new VPC as as option. In conclusion, you can tightly manage your number of VPCs, or you can request a higher limit.

One way to simplify and standardize your administration is to create all your VPCs using RFC 1918 IP address ranges and netmask /16. If you do that, you can create networks among these ranges

  • 10.0.0.0/16 – 10.255.0.0/16 (256 networks)
  • 172.16.0.0/16 – 172.31.0.0/16 (16 networks)
  • 196.168.0.0/16 (1 network)

for a total of up to 273 VPCs per region. For many applications, 273 VPCs per region is ample.

In a new account, the default VPC in each region has CIDR block 172.31.0.0/16. As of this writing, a new account covers 17 regions. The upshot is that a new AWS account has 17 VPCs all with the same CIDR block. In the case of isolated VPCs, this is not a problem, but default VPCs cannot be connected by peering because the IP address ranges overlap.

In addition to creating new VPCs, you can increase the size of an existing VPC by adding additional CIDR blocks. However, you cannot mix across RFC 1918 IP address ranges. Specifically, you cannot combine “10.0” and “172.” CIDR blocks in a single VPC.

In conclusion, if you consider all allowable ranges and netmasks, you can choose from over a half billion possible CIDR blocks. However, even if you restrict your choice to private networks and the largest allowable size, you can choose from among 273 different CIDR blocks.

Subnets

Subnetting VPCs is also quite flexible. You may specify a subnet mask ranging from /16 (65534 IP addresses) to /28 (14 IP addresses). By default, a VPC may have up to 200 subnets.

You cannot create a subnet larger than netmask /16. For example, if you compose a VPC from two contiguous netmask /16 networks, you could not, therefore, create a single netmask /15 subnet.

VPC summary

A new account covers multiple regions, each with a default VPC with CIDR block range 172.31.0.0/16. You can create additional VPCs. Some users may decide to stick with netmask /16 and RFC 1918 networks. The detailed explanations of VPC IP addresses in this section set the stage for the next section, which is VPC peering.

VPC Peering

A simple way to connect across VPCs is to establish VPC peering. VPC peering connects two VPCs to form a single network. Traffic is routed not across the Internet, but across a private AWS network. VPC peering is more secure and more reliable than using an internet gateway.

VPC peering requires that the VPC CIDR blocks do not overlap. Subnets are not considered. In other words, if two VPCs have overlapping CIDRs, you cannot establish VPC peering, even if no existing subnets overlap.

All default VPC CIDR blocks are 172.31.0.0/16. You cannot establish VPC peering across more than one default VPC because the IP address range overlaps. You must create one or more new VPC.

For this blog, we’re going to setup VPC peering across regions us-east-1, ap-northeast-1, and eu-west-1 (N. Virginia, Tokyo, and London). We’ll start by deleting the default VPC, namely 172.31.0.0/16, and creating these VPCs

Region Region VPC ID CIDR block
N. Virginia us-east-1 vpc-0ed2447f33a01d301 10.1.0.0/16
Tokyo ap-northeast-1 vpc-07251b9829e270787 10.2.0.0/16
London eu-west-2 vpc-0bf90b5507089c175 10.3.0.0/16

For example:

vpc

I have deleted the default VPCs for neatness: I have no need for them now. In each new VPC, create a subnet. Also, only for the sake of this blog, I’ll create an internet gateway in us-east-1 and add a route to the Internet via the gateway. Spin up an EC2, download the key pair, convert to putty keys, connect with PuTTY and we’re in. Install nc:

sudo yum -y update
sudo yum -y install nc

In ap-northeast-1 and eu-west-2, spin up an EC2 in each. Save the SSH keys (*.pem) for later.

Region Subnet EC2 IP address public IP address
us-east-1 10.1.0.0/24 10.1.0.244 107.23.67.190
ap-northeast-1 10.2.0.0/24 10.2.0.241
eu-west-2 10.3.0.0/24 10.3.0.183

Attempt to connect from us-east-1 to ap-northeast-1 and eu-west-2, and the connections time out.

timeout

Now, we’ll setup VPC peering.

Setting up VPC peering

To set up VPC peering, send an invitation by following the Create Peering Connection dialog. For example, from us-east-1, invite ap-northeast-1.

invite

In the ap-northeast-1 region, accept the invitation.

accept

In the “Actions” menu, select “Accept request”. In the dialog. click “Yes, Accept”, and in the next dialog, click “Modify my route tables now”, or select “Route Tables” from the left navigation pane.

In the us-east-1 route table, add a route to 10.2.0.0/16 via the peered connection. In the ap-northeast-1 route table, add a route to 10.1.0.0/16 via the peered connection. You can skip ahead to the screenshots in the next subsection to get a preview of the final route table.

Retest the connection to ap-northeast-1. Success:

tcp22ok

Likewise:

  • In us-east-1, invite to peering, eu-west-2 VPC.
  • In eu-west-2, accept the peering invitation.
  • In the us-east-1 route table, add a route to 10.3.0.0/16 via the peering connection.
  • In the eu-west-1 route table, add a route to 10.1.0.0/16 via the peering connection.

Using WinSCP, copy the *.pem files that you downloaded when you created the EC2s to the us-east-1 EC1. Change the file mode to 600. Now ssh succeeds:

from us-east-1:
ssh -i ap-northeast-1-key.pem ec2-user@10.2.0.241
ssh -i eu-west-2-key.pem ec2-user@10.3.0.183

Connecting Tokyo to London

Peering is not transitive, meaning that, so far, you cannot connect directly from ap-northeast-1 to eu-west-2 or vice versa. You may, however, setup peering directly between ap-northeast-1 and eu-west-2. Be sure to update the route tables.The final us-east-1 route table looks thus:

rout.us-east-1

The ap-northeast-1 route table is:

rout.ap-northeast-1

The eu-west-2 route table is:

rout.eu-west-2

Once routing is setup, you can connect between any two IP addresses in the three regions.

If you want to ssh from ap-northeast-1 to eu-west-2, then copy eu-west-2-key.pem to  ap-northeast-1 first.

from us-east-1:
cd .ssh/
scp -i ap-northeast-1-key.pem -p eu-west-2-key.pem  ec2-user@10.2.0.241:.ssh
ssh -i ap-northeast-1-key.pem ec2-user@10.2.0.241
from ap-northeast-1:
cd .ssh/
ssh -i eu-west-2-key.pem ec2-user@10.3.0.183

Again, note that only the us-east-1 EC2 instance is public. The ap-northeast-1 and eu-west-1 EC2 instances are private, and are accessible only via the us-east-1 EC2 instance.

This was an example of interconnecting three regions in the same account. The connections were accomplished using ssh (port 22). In the next section, we will connect across two separate AWS accounts via Oracle database link.

Connection across accounts

So far, we have setup VPC peering across regions in the same account. Now we are going to establish VPC peering across separate AWS accounts. Setup your accounts. Setup VPCs with non-overlapping CIDR blocks. For example:

Account number Region Region ID VPC ID CIDR block
665575760545 Seoul ap-northeast-2 vpc-04260ecd771d09cdb 10.5.0.0/16
128887077649 Singapore ap-southeast-1 vpc-043e1448a4e98a416 10.6.0.0/16

In each VPC, setup at least two subnets in separate availability zones.

Account number VPC ID Subnet Availability Zone
665575760545 vpc-04260ecd771d09cdb 10.5.0.0/24 ap-northeast-2b (apne2-az2)
665575760545 vpc-04260ecd771d09cdb 10.5.1.0/24 ap-northeast-2a (apne2-az1)
128887077649 vpc-043e1448a4e98a416 10.6.0.0/24 ap-southeast-1c (apse1-az3)
128887077649 vpc-043e1448a4e98a416 10.6.1.0/24 ap-southeast-1a (apse1-az1)

Create databases

In the first account:

  • Setup an internet gateway and a route for the sake of this blog.
  • Enable DNS hostnames.
  • Create an Oracle Database RDS (internet facing for the sake of this blog).
  • Test from Oracle SQL Developer.

dbsuccess

In the second account, create an Oracle Database RDS, private. Enable listener log exports. RDS Summary:

Account number DB ident Endpoint
665575760545 seoul-ora seoul-ora.c7oolvrrvu91.ap-northeast-2.rds.amazonaws.com
128887077649 singapore-ora singapore-ora.cdhkgqcl8pkk.ap-southeast-1.rds.amazonaws.com

(… continued)

Account number IP Address PORT db name
665575760545 52.79.225.94 1521 ORCL
128887077649 10.6.1.194 1521 ORCL

Note that the seoul-ora IP address is public and the singapore-ora IP address is private.

From the first account, from seoul-ora, the create database link statement succeeds:

CREATE DATABASE LINK singapore_link 
CONNECT TO admin IDENTIFIED BY "sing..33"
USING 'singapore-ora.cdhkgqcl8pkk.ap-southeast-1.rds.amazonaws.com:1521/ORCL';

A query across the database link times out:

select host_name from v$instance@singapore_link;
ORA-12170: TNS:Connect timeout occurred

Next we will setup VPC peering across accounts.

VPC Peering across accounts

From the first account, in Seoul, send the invitation:

invite.acct

From the second account, in Singapore, accept the invitation.

accept.acct

Click “Yes, Accept”, and in the next dialog, click “Modify my route tables now”, or select “Route Tables” from the left navigation pane. Add a route to destination 10.5.0.0/16 via peering connection pcx-09199f486b1e1a533.

From the first account, in Seoul, add a route to 10.6.0.0/16 via peering connection pcx-09199f486b1e1a533.

From the second account:

  • Select Singapore
  • Navigate to Services->RDS
  • Select singapore-ora
  • Identify the security group
  • Navigate to the security group
  • Add inbound rule:
    • TCP port: 1521
    • Source: 10.5.0.0/16

Here is a screenshot of the Singapore RDS security group inbound rules.

sing.sgRetry the database link from Seoul:

select host_name from v$instance@singapore_link;
HOST_NAME
ip-172-21-2-91

Success. Peering across AWS accounts works.

Note that the query returns the Singapore RDS hostname, ip-172-21-2-91. The host is an EC2 instance that is not accessible from your AWS account.

In the second account, in Singapore, CloudWatch, in Logs, in /aws/rds/instance/singapore-ora/listener, observe the establish record.

12-JUN-2019 21:44:13 * (CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=
 (PROGRAM=oracle)(HOST=ip-172-23-0-229)(USER=Brian Fitzgerald))) 
 * (ADDRESS=(PROTOCOL=tcp)(HOST=10.5.1.93)(PORT=64171)) 
 * establish * ORCL * 0

Notice that connection source IP address 10.5.1.93 is in Seoul VPC CIDR block 10.5.0.0/16.

Again, note that only the ap-northeast-2 (Seoul) RDS instance is public. The ap-southeast-1 (Singapore) RDS instances is private, and is accessible only via the ap-northeast-2 RDS instance.

Our result: a successful TCP connection across AWS accounts. In this case, we set up a database link across Oracle databases. The connection could as well have been a Microsoft SQL Server linked server, or a database client, such as ODBC or JDBC. VPC peering is not limited to database technology. A wide range of applications, tools, and services can be deployed across AWS VPCs, regions, or accounts by leveraging VPC peering. This blog has covered TCP over IPv4, but other transports, such as UDP can be considered. IPv6 is supported in AWS, as well as IPv4.

Programming

All actions that were demonstrated from the AWS console can be accomplished programmatically.

Command Line Interfaces

The AWS command line interface (CLI) can be used to issue commands that perform the same actions that were demonstrated from the AWS console. For example, to drop and reinstatiate N. Virginia to Tokyo peering:

C:\>aws ec2 delete-vpc-peering-connection --vpc-peering-connection-id pcx-0006343192557953b
True
C:\>aws ec2 create-vpc-peering-connection --peer-vpc-id vpc-07251b9829e270787 --vpc-id vpc-0ed2447f33a01d301 --peer-region ap-northeast-1
VPCPEERINGCONNECTION    2019-06-21T01:54:00.000Z        pcx-0d5aa3deb15773138
ACCEPTERVPCINFO 665575760545    ap-northeast-1  vpc-07251b9829e270787
REQUESTERVPCINFO        10.1.0.0/16     665575760545    us-east-1       vpc-0ed2447f33a01d301
CIDRBLOCKSET    10.1.0.0/16
PEERINGOPTIONS  False   False   False
STATUS  initiating-request      Initiating Request to 665575760545

C:\>aws ec2 accept-vpc-peering-connection --vpc-peering-connection-id pcx-0d5aa3deb15773138 --region ap-northeast-1
VPCPEERINGCONNECTION    pcx-0d5aa3deb15773138
ACCEPTERVPCINFO 10.2.0.0/16     665575760545    ap-northeast-1  vpc-07251b9829e270787
CIDRBLOCKSET    10.2.0.0/16
PEERINGOPTIONS  False   False   False
REQUESTERVPCINFO        10.1.0.0/16     665575760545    us-east-1       vpc-0ed2447f33a01d301
CIDRBLOCKSET    10.1.0.0/16
PEERINGOPTIONS  False   False   False
STATUS  provisioning    Provisioning

All other actions demonstrated from the AWS console in this blog article can be run from the CLI. Examples:

aws ec2 create-vpc
aws ec2 create-route
aws ec2 create-subnet
aws ec2 authorize-security-group-ingress

to mention only a few.

Programming APIs

The commands can be scripted in several languages, including javascript, powershell, and python. The python library is boto3. EC2 client methods include these examples:

create_vpc()
create_vpc_peering_connection()
accept_vpc_peering_connection()
create_route()
create_subnet()

 

Technical summary

The key details needed to setup VPC peering are:

  • Non-overlapping IP address ranges across VPCs.
  • Send and accept invitation.
  • Adding routes to the route table.
  • Security group inbound rules that cover the remote IP address ranges.

In this blog article, we explained, demonstrated, or mentioned:

  • AWS VPC peering across three regions.
  • Deleting the default VPC
  • Creating new VPCs
  • Use of RFC 1918 private IP address ranges
  • Use of /16 netmask for VPC
  • Sending and accepting VPC peering invitations.
  • Adding routes to the route table
  • Creation of subnets
  • Use of /24 subnet mask
  • Downloading ssh key pairs (pem)
  • Using PuTTYgen to convert ssh keys to putty keys (ppk).
  • Connecting to EC2 via PuTTY or WinSCP.
  • VPC peering across AWS accounts
  • Installing nc
  • Using nc in EC2 to test TCP connectivity
  • ssh across EC2 using ssh key pairs (pem)
  • Limiting RDS access via security groups.
  • Creating a database link
  • Reviewing RDS listener log

Conclusion

AWS VPC peering is a great way to connect applications across VPCs, regions, or accounts. VPC peering is faster, more reliable, and more secure than using the Internet. VPC peering can be implemented smoothly by avoiding overlapping IP address ranges. This blog covered ssh and database connections, but VPC peering applies to a wide range of networked application technology.

move LOB results in unusable indexes

Posted on by
Reply

By Brian Fitzgerald

It is well known that moving a table segment causes indexes to become unusable. It is less known that moving an LOB causes indexes to become unusable.

Moving a table segment causes indexes to become unusable because indexes point to the table’s physical location. After a table move, the index still points to the old table location, so the index is invalid. Oracle marks the index as unusable.

alter table t
move
tablespace ts0002

If a table has an LOB column, you can move the table and the LOB in a single command. Again, the move result in unusable indexes.

alter table t
move
tablespace ts0002
lob ( l ) store as ( tablespace ts0002 )

If only the LOB is moved, not the table, indexes are also left unusable. This has surprised some users, but it is the expected behavior. The behavior is documented.

alter table t
move
lob ( l ) store as ( tablespace ts0002 )

Further investigation shows that when an LOB is moved, the table segment is also “moved”. The table data_object_id and the table segment header block change, and every rowid in the table segment changes. Indexes on the table become unusable.

Unusable indexes are unintuitive because the command syntax is to move an LOB column, and the column is not indexed. Why should an index become unusable if the column that is moved is not indexed?

A Google search on “move lob unusable index” turns up questions and answers on this subject, and lead to the documentation, and to Note 1228324.1, which state that moving and LOB leading to unusable indexes is the expected behavior.

Point taken.

But why?

Specifically, why does moving an LOB result in “moving” the table segment?

The reason is clear when you consider that whether the move succeeds of fails, the operation must leave the schema in a consistent state.

Moving an LOB requires updating the LOB locator in every row. It’s similar to an update of every row. Generally, updates can be done conventional or direct path. The choice is an implementation decision. Direct path can be more efficient and less dependent on instance resources such as memory and undo. Alter table is DDL. Direct path is commonly used in DDL.

A DDL operation must succeed or fail atomically. We don’t want to end up half way where some LOBs are migrated and some not. The LOBs are in a single segment, which is the smallest unit of administration. You can’t move part of a segment. The entire move must succeed or fail. This is implemented by making a copy of the LOB segment, and a copy of the table segment. When the copying is complete, the LOB and the table are switched to point to the new copies. After a successful LOB move, the LOB has been copied, the table has been copied, and the new LOB locators point to the copied LOB. If the alter table is interrupted, the copies are discarded and the state is the same as it was to start with. After a failed LOB move, the original LOB locators simply point to the original LOB.

Note that moving an LOB will require not only free space for the new LOB, but also, space for the table.

What follows is a demo of moving an LOB only. There are a few points to notice:

  • Table T LOB column L moves from tablespace TS0001 to TS0002
  • Table T tablespace remains TS0001.
  • Table T data object id changes from 74914 to 74918. The segment in fact “moved”.
  • Table T segment header block changes from 130 to 170. The segment “moved”.
  • Because the segment moved, index I is left unusable
  • tkprof shows direct path read and write, supporting the earlier comment.

Conclusions:

  • Whether you move the table, the LOB, or both, indexes will become unusable.
  • Unusable indexes are a result of a direct path LOB move implementation.
  • Space for the table and the LOB moves have to be planned for.
conn u/u
@ columnformat.sql
set linesize 32767
set trimspool on
column l format a1

drop table t purge;
create table t
(
c varchar2(1),
l clob
)
lob ( l )
store as ( tablespace ts0001 disable storage in row )
tablespace ts0001
;

insert into t ( c, l )
select 'A', 'Z'
from dual
connect by level <= 1000
;
commit;
create index i on t ( c );

select df.file_id,
df.tablespace_name
from dba_data_files df
where df.tablespace_name in ( 'TS0001', 'TS0002' );

@ trace.10046.on.sql

@ sel.t.info.sql

prompt moving the LOB only
alter table t
move
lob ( l ) store as ( tablespace ts0002 )
;

@ sel.t.info.sql
@ trace.10046.on.sql
@ tkprof.sql

SQL> @ movelob.sql
Connected.

Table dropped.

Table created.

1000 rows created.

Commit complete.

Index created.

FILE_ID TABLESPACE_NAME
---------- ------------------------------
 8 TS0001
 9 TS0002

Session altered.

Session altered.

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_BLOCK EXTENTS
------------------------------ ------------------------------ ------------------ ------------------------------ ------------ ----------
U T TABLE TS0001 130 2

OWNER OBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID
------------------------------ ------------------------------ ------------------------------ --------------
U T TABLE 74914

OWNER TABLE_NAME COLUMN_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
U T L TS0001

TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
T I VALID

moving the LOB only

Table altered.

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_BLOCK EXTENTS
------------------------------ ------------------------------ ------------------ ------------------------------ ------------ ----------
U T TABLE TS0001 178 2

OWNER OBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID
------------------------------ ------------------------------ ------------------------------ --------------
U T TABLE 74918

OWNER TABLE_NAME COLUMN_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
U T L TS0002

TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
T I UNUSABLE

Session altered.

Session altered.

TRACEFILENAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_15552.trc

BASE
-----------------
db12201_ora_15552

TKPROF: Release 12.2.0.1.0 - Development on Sun Nov 19 18:20:59 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

created file:
db12201_ora_15552.tkp

********************************************************************************

alter table t
move
lob ( l ) store as ( tablespace ts0002 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 1 0
Execute 1 0.58 13.03 1004 87184 61243 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.59 13.03 1004 87186 61244 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
 0 0 0 LOAD AS SELECT T (cr=87182 pr=1000 pw=1007 time=12586318 us starts=1)
 1000 1000 1000 TABLE ACCESS FULL T (cr=16 pr=0 pw=0 time=0 us starts=1 cost=5 size=237000 card=1000)

Elapsed times include waiting on following events:
 Event waited on Times Max. Wait Total Waited
 ---------------------------------------- Waited ---------- ------------
 PGA memory operation 3 0.00 0.00
 direct path read 999 0.88 11.40
 local write wait 8 0.00 0.00
 acknowledge over PGA limit 1 0.00 0.00
 direct path write 1 0.01 0.01
 enq: RO - fast object reuse 4 0.06 0.12
 db file sequential read 4 0.15 0.15
 enq: CR - block range reuse ckpt 4 0.14 0.14
 log file sync 1 0.00 0.00
 SQL*Net message to client 1 0.00 0.00
 SQL*Net message from client 1 0.00 0.00
********************************************************************************

 

 

 

 

 

 

 

 

 

 

Scripts for trace 10053

Posted on by
Reply

By Brian Fitzgerald

Trace 10053 tells you “how” the optimizer selected its plan — the reasons for the choices it has made. The main fact to remember when you are collecting trace 10053 is that you must force a hard parse each time you run your statement. Although forcing a hard parse can be done by manually varying the statement’s SQL text, this blog post offers a way to automate some of the steps, in case you need to quickly make a high number of trace 10053 runs.

For the sake of this blog article, we’ll use trace 10053 to examine the effect of statistics on the execution plan.

Preliminary: To run trace 10053, the user must be granted alter session.

SQL> grant alter session to scott;

Grant succeeded.

Trace 10053 with manual steps

In order to analyze the effect of gathering statistics on execution plan, we’ll run a statement twice, once without statistics and once with statistics. We’ll cycle the pluggable database to guarantee at least one hard parse to start with. Here is file demo.softparse.10053.sql

set pagesize 50000
conn / as sysdba
alter pluggable database pdba close;
alter pluggable database pdba open;
@ conn.pdba.scott.sql

alter session set tracefile_identifier = a;
exec dbms_stats.delete_schema_stats(user);
alter session set events='10053 trace name context forever, level 1';
select e.ename, e.deptno
from emp e
;
alter session set events '10053 trace name context off';
select value tracefilename
from v$diag_info
where name = 'Default Trace File';

alter session set tracefile_identifier = b;
exec dbms_stats.gather_schema_stats(user);
alter session set events='10053 trace name context forever, level 1';
select e.ename, e.deptno
from emp e
;
alter session set events '10053 trace name context off';
select value tracefilename
from v$diag_info
where name = 'Default Trace File';

The output:

[oracle@stormking cdb12201 sql]$ sqlplus /nolog @ demo.softparse.10053.sql
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 7 21:25:35 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected.
Pluggable database altered.
Pluggable database altered.
Connected.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
Session altered.
TRACEFILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_16771_A.trc
Session altered.
PL/SQL procedure successfully completed.
Session altered.
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
Session altered.
TRACEFILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_16771_B.trc
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@stormking cdb12201 sql]$ ls -l /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_16771_{A,B}.trc
ls: cannot access /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_16771_B.trc: No such file or directory
-rw-r----- 1 oracle asmadmin 99753 Aug 7 21:25 /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_16771_A.trc

Whoops, where is my second trace file? The second trace file is not there because I did not force a hard parse on the second statement execution. The SQL text was exactly the same in both runs:

select e.ename, e.deptno
from emp e
;

We failed to collect a trace 10053 for the second execution.

I could work around by manually varying the white space, upper case, or adding a comment. There are other ways to force a hard parse, that sometimes work, such as issuing a grant or flushing the statement from the shared pool, if you have the privilege.

In the next example, the statements are the same, except that SELECT is in caps in the first, and FROM is in caps in the second. These SQL text variations were made manually with an editor. Here is how demo.hardparse.10053.sql differs from the original:

[oracle@stormking cdb12201 sql]$ diff demo.softparse.10053.sql demo.hardparse.10053.sql
10c10
< select e.ename, e.deptno
---
> SELECT e.ename, e.deptno
22c22
< from emp e
---
> FROM emp e
YYY

The output :

[oracle@stormking cdb12201 sql]$ sqlplus /nolog @ demo.hardparse.10053.sql
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 7 21:34:36 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected.
Pluggable database altered.
Pluggable database altered.
Connected.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
Session altered.
TRACEFILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_18928_A.trc
Session altered.
PL/SQL procedure successfully completed.
Session altered.
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
Session altered.
TRACEFILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_18928_B.trc
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@stormking cdb12201 sql]$ ls -l /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_18928_{A,B}.trc
-rw-r----- 1 oracle asmadmin 99909 Aug 7 21:35 /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_18928_A.trc
-rw-r----- 1 oracle asmadmin 98628 Aug 7 21:35 /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_18928_B.trc

Notice that there are two 10053 trace files this time, one for each statement. The statements are unique, so each one was hard parsed.

Notice also the trace file continuation message from file A to file B:

[oracle@stormking cdb12201 sql]$ tail -2 /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_18928_A.trc
*** TRACE CONTINUES IN FILE /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_18928_B.trc ***

Issuing alter session set tracefile_identifier = b forced a switch to a new trace file.

That’s the basic idea, force a hard parse and switch trace file each execution, but I want to automate the process because in the future, I want to make a high number of trace 10053 runs.

Scripts for trace 10053

My demo queries are in directory /home/oracle/brian/sql/sql, but I plan to reuse my trace in the future. I set SQLPATH, and put reusable scripts there.

[oracle@stormking cdb12201 sqlplus]$ echo $SQLPATH
/home/oracle/brian/sql/sqlplus

Here are the scripts:

conn.pdba.scott.sql

A one-liner for connecting to the schema. I will reuse this script many times.

conn scott/tiger@stormking:1521/pdba

trace.10053.on.sql

Here is a one-line script for starting the trace 10053. Using this script is a lot easier than remembering the exact syntax, and it makes for an uncluttered main script.

alter session set events='10053 trace name context forever, level 1';

trace.10053.off.sql

Here is the script for stopping trace 10053. If you do not explicitly turn off trace, you will not get your trace file.

alter session set events '10053 trace name context off';

tracefilename.sql

This script displays the trace file name. It’s a better approach than searching for the file manually.

set echo off
column tracefilename format a100
set trimspool on
select value tracefilename
from v$diag_info
where name = 'Default Trace File';

randident.sql

I want to use a random identifier in two places. I want a random trace file identifier, and I want a random identifier somewhere in my statement.

-- set sqlplus substitution variable randident
-- to a random character string
set termout off
column randident new_value randident format a6
select dbms_random.string('u',6) randident from dual;
set termout on

randtfi.sql

This script sets the tracefile identifier to a random string. Notice that each time you call randtfi.sql, Oracle switches to a new trace file. That’s handy if you want to break up the trace for a single sqlplus session into multiple parts.

-- Change to a new, randomly named trace file on each call
@ randident.sql
alter session set tracefile_identifier = &&randident;

This is my collection of reusable scripts. They go in /home/oracle/brian/sql/sqlplus

Demonstrating the scripts

The demonstration is a throwaway example. The script does not belong in SQLPLATH. I have put the script in /home/oracle/brian/sql/sql. Here is demo.trace.10053.sql:

set pagesize 50000
@ conn.pdba.scott.sql

exec dbms_stats.delete_schema_stats(user);
@ randtfi.sql
@ trace.10053.on.sql
@ randident.sql
set echo on
set verify on
select e.ename &&randident, e.deptno
from emp e
;
set echo off
@ trace.10053.off.sql
@ tracefilename.sql

exec dbms_stats.gather_schema_stats(user);
@ randtfi.sql
@ trace.10053.on.sql
@ randident.sql
set echo on
set verify on
select e.ename &&randident, e.deptno
from emp e
;
set echo off
@ trace.10053.off.sql
@ tracefilename.sql

Running demo.trace.10053.sql:

[oracle@stormking cdb12201 sql]$ sqlplus /nolog @ demo.trace.10053.sql
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 7 21:50:53 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected.
PL/SQL procedure successfully completed.
Session altered.
Session altered.
SQL> set verify on
SQL> select e.ename &&randident, e.deptno
 2 from emp e
 3 ;
old 1: select e.ename &&randident, e.deptno
new 1: select e.ename AWQVDZ, e.deptno
AWQVDZ DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
SQL> set echo off
Session altered.
TRACEFILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_22121_RYNDLO.trc
PL/SQL procedure successfully completed.
Session altered.
Session altered.
SQL> set verify on
SQL> select e.ename &&randident, e.deptno
 2 from emp e
 3 ;
old 1: select e.ename &&randident, e.deptno
new 1: select e.ename ZRQGGE, e.deptno
ZRQGGE DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
SQL> set echo off
Session altered.
TRACEFILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_22121_XGLYME.trc
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@stormking cdb12201 sql]$ grep '^sql_id' /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_22121_RYNDLO.trc /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_22121_XGLYME.trc
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_22121_RYNDLO.trc:sql_id=6z7nrbdts160v plan_hash_value=-338806364 problem_type=3
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_22121_XGLYME.trc:sql_id=5ub8qv1cugvr4 plan_hash_value=-338806364 problem_type=3

Again, there are two trace files. The first trace file has a continuation message on to the second:

[oracle@stormking cdb12201 sql]$ tail -2 /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_22121_RYNDLO.trc
*** TRACE CONTINUES IN FILE /u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_22121_XGLYME.trc ***

I have varied the output column name:

new 1: select e.ename AWQVDZ, e.deptno

vs.

new 1: select e.ename ZRQGGE, e.deptno

Because the SQL is different each time, a hard parse is forced each execution. I could have also forced a hard parse by placing the random string in a comment:

/* &&randident */

Now, on to the analysis and comparison of the two trace files.

In the first execution, statistics had been deleted. The trace 10053 was collected in file cdb12201_ora_22121_RYNDLO.trc. The plan is:

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | TABLE ACCESS FULL | EMP | 14 | 280 | 3 | 00:00:01 |
-------------------------------------+-----------------------------------+

Trace cdb12201_ora_22121_XGLYME.trc, with statistics, show this plan:

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 | 00:00:01 |
-------------------------------------+-----------------------------------+

The plan hash value in both cases is -338806364. The rows and blocks estimate is the same, and the bytes estimate is slightly different.

Trace cdb12201_ora_22121_RYNDLO.trc contains a dynamic sampling analysis:

*** 2017-08-07T21:50:53.660117-04:00 (PDBA(3))
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: PK_EMP, blocks=1
** Dynamic sampling updated table stats.: blocks=5

*** 2017-08-07T21:50:53.660117-04:00 (PDBA(3))
** Generated dynamic sampling query:
 query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("E") FULL("E") NO_PARALLEL_INDEX("E") */ 1 AS C1, 1 AS C2 FROM "SCOTT"."EMP" "E") SAMPLESUB

*** 2017-08-07T21:50:53.660117-04:00 (PDBA(3))
** Executed dynamic sampling query:
 level : 2
 sample pct. : 100.000000
 actual sample size : 14
 filtered sample card. : 14
 orig. card. : 409
 block cnt. table stat. : 5
 block cnt. for sampling: 5
 max. sample block cnt. : 64
 sample block cnt. : 5
 min. sel. est. : -1.00000000

In the second execution, statistics had been collected. The trace is cdb12201_ora_22121_XGLYME.trc. No dynamic sampling appears.

The analysis of the bytes difference follows. For the case of gathered stats, the row length is:

SQL> select sum(avg_col_len) from user_tab_columns 
where table_name = 'EMP' 
and column_name in ('ENAME','DEPTNO');

SUM(AVG_COL_LEN)
----------------
 9

9 bytes per row x 14 rows = 126 bytes, which is the bytes value in the plan, step 2 TABLE ACCESS FULL. For deleted stats, avg_col_len is null. The dynamic sampling query only finds the row count. The simplified dynamic sampling query is:

SQL> SELECT NVL(SUM(C1),0) FROM (SELECT /*+ FULL("E") */ 1 AS C1 FROM "SCOTT"."EMP" "E");
NVL(SUM(C1),0)
--------------
 14

Dynamic sampling tells us nothing about the number of bytes per row. The row length would have to come from the catalog.

SQL> select column_name, data_length, data_type from user_tab_columns where table_name = 'EMP' and column_name in ('ENAME','DEPTNO');

COLUMN_NAME DATA_LENGTH DATA_TYPE
------------------------------ ----------- ------------------------------
ENAME 10 VARCHAR2
DEPTNO 22 NUMBER

Allowing number digits per byte, each row would return 21 bytes. (That’s how I figure it). The optimizer estimated 20 bytes per row, or 280 bytes.

In conclusion, whether dynamic stats or gathered stats were used, the resulting plan was the same. The estimates of cardinality, CPU and time were the same, but the estimates of bytes returned differed slightly. Trace 10053 helps to explain the similarities and differences.

This was a simple example to demonstrate the use of the trace 10053 scripts to find out the reason for the optimizer’s predicted costs.

Conclusion

This blog post made the following points about the trace 10053 technique:

  • Trace 10053 can be used to find out why the optimizer made its decisions.
  • A user must have been granted alter session before running trace 10053.
  • To get trace 10053 output, is necessary to force a hard parse.
  • To get a trace file split into pieces, change the trace file identifier.
  • To get a random identifier in sqlplus, use dbms_random and column new_value.
  • One way to force a hard parse is to vary the SQL with a random identifier.
  • Force a trace file switch by using a random tracefile identifier.

The scripts in this blog post can let you efficiently, systematically, and quickly collect trace 10053 files on a SQL statement. The collected files can be reviewed, analyzed, and compared.

 

 

 

 

 

 

 

 

 

Scripts for trace 10046 and tkprof

Posted on by
Reply

By Brian Fitzgerald

Trace 10046 is used to collect session diagnostic information, such as cursors, waits, and binds. Trace 10046 can also be used to investigate the internal workings of Oracle software. Trace 10046 can be used to localize errors. The tkprof utility formats 10046 trace files into a readable format.

The syntax for invoking trace 10046 and tkprof can be arcane, verbose, and error prone. Putting the commands in several files in a separate directory can lead to cleaner scripts and less cluttered directories. Using scripts can eliminate the manual steps of identifying the trace file and processing with tkprof. These scripts are for tracing “own session”.

Preliminary: To run trace 10046, the user must be granted alter session.

SQL> grant alter session to scott;
Grant succeeded.

Scripts for trace 10046 and tkprof

The scripts are general purpose and can be used for more than one investigation. You should set environment SQLPATH to a common directory and put the scripts there. For example:

[oracle@stormking cdb12201 sqlplus]$ echo $SQLPATH
/home/oracle/brian/sql/sqlplus

Here are  the scripts:

conn.pdba.scott.sql

A script for connecting to the schema.

conn scott/tiger@stormking:1521/pdba

trace.10046.on.sql

Start tracing

alter session set max_dump_file_size = '100M';
alter session set events '10046 trace name context forever, level 12';

trace.10046.off.sql

Stop tracing. Close the trace file.

alter session set events '10046 trace name context off';

tracefilename.sql

Display the trace file name. Set sqlplus substitution variable tracefilename

set verify off
set trimspool on
column tracefilename new_value tracefilename format a100
select value tracefilename
from v$diag_info
where name = 'Default Trace File';

tkprof.sql

Identify the trace file and process it with tkprof. Store the output in the current working directory.

@ tracefilename.sql
column base new_value base
select regexp_replace( '&&tracefilename', '.*/(.*).trc','\1' ) base
from dual;
define tkfile=&&base..tkp
host tkprof &&tracefilename &&tkfile
prompt created file:
prompt &&tkfile

Demonstration

Here’s a simple example. User scott tries to run “show parameter”.

SQL> show parameter cursor
ORA-00942: table or view does not exist

What table or view does not exist? The message does not say. Let’s trace and find out. Here is file show.parameter.demo.sql for tracing “show parameter”:

@ conn.pdba.scott.sql
@ trace.10046.on.sql
show parameter optimizer
@ trace.10046.off.sql
@ tkprof.sql

See how clean and simple it looks. Now run it:

[oracle@stormking cdb12201 parameter]$ sqlplus /nolog @ show.parameter.demo.sql
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 27 21:48:50 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected.
Session altered.
Session altered.
ORA-00942: table or view does not exist
Session altered.
TRACEFILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb12201/cdb12201/trace/cdb12201_ora_29212.trc
BASE
------------------
cdb12201_ora_29212
TKPROF: Release 12.2.0.1.0 - Development on Thu Jul 27 21:48:50 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
created file:
cdb12201_ora_29212.tkp

In cdb12201_ora_29212.tkp

The following statement encountered a error during parse:
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,
DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,
DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM 
FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) 
ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
Error encountered: ORA-00942

tkprof shows that “show parameter” failed because the user has no access to view v$parameter.

The trace file itself also shows the error:

PARSING IN CURSOR #140627652972696 len=289 dep=0 uid=112 oct=3 lid=112 tim=2237530714211 hv=2462394820 ad='b4be2118' sqlid='7cfz5wy9caaf4'
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
END OF STMT
PARSE #140627652972696:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=2237530714211

User scott can confirm no access:

SQL> select * from V$PARAMETER where 1 = 2;
select * from V$PARAMETER where 1 = 2
 *
ERROR at line 1:
ORA-00942: table or view does not exist

To solve the user’s problem, select_catalog_role or some other role can be granted to user scott.

SQL> select grantee from dba_tab_privs 
where table_name = 'V_$PARAMETER' 
and grantee in ( select role from dba_roles ) order by grantee;
GRANTEE
----------------------------------------------------------------------------
DV_SECANALYST
SELECT_CATALOG_ROLE
SQL> select grantee from dba_role_privs 
where granted_role = 'SELECT_CATALOG_ROLE' order by grantee;
GRANTEE
----------------------------------------------------------------------------
DBA
EM_EXPRESS_BASIC
EXP_FULL_DATABASE
IMP_FULL_DATABASE
OEM_MONITOR
SYS
SYSBACKUP
SYSUMF_ROLE
8 rows selected.
SQL> select grantee from dba_sys_privs 
where privilege = 'SELECT ANY DICTIONARY' order by grantee;
GRANTEE
----------------------------------------------------------------------------
DBA
DBSNMP
GGSYS
GSMADMIN_INTERNAL
OEM_MONITOR
SYSBACKUP
SYSDG
WMSYS
8 rows selected.

Alternatively, we can create a new role:

SQL> conn / as sysdba
Connected.
SQL> alter session set container = pdba;
Session altered.
SQL> create role sqlplus_role;
Role created.
SQL> grant select on v_$parameter to sqlplus_role;
Grant succeeded.
SQL> grant sqlplus_role to scott;
Grant succeeded.
SQL> @ conn.pdba.scott.sql
Connected.
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
cursor_bind_capture_destination string memory+disk
cursor_invalidation string IMMEDIATE
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50

The user’s problem is solved. sqlplus command “show parameter” no longer throws ORA-00942.

Summary

  • event 10046 can be used for performance diagnosis
  • trace 10046 can also be used to investigate the inner workings of software
  • trace 10046 can be used to identify the source of an error
  • reusable sqlplus scripts belong in SQLPATH
  • trace syntax can be saved in convenient scripts
  • the result is less cluttered administrative scripts
  • the task of identifying a the trace file and processing with tkprof can be handled in a single script
  • sqlplus command “show parameter” requires access to v$parameter
  • users can be granted limited privileges with custom roles

Flashback database with deleted archive log, or no archive log.

Posted on by
Reply

By: Brian Fitzgerald

Scenarios:

  1. You want to run flashback database, but, for some reason, a needed archivelog has been deleted, but you have a backup.
  2. You want to be able to flashback a database without needing any archivelog.

Solutions:

  1. Identify and restore the needed archive log (usually just one).
  2. Create the restore point while the database is mounted and consistent.

Demo #1. Flashback if a needed archivelog has been deleted:

[oracle@stormking TESTFB blog]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Oct 24 20:09:43 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTFB (DBID=2908670758)

RMAN> backup archivelog all not backed up 1 times delete input;

Starting backup at 20161024 20:10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=6 STAMP=926107742
input archived log thread=1 sequence=22 RECID=7 STAMP=926107771
input archived log thread=1 sequence=23 RECID=8 STAMP=926107773
input archived log thread=1 sequence=24 RECID=9 STAMP=926107777
input archived log thread=1 sequence=25 RECID=10 STAMP=926107803
channel ORA_DISK_1: starting piece 1 at 20161024 20:10
channel ORA_DISK_1: finished piece 1 at 20161024 20:10
piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807 tag=TAG20161024T201006 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741 thread=1 sequence=21
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771 thread=1 sequence=22
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_23.469.926107773 RECID=8 STAMP=926107773
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_24.536.926107777 RECID=9 STAMP=926107777
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_25.527.926107803 RECID=10 STAMP=926107803
Finished backup at 20161024 20:10

RMAN> delete noprompt force archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
List of Archived Log Copies for database with db_unique_name TESTFB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - --------------
6 1 21 A 20161024 20:07
 Name: +RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741

7 1 22 A 20161024 20:09
 Name: +RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771

deleted archived log
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741 RECID=6 STAMP=926107742
deleted archived log
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771 RECID=7 STAMP=926107771
Deleted 2 objects

[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:32:02 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
FBDEMO_RP_20161024_2009

FBDEMO_RP_20161024_2009
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point FBDEMO_RP_20161024_2009
flashback database to restore point FBDEMO_RP_20161024_2009
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1709223 to SCN 1709224
ORA-38761: redo log sequence 22 in thread 1, incarnation 2 could not be
accessed

OK, we have a problem. The solution is to find out the needed SCN from v$restore_point, and then restore that archivelog.

[oracle@stormking TESTFB blog]$ sysdba @ vrestorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:54:02 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


Session altered.


 SCN DATABASE_INCARNATION# GUA TIME NAME
---------------- --------------------- --- ---------------- ----------------------------------------
 1709224 2 YES 2016-10-24 20:09 FBDEMO_RP_20161024_2009

RMAN> restore archivelog from scn 1709224 until scn 1709224;

Starting restore at 20161024 20:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_DISK_1: reading from backup piece +RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807
channel ORA_DISK_1: piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807 tag=TAG20161024T201006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 20161024 20:39

RMAN>

Recovery Manager complete.
[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:39:16 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
FBDEMO_RP_20161024_2009

FBDEMO_RP_20161024_2009
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point FBDEMO_RP_20161024_2009

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Demo #2. Be able to flashback without any archivelogs. First, shutdown immediate, then startup mount. Create the restore point. No archivelogs will be required to flash back.

[oracle@stormking TESTFB blog]$ sysdba @ cr.consistent.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 21:02:59 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

flashback database is enabled

Session altered.


no rows selected


no rows selected

no restore point exists

RP INST
-------------------------------------------------------------------------------- --------
consistent_rp_20161024_2102 TESTFB

consistent_rp_20161024_2102
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.

no rows selected

the database is consistent
old 1: create restore point &&rp guarantee flashback database
new 1: create restore point consistent_rp_20161024_2102 guarantee flashback database

Restore point created.


Session altered.


 SCN DATABASE_INCARNATION# GUA TIME NAME
---------------- --------------------- --- ---------------- ----------------------------------------
 1712257 3 YES 2016-10-24 21:03 CONSISTENT_RP_20161024_2102

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@stormking TESTFB blog]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Oct 24 21:03:40 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTFB (DBID=2908670758, not open)

RMAN> backup archivelog all not backed up 1 times delete input;

Starting backup at 20161024 21:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
skipping archived log of thread 1 with sequence 22; already backed up
skipping archived logs of thread 1 from sequence 24 to 25; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=26 RECID=13 STAMP=926109581
channel ORA_DISK_1: starting piece 1 at 20161024 21:03
channel ORA_DISK_1: finished piece 1 at 20161024 21:03
piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t210351_0.469.926111033 tag=TAG20161024T210351 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_26.527.926109581 RECID=13 STAMP=926109581
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926109543 RECID=11 STAMP=926109542
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_24.536.926109583 RECID=14 STAMP=926109582
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_25.456.926109579 RECID=12 STAMP=926109579
Finished backup at 20161024 21:03

RMAN> delete noprompt archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
specification does not match any archived log in the repository

RMAN>

Recovery Manager complete.
[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 21:04:21 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
CONSISTENT_RP_20161024_2102

CONSISTENT_RP_20161024_2102
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point CONSISTENT_RP_20161024_2102

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Scripts that were used in this blog post, in alphabetical order:

assert.database.flashback.is.on.sql

whenever sqlerror exit 1
select 0 / 0 "chk that flashback is on"
from v$database
where FLASHBACK_ON != 'YES';

prompt flashback database is enabled

assert.database.is.consistent.sql:

whenever sqlerror exit 1

select 0 / 0 "chk datafiles are consistent"
from v$datafile_header
where status = 'ONLINE'
and fuzzy = 'YES';

prompt the database is consistent

assert.guaranteed.restorepoint.exists.sql:

whenever sqlerror exit 1

select 0 / 0 "chk for guar restore point"
from dual
where not exists
(
 select *
 from v$restore_point
 where guarantee_flashback_database = 'YES'
);

prompt a guaranteed restore point exists

assert.no.restore.point.exists.sql:

whenever sqlerror exit 1

select 0 / 0 "chk no restore point exists"
from v$restore_point
where rownum = 1;
prompt no restore point exists

cr.consistent.restorepoint.sql:

whenever oserror exit 1
whenever sqlerror exit 1
column inst format a8
set linesize 200
set trimspool on

@ assert.database.flashback.is.on.sql
@ vrestorepoint.sql
@ assert.no.restore.point.exists.sql

column rp new_value rp format a80
select 'consistent_rp_' || to_char ( sysdate , 'yyyymmdd_hh24mi' ) rp,
 sys_context( 'userenv', 'instance_name') inst
from dual;

prompt &&rp

shutdown immediate
startup mount

@ assert.database.is.consistent.sql

create restore point &&rp guarantee flashback database;

@ vrestorepoint.sql
quit

cr.fbdemo.restorepoint.sql:

whenever oserror exit 1
whenever sqlerror exit 1
column inst format a8
set linesize 200
set trimspool on

@ assert.database.flashback.is.on.sql
@ vrestorepoint.sql
@ assert.no.restore.point.exists.sql

column rp new_value rp format a80
select 'fbdemo_rp_' || to_char ( sysdate , 'yyyymmdd_hh24mi' ) rp,
 sys_context( 'userenv', 'instance_name') inst
from dual;

prompt &&rp

alter system checkpoint;
alter system switch logfile;

create restore point &&rp guarantee flashback database;

@ vrestorepoint.sql
quit

vrestorepoint.sql:

set linesize 200
set trimspool on
set pagesize 900
column scn format 999999999999999
column time format a16
column name format a40

alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi';

select rp.scn,
 rp.database_incarnation#,
 rp.guarantee_flashback_database,
 rp.time,
 rp.name
from v$restore_point rp;