Feed aggregator

Question on Buffer Cache Reads Avg Time in AWR

Tom Kyte - Sat, 2017-04-22 15:46
Hi Tom, In AWRs, under the section <b><i>"IOStat by Function summary"</i></b>, we find a statistic called <i><b>"Buffer Cache Reads Avg Time"</b> </i> According to my understanding it is the avg time taken to do a <b>"Buffer Get"</b>, Am I right...
Categories: DBA Blogs

CPU Utilisation

Tom Kyte - Sat, 2017-04-22 15:46
Hi team, Every Saturday DB Server CPU Become very high after to restart the db also it become high i checked awr where i found "cpu quantum" event then i check SQL> select client_name, status from dba_autotask_client; CLIENT_NAME ...
Categories: DBA Blogs

MV log missing records

Tom Kyte - Sat, 2017-04-22 15:46
Hi, During maintenance of fast refresh materialized views, 10000 MLOG$ records are lost. Can you suggest what are our options for restoring those missing records to our MV? Making dummy update of those records on master table is not allowed due...
Categories: DBA Blogs

Size of sga_target and sga_max_size exceeding the size of physical RAM. Is it possible?

Tom Kyte - Sat, 2017-04-22 15:46
Greetings Sir, For an example, i have total ram of 2GB. I have disabled the AMM ie memory_target=0. This allows me to resize the sga parameters. I just tried to set the sga_max_size=5G and sga_target=4G. I restarted the instance. It goes normally...
Categories: DBA Blogs

Redolog file

Tom Kyte - Sat, 2017-04-22 15:46
Why is writing to Redolog file is faster then actually writing changes to datafiles ?
Categories: DBA Blogs

remote startup

Tom Kyte - Sat, 2017-04-22 15:46
Hi tom, For the testing purpose, I have a remote DR database, how to startup the DR database from DC using the static listener registration method?? Please, provide me the sample listener.ora and tnsnames.ora file with connection to remote datab...
Categories: DBA Blogs

Use case statements to compare two columns

Tom Kyte - Sat, 2017-04-22 15:46
Hi Tom, Can you please suggest the best way to use case statements while comparing fields between two tables. create table t1(id number, first_name varchar2(20), last_name varchar2(20), birth_date date); insert into t1 (id, first_name, las...
Categories: DBA Blogs

Deleting old records from a Very big table based on criteria

Tom Kyte - Sat, 2017-04-22 15:46
I am having one table (Table A) that contain <b>300 Million Records</b>, want to do a data retention activity on basis of some criteria. Concerning the performance, I planned to create a new table(Table B) with the oldest 10M records from Table-A....
Categories: DBA Blogs

Exporting sequences along with table using DBMS_DATAPUMP package.

Tom Kyte - Sat, 2017-04-22 15:46
I am exporting some table of my schema 'KNK' using following code. <i> <b>declare h1 number; v_schema VARCHAR2(32); v_time VARCHAR2(32); v_job_state varchar2(4000); begin SELECT to_char(SYSDATE, 'DD_MM_YYYY_hh24_mi_ss') INTO v_tim...
Categories: DBA Blogs

Order rows of a table in the same order they have inserted

Tom Kyte - Sat, 2017-04-22 15:46
Hi Tom, Is there any way to get the rows of a table in the same order they have inserted, without using any sequence number or timestamp (trying to find out if oracle has some inbuilt functionalities to perform the action)?
Categories: DBA Blogs

Benefits of Dashboards and Analytics for Hospitality Industry

Nilesh Jethwa - Sat, 2017-04-22 09:05

Briefly, dashboard software offer a method of collecting and organizing data. Using simple access points and display functions, this software can provide their users with snapshots of measurable metrics that they can then use to make better decisions, in this case, on running their establishments.

Enabling informed decision-making is one of the primary benefits and use of dashboard for hospitality.

The hospitality industry is rapidly growing. And with competition at every corner, it is important for hotels to properly manage their operations. Also, to have access to reliable metrics that will help them develop business strategies that can be capitalized on.

This is one of the reasons why business dashboards are essential. For one, a dashboard can help hotel managers facilitate the different departments under their supervision.

One of the highly-valued benefits and use of dashboard for hospitality is obtaining valuable analytic data that clearly presents how each sector operates, their efficiency, and of course, how every action translates to costs and savings for the establishment.

Basically, a dashboard provides you with a picture of how well your hotel is running. It offers easy-to-understand charts, graphs, and measures at a glance. Simple and effective, there is no need to exhaust valuable time and energy reading and interpreting data as the system does these for you.

Read more at http://www.infocaptor.com/dashboard/how-can-hospitality-industry-benefit-from-dashboards-and-analytics

Query Writing Differently (Relational Division)

Tom Kyte - Fri, 2017-04-21 21:26
<code>create table developers (name varchar2(30), skill varchar2(30)); create table projects (name varchar2(30), skill varchar2(30)); insert into developers values ('SMITH','ORACLE'); insert into developers values ('SMITH','JAVA'); insert i...
Categories: DBA Blogs

How this query returns value??

Tom Kyte - Fri, 2017-04-21 21:26
When I execute the following query , It returns the values. <code>select * from TABLE_1 where FIELD1 in ( select FIELD1 from Table2 where FIELD2 = 'AAAAAAA' and FIELD3 = 'SSSSSS') and FIELD2 in (select FIELD2 from TABLE3 where FIELD3 in ('XXXXX...
Categories: DBA Blogs

COUNT Aggregate Function and null

Tom Kyte - Fri, 2017-04-21 21:26
Hi, I created a table called Temp in that i inserted 2 NULL Rows so now total rows is 8. INSERT INTO TEMP VALUES(NULL,NULL); When i executed SELECT COUNT(*) FROM TEMP giving Result as 8 it also counted 2 NULL Rows as per my knowledge COUNT igno...
Categories: DBA Blogs

Link the value of entities from different object types in Oracle 12c

Tom Kyte - Fri, 2017-04-21 21:26
Let's assume we have the following TYPEs: <code>CREATE TYPE customer_rec_tp AS OBJECT ( customer_id NUMBER(10) , first_name VARCHAR2(20) , last_name VARCHAR2(20) , store VARCHAR2(50) , err_msg VARCHAR2...
Categories: DBA Blogs

Love Your Data Conference in NYC on 31st May

Pakistan's First Oracle Blog - Fri, 2017-04-21 19:11
In this InfoEra, its all about data.Whether its in the cloud or on-premises everything is truly revolving around and is for data. Pythian understood that decades ago and loving the data of their customers since day one. They are showcasing this love on 31st May in NYC.

http://promo.pythian.com/love-your-data-conference/


To help you turn your organization into a truly data-driven business, this interactive 1-day event in New York City on May 31, 2017, combines presentations, practical interactive panel sessions and open discussions across business and technical tracks.

This event is for CIO’s and IT Business leaders interested in learning how to better empower their company to drive business outcomes with analytics. Pythian’s Love Your Data Conference will focus on practical ways to:
  • Transform your organization using data and self-service analytics
  • Align IT to the business by giving all users access to data
  • Add data intelligence and automation to business decisions
  • Get a 360-degree view of your customer and promote innovation 
If you want to attend only one event this year then this must be the one.
Categories: DBA Blogs

SecureFiles on multi-datafiles tablespaces

Yann Neuhaus - Fri, 2017-04-21 14:47

When we have a tablespace with multiple datafiles, we are used to seeing the datafiles filled evenly, the extents being allocated in a round-robin fashion. In the old time, we used that to maximize performance, distributing the tables to all disks. Today, we use LVM striping, maximum Inter-Policy, ASM even distribution. And we may even use bigfile tablespaces, so that we don’t care about having multiple datafiles.

But recently, during test phase of migration, I came upon something like this:
SecureFile003

To reproduce the case, I’ve created a tablespace with 8 datafiles:

SQL> create tablespace MYTABS datafile
2 '/tmp/MYTABS01.dbf' size 1M autoextend on maxsize 100M,
3 '/tmp/MYTABS02.dbf' size 1M autoextend on maxsize 100M,
4 '/tmp/MYTABS03.dbf' size 1M autoextend on maxsize 100M,
5 '/tmp/MYTABS04.dbf' size 1M autoextend on maxsize 100M,
6 '/tmp/MYTABS05.dbf' size 1M autoextend on maxsize 100M,
7 '/tmp/MYTABS06.dbf' size 1M autoextend on maxsize 100M,
8 '/tmp/MYTABS07.dbf' size 1M autoextend on maxsize 100M,
9 '/tmp/MYTABS08.dbf' size 1M autoextend on maxsize 100M
10 /
 
Tablespace created.

SecureFiles

This was a 11g to 12c migration, with Data Pump, and a good occasion to convert all LOB to SecureFiles with the transform=lob_storage:securefile parameter. And this tablespace is the one where the LOB segments are stored. I reproduced it with:

SQL> create table MYTABLE ( x clob ) tablespace USERS
2 LOB(x) store as securefile MYLOBSEG (tablespace MYTABS disable storage in row);
 
Table created.

Then I inserted about 80MB:

SQL> insert into MYTABLE select lpad('x',100000) from xmltable('1 to 8000') ;
8000 rows created.
 
SQL> select file_id,file_name,blocks,user_blocks from dba_data_files where tablespace_name='MYTABS' order by file_id;
 
FILE_ID FILE_NAME BLOCKS USER_BLOCKS
---------- ------------------------------ ---------- -----------
61 /tmp/MYTABS01.dbf 128 120
62 /tmp/MYTABS02.dbf 128 120
63 /tmp/MYTABS03.dbf 128 120
64 /tmp/MYTABS04.dbf 9344 9336
65 /tmp/MYTABS05.dbf 128 120
66 /tmp/MYTABS06.dbf 128 120
67 /tmp/MYTABS07.dbf 128 120
68 /tmp/MYTABS08.dbf 128 120
 
8 rows selected.
 
SQL> select file_id,segment_type,sum(blocks) from dba_extents where tablespace_name='MYTABS' group by file_id,segment_type order by file_id,segment_type;
 
FILE_ID SEGMENT_TYPE SUM(BLOCKS)
---------- ------------------ -----------
64 LOBSEGMENT 9216
65 LOBINDEX 8
65 LOBSEGMENT 24

And I continued to load rows, and observed the datafiles filled to their maxsize one after the other, without numeric or alphabetical order.

SQL> select file_id,file_name,blocks,user_blocks from dba_data_files where tablespace_name='MYTABS' order by file_id;
 
FILE_ID FILE_NAME BLOCKS USER_BLOCKS
---------- ------------------------------ ---------- -----------
61 /tmp/MYTABS01.dbf 8320 8312
62 /tmp/MYTABS02.dbf 12416 12408
63 /tmp/MYTABS03.dbf 12416 12408
64 /tmp/MYTABS04.dbf 12416 12408
65 /tmp/MYTABS05.dbf 128 120
66 /tmp/MYTABS06.dbf 128 120
67 /tmp/MYTABS07.dbf 128 120
68 /tmp/MYTABS08.dbf 128 120
 
8 rows selected.
 
SQL> select file_id,segment_type,sum(blocks) from dba_extents where tablespace_name='MYTABS' group by file_id,segment_type order by file_id,segment_type;
 
FILE_ID SEGMENT_TYPE SUM(BLOCKS)
---------- ------------------ -----------
61 LOBSEGMENT 8192
62 LOBSEGMENT 12288
63 LOBSEGMENT 12288
64 LOBSEGMENT 12288
65 LOBINDEX 8
65 LOBSEGMENT 24

Here are some screenshots during this load

SecureFile001

SecureFile002

SecureFile003

BasicFiles

This occurs only with SecureFiles. With the same load into LOB stored as BasicFile I got the following distribution:
SecureFile004

Note that the inserts to BasicFile were about two times slower than the same into SecureFiles.

So what?

I don’t see any bad consequences about that, and this may even be an improvement when concurrent sessions are loading the LOBs. When the datafile picked-up looks random, a guess is that it depends on a process ID to try to distribute the concurrent load. And if you want to distribute data over multiple disks, then you should do it at a lower level. However, it is 30 years behavior that changes and it’s better to be aware of it: datafiles may reach their maxsize even when the tablespace is not full. I order to be sure that it is the expected behavior, I opened an SR with easy to reproduce testcase. Status is ‘review update’ for 9 days (SR 3-14677784041) and I’ll update this post is I get an answer.

 

Cet article SecureFiles on multi-datafiles tablespaces est apparu en premier sur Blog dbi services.

Twitter Chat Reveals – Is headless CMS signaling the end of WCM?

WebCenter Team - Fri, 2017-04-21 10:22

On April 20, @oraclewebcenter hosted a live Twitter Chat at #contentdgtl. The topic – Is headless CMS signaling the end of WCM?

Decoupled CMS architecture (aka “headless”) is rising in popularity in the development world. This model allows breakthrough user experiences, gives developers great flexibility to innovate, and helps site owners future-proof their builds by allowing them to refresh the design without re-implementing the whole CMS. With all this upside, it’s no wonder this type of build has gained serious traction.

With that in mind, the live Twitter discussion focused on the topic of headless CMS and whether companies need to be thinking about it. The Twitter Chat explored headless CMS vs. WCM and use cases of when it makes sense to use one or the other. Industry thought leaders including Melissa Webster from IDC, Dave Gray from Xplaner.com, numerous partners and more participated in this very engaging discussion. The interaction ranged from the benefits of headless CMS to both the developer and business, to things to watch for with headless CMS and ended with best practice recommendations for content production, management & delivery in a cloud world.

From serious musings to light hearted commentary (including this pic that Tanu Sood shared), the Twitter Chat proved to be a great meeting of minds.

Even if you participated, you may have missed portions of the live discussion so we have curated the chat here; it might be worth going back and following the discussion.

One of my personal favorites was a tweet from Carlos Picazo (@carlines) that said "in a few years, we will have even more connected than today and content is key, Internet of People!"

Catch the recap of the Twitter Chat and while you still can, feel free to search for the complete thread by searching on “#contentdgtl” on Twitter.

Archive: Is headless CMS signaling the end of WCM?

Picture Courtesy: https://t.co/jb7JDERk9i

How to create dashboard for Hadoop Hive Database

Nilesh Jethwa - Fri, 2017-04-21 09:25

HiveServer2 is a service that enables clients to execute queries against Hive. It supports multi-client concurrency and authentication.

Are you using Hadoop Hive for your data marts or data-warehouse? If so, build your Free Hadoop Hive web dashboard software.

A Hadoop Hive dashboard visually summarizes all the important metrics you have selected to track, to give you a quick-and- easy overview of where everything stands. With real-time Hiveserver2 SQL reporting reporting, it's a live view of exactly how your marketing campaign is performing.

  • Better Decision Making
  • Gain Competitive Advantage
  • Enhance Collaboration
  • Spotting potential problems
  • Merge with data from Excel Dashboards
  • Live SQL against database
  • No need for Data-warehouse or ETL
  • Leverage the speed and stability of your powerful database.

Read more at http://www.infocaptor.com/ice-database-connect-dashboard-to-hiveserver2-sql

SQL Server 2017 AlwaysOn AGs and new read-scale architectures

Yann Neuhaus - Fri, 2017-04-21 06:40

As you probably know Microsoft announced an official name for SQL Server vNext during the last Microsoft Data Amp event on April 19 2017. It becomes officially SQL Serve 2017.

In my first blog post, I wrote about SQL Server on Linux and the introduction of availability groups features. At this moment the SQL Server release version was CTP 1.3. As a reminder, with previous CTP releases, listeners were unusable because they did not persist when switch over events occurred as well as they didn’t provide any transparent redirection capabilities. Today, we are currently on the CTP 2.0 and this last CTP release comes with an important improvement concerning AGs with the support of listeners.

In this blog post I don’t want to write about creating an AG listener on Linux environment. The process is basically the same that creating a listener on Windows and it is well documented by Microsoft for a while. But several things shipped with the last CTP 2.0 have drawn my attention and will allow extending some scenarios with AGs.

First of all, from the Microsoft documentation we may notice a “Create for read-scale only” section. In a nutshell, we are now able to create a cluster-less availability group. Indeed, in this context we want to prioritize scale-out scenarios in favor of HA meaning the cluster layer is not mandatory here. That’s the point. Using Linux or Windows operating system in this case? Well, we may have a long debate here but let’s say we will use a Linux operating system for this scenario.

You also probably noticed that the CLUSTER_TYPE parameter includes now a new EXTERNAL value. So we may create an availability group? by using one of the following values:

  • WSFC = A Windows Server Failover Cluster will manage the availability group
  • EXTERNAL = An external entity will manage the availability group (pacemaker on Linux so far)
  • NONE = No cluster entity will manage the availability group

In my opinion, introducing the EXTERNAL value does make sense regarding the previous CTP releases. Indeed we were able only to specify NONE value to either use an external entity to manage AGs or to use nothing for read-scale scenarios making it meaningless.

At the same time FAILOVER_MODE parameter includes also a new EXTERNAL value which must be specified when using an external entity to manage AGs failover. Before going further in this blog post let’s set the scene. A pretty basic environment which includes 3 high available replicas on Linux involved in a read-scale scenario meaning no extra layer of HA management and asynchronous mode as well.

 blog 121 - ag linux read-scale scenario

As a reminder, implementing a listener with corresponding read-only routes is very useful for the following reasons:

  • Applications are transparently redirected to the corresponding read-only replica when read intent parameter is specified
  • Since SQL Server 2016 applications may be redirected in a round-robin fashion, there’s no need to implement extra component (ok .. round-robin algorithm is pretty basic but that’s not so bad actually)
  • Application does not need to know the underlying infrastructure. They have to connect to the AG listener and that’s it.

But in such scenario where no cluster layer is installed, we are not able to benefit from a floating virtual IP which is part of the automatic redirection to the primary replica in case of a failover event and as you already know, connections must be redirected to the primary in order to benefit from transparent redirection / round robin capabilities. So the remaining question is how to achieve redirection without a floating IP address in this case?

Firstly let’s say creating an AG listener on Linux doesn’t imply creating a corresponding virtual IP and Network Name on the cluster side and especially in this case where AG doesn’t rely on the cluster layer. However creating an AG listener that relies on the primary replica IP address to benefit from transparent / round-robin redirection remains a viable option. This is only the first part of the solution because we have also to address scenarios that include switchover events. Indeed, in this case, primary replica may change regarding the context and the current listener’s configuration becomes invalid (we refer to the previous primary’s IP address). At this stage, I would like to thank again @MihaelaBlendea from Microsoft who put me on the right track.

This is not an official / supported solution but it seems to work well according to my tests. Update 21.04.2017 : Mihaela has confirmed this is a supported solution from Microsoft.

The solution consists in including all the replica IP addresses included in the topology in the listener definition and we may use a DNS record to point to the correct primary replica after a manual failover event. Therefore, applications do have only to know the DNS record to connect to the underlying SQL Server infrastructure.

Here the definition of my availability group including the listener:

CREATE AVAILABILITY GROUP [AdvGrpDRLinux]
WITH
(
    DB_FAILOVER = ON, --> Trigger the failover of the entire AG if one DB fails 
    CLUSTER_TYPE = NONE 
)
FOR REPLICA ON
N'LINUX07'
WITH
(
    ENDPOINT_URL = N'tcp://192.168.40.23:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC, --> use direct seeding
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'LINUX08'
WITH
( 
    ENDPOINT_URL = N'tcp://192.168.40.24:5022', 
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL, 
    SEEDING_MODE = AUTOMATIC, --> use direct seeding
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'LINUX09'
WITH
( 
    ENDPOINT_URL = N'tcp://192.168.40.25:5022', 
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL, 
    SEEDING_MODE = AUTOMATIC, --> use direct seeding
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
GO

ALTER AVAILABILITY GROUP [AdvGrpDRLinux] GRANT CREATE ANY DATABASE;
GO


ALTER AVAILABILITY GROUP [AdvGrpDRLinux]   
ADD LISTENER 'lst-advgrplinux' 
( 
	WITH IP ( ('192.168.40.23', '255.255.255.0'), --> LINUX07 IP Address
			  ('192.168.40.24', '255.255.255.0'), --> LINUX08 IP Address
			  ('192.168.40.25', '255.255.255.0')  --> LINUX09 IP Address
	        ) 
		, PORT = 1433 
);   
GO

Notable parameters are:

  • CLUSTER_TYPE = NONE
  • AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
  • FAILOVER_MODE = MANUAL
  • Direct seeding is enabled.

Let’s have a look at the AG configuration by using AG DMVs:

blog 121 - ag linuxag config

Then here the listener configuration:

blog 121 - ag linuxag listener config

And finally the configuration of my read-only routes and the priority list for redirection:

blog 121 - ag linuxag ro config

You may notice that I use round-robin capabilities for each replica.

I also created a DNS A record with the address of the current primary replica (lst-advgrplinux – 192.168.40.23). DNS record will be used by applications to connect the AdvGrpDRLinux AG.

Let’s test the new configuration by using SQLCMD tool with –K READONLY option. Redirection and round-robin feature come into play. First test is conclusive.

blog 121 - ag linux first test sqlcmd

Go ahead and let’s perform a manual failover. In this case, the primary replica is still available, so I just switched momentary on synchronous mode to avoid resuming replication databases from secondary replicas afterwards. Then I performed a switch over to the LINUX08 replica. According to the Microsoft documentation, in order to guarantee no data loss I also changed temporary the REQUIERED_COPIES_TO_COMMIT to 1. Finally, after performing the manual failover successfully, I switched back to asynchronous mode (REQUIERED_COPIES_TO_COMMIT must be reverted to 0 in this case).

USE [master]
GO

-- switch momentary to synchronous mode
ALTER AVAILABILITY GROUP [AdvGrpDRLinux]
MODIFY REPLICA ON N'LINUX07' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT)
GO
ALTER AVAILABILITY GROUP [AdvGrpDRLinux]
MODIFY REPLICA ON N'LINUX08' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT)
GO
ALTER AVAILABILITY GROUP [AdvGrpDRLinux]
MODIFY REPLICA ON N'LINUX09' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT)
GO

ALTER AVAILABILITY GROUP [AdvGrpDRLinux]
SET (REQUIRED_COPIES_TO_COMMIT = 1)

-- demote old primary replica LINUX07
ALTER AVAILABILITY GROUP [AdvGrpDRLinux] SET (ROLE = SECONDARY); 

-- switch to new primary replica LINUX08
:CONNECT LINUX08 -U sa -PXXXXX
ALTER AVAILABILITY GROUP [AdvGrpDRLinux] FAILOVER;
GO

-- revert back to asynchronous mode
:CONNECT LINUX08 -U sa -PXXXXX
ALTER AVAILABILITY GROUP [AdvGrpDRLinux]
SET (REQUIRED_COPIES_TO_COMMIT = 0)

ALTER AVAILABILITY GROUP [AdvGrpDRLinux]
MODIFY REPLICA ON N'LINUX07' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT)
GO
ALTER AVAILABILITY GROUP [AdvGrpDRLinux]
MODIFY REPLICA ON N'LINUX08' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT)
GO
ALTER AVAILABILITY GROUP [AdvGrpDRLinux]
MODIFY REPLICA ON N'LINUX09' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT)
GO

After updating the DNS record to point to the new primary replica – LINUX08 with IP address equal to 192.168.40.24, transparent redirection and round-robin capabilities continued to work correctly.

blog 121 - ag linux second test sqlcmd

See you soon for other interesting new scenarios with availability groups on Linux!

 

 

 

Cet article SQL Server 2017 AlwaysOn AGs and new read-scale architectures est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator