DBA Blogs

Buffer Limit

Tom Kyte - Thu, 2019-04-04 13:46
Hi, The Buffer size we give for dbms_out.put_line is different from Data base buffer cache in SGA or same? and can you show me am example on how to use dbms_out.getline function. Thanks in Advance.
Categories: DBA Blogs

Can we have multiple versions of Oracle database instance in 11gR2 RAC

Tom Kyte - Thu, 2019-04-04 13:46
We would like to install 2 11.2 database and one 10.2 database in 11GR2 RAC. Is this possible? ? Can we run all 3 instances on a Single RAC 11.2 Cluster? Will this complicate backup/recovery? Thanks
Categories: DBA Blogs

Everyone should read this

Hemant K Chitale - Thu, 2019-04-04 02:10
An excellent article that anyone promising, developing, maintaining or using any system that is non-trivial should read :

https://embeddedartistry.com/blog/2019/4/1/what-can-software-organizations-learn-from-the-boeing-737-max-saga




Categories: DBA Blogs

Huge Pages Speeds Startup of Web Servers

Bobby Durrett's DBA Blog - Tue, 2019-04-02 10:50

We had issues deploying changes to a large web farm a few weeks back. We had automated the startup of the 25 web servers using a DevOps tool but when it tried to bring them all up at once it took forever and the web servers got timeout errors. We resolved this by putting in huge pages on the metadata database.

I mentioned the unexpectedly large impact of huge pages on login time in a previous post. But, we had not realized that a small metadata database with a 5 gigabyte SGA would need huge pages. I guess I should mention that this is 11.2.0.3 Oracle on Linux. The web farm has 25 servers with 25 database connections each. I think the number of connections from each server should be 5 or less but I have not convinced others to reduce the number. If you have a metadata database with 4 cores and 625 connections from 25 web servers, the web servers cannot use all of those connections. A 4-core database server can only process 10 or 20 SQL statements at once if they are I/O centric. If they are all CPU you are looking at 4 concurrent active SQL statements. If you kick off 625 SQL statements at once the load will go through the roof and everything will timeout. I thought about putting in shared servers on the metadata database to force the 625 sessions to funnel through 12 shared servers so the database server would not be overwhelmed should all 625 connections choose to run at once. But the only problem we had was with logins. All 625 were logging in at the same time when the web farm was started by our DevOps tool. Huge pages resolved this issue by making the login time 10-20 times faster.

The database was in a 16 gigabyte Linux VM with 4 cores and a 5 gigabyte SGA. Without huge pages each login took about 2.5 seconds. With it the logins took about .15 seconds. Without huge pages the load on the database server went over 100 when all 625 sessions started at once. With huge pages the load never exceeded 1. Without huge pages the web farm never came up cleanly and with it the farm came up quickly without error. So, if you have a Linux VM with several gigabytes in your SGA you might want to use huge pages.

Bobby

Categories: DBA Blogs

How to Promote Yourself on LinkedIn

VitalSoftTech - Tue, 2019-04-02 10:50
Twenty new profiles are made every second on LinkedIn! To stand out from the crowd, technologists must have a impact-full profile. People create an impression about someone within the first 3 to 4 seconds of landing on their profile. If your profile connects with clients, they will want to contact you and do business with […]
Categories: DBA Blogs

Northern California Oracle Users Group organizes the mother of all competitions

Iggy Fernandez - Tue, 2019-04-02 08:59
In a coolly calculated effort to promote its spring conference featuring a full-day Oracle Database security master class with Oracle ACE director alumnus Dan Morgan on Thursday, May 9 at the PayPal Town Hall in San Jose, the Northern California Oracle Users Group (NoCOUG) is conducting the mother of all competitions with the grand prize being a lifetime NoCOUG individual membership worth a cool $9500 USD at the going rate of $95 USD per year. Unlike the insanely difficult SQL competitions of the past which required contestants to be brainiacs, the latest NoCOUG competition is as simple as doing an expense report. The winner will be the contestant who has notattended the most NoCOUG conferences even after receiving numerous reminder e-mails. What could be simpler?
Categories: DBA Blogs

interval-list partitions

Tom Kyte - Tue, 2019-04-02 06:26
Hi Tom, I would like to ask you about interval-list partitions. On oracle-base.com is mentioned that one of the new features of 11g is interval partitioning (available over number and date columns) and it should be possible to create interval-lis...
Categories: DBA Blogs

Oracle Trace

Tom Kyte - Tue, 2019-04-02 06:26
Hello, I need to analyze my database. I used trace + tkprof and I see this result : SQL ID: 4yvsj6a508pgf Plan Hash: 2709293936 SELECT NAME FROM SYS.USER$ WHERE USER# = :B1 call count cpu elapsed disk query ...
Categories: DBA Blogs

When it's time to close cursors

Tom Kyte - Tue, 2019-04-02 06:26
Hello, Tom. Hope you're doing well :) My question is - is it bad practice to let the 'main' procedure of the package to close all open cursors? Here is the code I want to use: create or replace PACKAGE body pck_test AS -- Global package curso...
Categories: DBA Blogs

Partner Webcast – Single Sign-on to Applications in Oracle Identity Cloud Service

Cloud adoption promises the benefit of increased flexibility, agility, and significant cost savings, so migrating more and more applications including business-critical applications to the cloud is...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Single Sign-on to Applications in Oracle Identity Cloud Service

Cloud adoption promises the benefit of increased flexibility, agility, and significant cost savings, so migrating more and more applications including business-critical applications to the cloud is...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Indexing The Oracle Autonomous Data Warehouse (Autobahn)

Richard Foote - Tue, 2019-04-02 02:39
When the Autonomous Data Warehouse was initially released in March 2018, one of the notable restrictions was that it did not support typical Data Warehouse performance related structures such as Indexes, Partitioning or Materialized Views. The platform was deemed efficient enough to not require these features, which would unnecessarily complicate the autonomous environment. In September […]
Categories: DBA Blogs

Oracle Analytics Cloud - Current Week SQL Filter

Tom Kyte - Mon, 2019-04-01 12:06
I'm trying to automate some reports in the Analytics Cloud by using SQL syntax within SQL filter. I was able to make a report refresh on a daily basis; but I'm stuck on getting it to work for the week. Is there a syntax that will recognize Monday to ...
Categories: DBA Blogs

Subtotal, Grand Total, ordering and breaking on different fields

Tom Kyte - Mon, 2019-04-01 12:06
Hi Tom I was looking through the already asked questions about Subtotals and Grand totals , but couldn't really find anything that suits my problem. I have a table with the following fields <code>DATE REGION REG_NUM AMOUNT 0...
Categories: DBA Blogs

Why oracle stored procedure execution time is greatly increased depending on how it is executed?

Tom Kyte - Mon, 2019-04-01 12:06
I have three different procedures written in PL/SQL (Oracle database). They do various calculation and analysis, end product of all three being ?INSERT INTO TABLE_A? (so all functions are inserting into same table). It?s a pretty straightforward PL/...
Categories: DBA Blogs

DBMS_SCHEDULER JOB (Need to make repeat_interval parameterized)

Tom Kyte - Mon, 2019-04-01 12:06
How can i dynamically retrieve the value for repeat_interval in the following job:- BEGIN DBMS_SCHEDULER.create_job ( job_name => 'create_Subpartition_Ongoing_LOCDB_M', job_type => 'PLSQL_BLOCK', job_action => ...
Categories: DBA Blogs

Step by Step Troubleshooting and Validation of AWS CloudFormation Yaml template

Pakistan's First Oracle Blog - Mon, 2019-04-01 02:00
CloudFormation (CFN) is infrastructure as a code service of AWS. You just tell CFN your desired state of resources and it creates them in order while resolving dependencies. You mention these resources in a file called as template which can be written in YAML or JSON. YAML being bit more user readable, is widely used now. YAML is great but you have to be aware of its indentation.


Following is a step by step troubleshooting and validation of a simple CFN template by using aws cli from windows 10 command prompt.

Step 1: Create your template file. I am using ec2.yaml which just creates an EC2 instance and a security group with some parameters and outputs.


#####################################################################################################
#  Template : Use to deploy simple T2.MICRO EC2 Instance with a security group
#
#  Author   : Fahd Mirza
#
#  Created on :  23rd March, 2019
#
#####################################################################################################
---
AWSTemplateFormatVersion: "2010-09-09"
Description: Deploy EC2 Instance with a security group
######################################################################################################
Parameters:
######################################################################################################
  InstanceType:
    Type: String
    Default: t2.micro
    AllowedValues:
      - t2.micro
      - t2.2xlarge
  MyRSAKey:
    Type: String
    Description: Supply your RSA Key Name
Mappings:
  RegionMap:
    us-east-1:
      AMI: ami-1853ac65
    us-west-1:
      AMI: ami-bf5540df
    eu-west-1:
      AMI: ami-3bfab942
    ap-southeast-1:
      AMI: ami-e2adf99e
    ap-southeast-2:
      AMI: ami-43874721   
######################################################################################################
Resources:
######################################################################################################
  MySecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow SSH to EC2 Bastion Host
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '22'
        ToPort: '22'
        CidrIp: 0.0.0.0/0
      Tags:
      - Key: Name
        Value: MyBastionSG112
  MyEc2Instance:
    Type: AWS::EC2::Instance
    Metadata:
      AWS::CloudFormation::Init:
        config:
          files:
            /etc/cfn/cfn-hup.conf:
              content: !Sub |
                    [main]
                    stack=${AWS::StackId}
                    region=${AWS::Region}
                    interval=1
              mode: '000744'
              owner: root
              group: root
            /etc/cfn/hooks.d/cfn-auto-reloader.conf:
              content: !Sub |
                     [cfn-auto-reloader-hook]
                     triggers=post.update
                     path=Resources.MyEc2Instance.Metadata.AWS::CloudFormation::Init
                     action=/opt/aws/bin/cfn-init -v --stack ${AWS::StackId} --region ${AWS::Region} --resource MyEc2Instance
                     runas=root
            ~/hello1.txt:
               content: !Sub |
                     hello world, I am from files section of CFN Init metadata.         
          commands:
            RunA:
              command: cat "hello world, I am from command section of CFN Init metadata." > ~/hello.txt
              ignoreErrors: "true"
          services:
              sysvinit:
                  cfn-hup:
                    enabled: true
                    ensureRunning: true
                    files: [/etc/cfn/cfn-hup.conf, /etc/cfn/hooks.d/cfn-auto-reloader.conf]
    Properties:
      Tags:
      - Key: Name
        Value: !Ref "AWS::StackName"
      ImageId:
        Fn::FindInMap:
        - RegionMap
        - !Ref AWS::Region
        - AMI       
      InstanceType:
        Ref: InstanceType
      KeyName:
        Ref: MyRSAKey
      UserData:
        Fn::Base64: !Sub |
              #!/bin/bash -xe
              yum install -y aws-cfn-bootstrap
              chkconfig --add cfn-hup
              /opt/aws/bin/cfn-init -v --stack ${AWS::StackId} --resource MyEc2Instance --region ${AWS::Region}
              /opt/aws/bin/cfn-signal -e 0 --stack ${AWS::StackName} --resource MyEc2Instance --region ${AWS::Region}
    CreationPolicy:
      ResourceSignal:
        Timeout: "PT15M"
######################################################################################################
Outputs:
######################################################################################################
  MyEC2InstancePublicIP:
    Description: My EC2 Instance Public IP
    Value: !GetAtt MyEc2Instance.PublicIp
  MyEC2InstanceID:
    Description: My EC2 Instance ID
    Value: !Ref MyEc2Instance
  MyEC2SecGroup:
    Description: My EC2 Security Group
    Value: !Ref MySecurityGroup

Step 2: When I first ran the validation on this template, it gave me error about its indentation like following. I fixed the indentation  by using simple editor. You can also use any online editor or Visual code studio.

C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>dir
 Volume in drive C is Windows
 Volume Serial Number is 5285-4635

 Directory of C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates

23/03/2019  12:54 PM              .
23/03/2019  12:54 PM              ..
23/03/2019  12:49 PM             4,360 ec2.yaml
23/03/2019  12:54 PM             2,461 my.yaml
               2 File(s)          6,821 bytes
               2 Dir(s)  807,032,090,624 bytes free

C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>aws cloudformation validate-template --template-body file://my.yaml

An error occurred (ValidationError) when calling the ValidateTemplate operation: [/Parameters/Mappings] 'null' values are not allowed in templates

C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>
C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>
C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>aws cloudformation validate-template --template-body file://ec2.yaml

An error occurred (ValidationError) when calling the ValidateTemplate operation: Invalid template parameter property 'RegionMap'

C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>aws cloudformation validate-template --template-body file://ec2.yaml

Step 3: Then I had declared a resource in resources section but it wasn't present in the Parameters section. So I removed it from parameters.

An error occurred (ValidationError) when calling the ValidateTemplate operation: Template format error: Unresolved resource dependencies [MyVPCId] in the Resources block of the template

Step 4: Now it works like a charm.

C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>
C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>
C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>
C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>aws cloudformation validate-template --template-body file://ec2.yaml
Deploy EC2 Instance with a security group
PARAMETERS              Supply your RSA Key Name        False   MyRSAKey
PARAMETERS      t2.micro                False   InstanceType

C:\Users\mirza\Desktop\Cloud\AWS\Cloud\CloudFormation\templates>

Categories: DBA Blogs

Granting select on all tables and views of one user to other

Tom Kyte - Sat, 2019-03-30 05:06
Tom, I have a user with x tables any y views. I wish to grant select on all the tables and views owned by this user to another user. Is there any thing like "select any table" for this purpose. I am thinking to implement by granting the select...
Categories: DBA Blogs

best way to get high value of partitions from data dictionary

Tom Kyte - Sat, 2019-03-30 05:06
Hi, Chris and Connor, I'm trying to find the best way to get "high value" of partitions from data dictionary, in order to write a customized statistics gathering program. As you know, the data type of "high_value" column of DBA_TAB_PARITTIONS i...
Categories: DBA Blogs

MAX aggregation function shows different results when partitioned

Tom Kyte - Thu, 2019-03-28 16:26
Hi everyone, I'm getting strange results from a query. I have a table list partitioned by id, when I execute this : <code>select max(col1) from table where id=100;</code> it returns the value 'A1'. Then I execute the following (keep in mind ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs