Feed aggregator

Upcoming Public Appearances

Randolf Geist - Sun, 2016-10-09 16:54
It's that time of the year again... I'll be speaking at the following events:

- SSWUG 2016 Fall Virtual Conference: On November 15th I'll do the geeky stuff that was rejected by DOAG this year, which is "Oracle Database Parallel Execution plans deep dive - understanding the internals of Parallel Execution plans". You should only attend this session if you already have a good understanding of serial execution plans.

- DOAG Conference 2016: On November the 16th I'll present "Oracle Database Cloud Performance" where I talk about my experience regarding the performance of the Oracle DBaaS offerings. Note that some printed conference schedule material is outdated, I'll speak at 3pm, not 10am

- IT Tage 2016: On December 13th I'll present "Oracle 12c - Cost Based Optimizer Grundlagen" which describes the basics of the Cost Based Optimizer with an update how things might be different with Oracle 12c

Hope to see you at some of these events - if you're going to be there, say Hello!

oracle real time apply on standby

Learn DB Concepts with me... - Sun, 2016-10-09 11:47
 
                   Oracle Real Time Apply on Standby


By default, log apply services wait for the full archived redo log file to arrive on the standby database before applying it to the standby database. If the real-time apply feature is enabled, log apply services can apply redo data as it is received from the Primary DB, without waiting for the current standby redo log file to be archived. We can use the ALTER DATABASE statement to enable the real-time apply feature, as below:

  • For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.

  • For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

NOTE : Standby redo log files are required to use real-time apply.


Lets Test it:

oracle@ORCLSTDBY:[~] $ sqlplus /"as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 4 10:57:52 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  IDLE

-- Dest_id can be different in your database. but mostly it will be set to local.
-- Lets start applying logs and start the recovery mode Default (apply on log fill up)

SQL> recover managed standby database disconnect from session;
Media recovery complete.

-- Query the Recovery Mode now:

SQL> col DEST_NAME format A20
col status format A10
col type format A10
col recovery_mode format A30

select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  MANAGED

-- See that Recovery Mode will be just Managed. 

-- Lets stop log Apply and change it the recovery mode to Real-Time Apply

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME                       STATUS      TYPE       SRL        RECOVERY_MODE
--------------------                   ----------       ----------     ---          ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL   NO  MANAGED REAL TIME APPLY


-- We can also check this in alertlog_File.log


Completed: ALTER DATABASE RECOVER  managed standby database cancel
Tue Oct 04 11:00:47 2016
.
.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (ORCLSTDBY)
Tue Oct 04 11:00:47 2016
MRP0 started with pid=58, OS id=40557
MRP0: Background Managed Standby Recovery process started (ORCLSTDBY)
 started logmerger process
Tue Oct 04 11:00:52 2016
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...


Reference Oracle Docs:

https://docs.oracle.com/cd/B19306_01/server.102/b14239/log_apply.htm#i1034632


 Similar Posts :


Categories: DBA Blogs

How to patch Postgres Plus Advanced Server in a Standby configuration

Yann Neuhaus - Sun, 2016-10-09 09:57

In the last post we looked at how you can patch a Postgres Plus Advanced server. Wouldn’t it be nice, in a standby configuration, to patch the standby first without touching the master, then do a controlled switchover and finally patch the old master? In case this is a configuration with EDB Failover Manager the only downtime which would happen is the relocation of the VIP from one node to another (if you use a VIP). Without using a VIP but using pgpool-II the downtime is even less. Lets see if it works by starting from my usual EDB Failover Manager configuration.

This is the current status of my failover cluster:

[root@edbbart efm-2.1]# /usr/efm-2.1/bin/efm cluster-status efm
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.243       UP     UP        
	Standby     192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.245

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.243       0/4A000140       
	Standby     192.168.22.245       0/4A000140       

	Standby database(s) in sync with master. It is safe to promote.

All is fine, I have one master, one standby and one witness. Going straight forward lets shutdown the standby (please notice that I have disabled auto failover):

Shutdown the standby database:

postgres@edbppasstandby:/home/postgres/ [pg950] pg_ctl -D /u02/pgdata/PGSITE2 stop -m fast
waiting for server to shut down.... done
server stopped

What happened to my cluster?

[root@edbbart efm-2.1]# /usr/efm-2.1/bin/efm cluster-status efm
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.243       UP     UP        
	Standby     192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.245

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.243       0/4A000140       
	Unknown     192.168.22.245       UNKNOWN          Connection to 192.168.22.245:4445 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

	One or more standby databases are not in sync with the master database.
[root@edbbart efm-2.1]# 

Not really surprising EFM complains that the standby is not reachable anymore. Thats fine. Lets patch the standby:

postgres@edbppasstandby:/u01/app/postgres/software/ [pg950] chmod +x postgresplusas-9.5.4.9-1-linux-x64.run
postgres@edbppasstandby:/u01/app/postgres/software/ [pg950] ./postgresplusas-9.5.4.9-1-linux-x64.run --extract-only true --prefix /u01/app/postgres/product/95/db_1/9.5AS/
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : 1
----------------------------------------------------------------------------
Welcome to the Postgres Plus Advanced Server Setup Wizard.

----------------------------------------------------------------------------
Please specify the directory where Postgres Plus Advanced Server will be 
installed.

Installation Directory [/u01/app/postgres/product/95/db_1/9.5AS]: 

----------------------------------------------------------------------------
Setup is now ready to begin installing Postgres Plus Advanced Server on your 
computer.

Do you want to continue? [Y/n]: Y

----------------------------------------------------------------------------
Please wait while Setup installs Postgres Plus Advanced Server on your computer.

 Installing Database Server
 0% ______________ 50% ______________ 100%
 #########################################

----------------------------------------------------------------------------
Setup has finished installing Postgres Plus Advanced Server on your computer.

postgres@edbppasstandby:/u01/app/postgres/software/ [pg950] 

… bring it up again:

postgres@edbppasstandby:/home/postgres/ [pg950] pg_ctl -D /u02/pgdata/PGSITE2 start
server starting

… checking the PostgreSQL log file all is fine, streaming restarted:

2016-10-05 11:35:25.745 GMT - 2 - 4984 -  - @ LOG:  entering standby mode
2016-10-05 11:35:25.751 GMT - 3 - 4984 -  - @ LOG:  consistent recovery state reached at 0/4A000108
2016-10-05 11:35:25.751 GMT - 4 - 4984 -  - @ LOG:  redo starts at 0/4A000108
2016-10-05 11:35:25.751 GMT - 5 - 4984 -  - @ LOG:  invalid record length at 0/4A000140
2016-10-05 11:35:25.751 GMT - 4 - 4982 -  - @ LOG:  database system is ready to accept read only connections
2016-10-05 11:35:25.755 GMT - 1 - 4988 -  - @ LOG:  started streaming WAL from primary at 0/4A000000 on timeline 8

What is the status of EFM?

postgres@edbppasstandby:/home/postgres/ [pg950] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.243       UP     UP        
	Idle        192.168.22.245       UP     UNKNOWN   

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	(List is empty.)

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.243       0/4A000140       

	No standby databases were found.

Idle Node Status (idle nodes ignored in XLog location comparisons):

	Address              XLog Loc         Info
	--------------------------------------------------------------
	192.168.22.245       0/4A000140       DB is in recovery.

Status “Idle” for the standby which is fine, just resume:

postgres@edbppasstandby:/home/postgres/ [pg950] sudo /usr/efm-2.1/bin/efm resume efm
Resume command successful on local agent.
postgres@edbppasstandby:/home/postgres/ [pg950] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Witness     192.168.22.244       UP     N/A       
	Standby     192.168.22.245       UP     UP        
	Master      192.168.22.243       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.245

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.243       0/4A000140       
	Standby     192.168.22.245       0/4A000140       

	Standby database(s) in sync with master. It is safe to promote.

… and everything is back as it should be. Time to switchover:

postgres@edbppasstandby:/home/postgres/ [PGSITE2] sudo /usr/efm-2.1/bin/efm promote efm -switchover
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.

The master and the standby should have switched its roles:

postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Master      192.168.22.245       UP     UP        
	Witness     192.168.22.244       UP     N/A       
	Standby     192.168.22.243       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.243

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.245       0/4B0001A8       
	Standby     192.168.22.243       0/4B0001A8       

	Standby database(s) in sync with master. It is safe to promote.
postgres@edbppasstandby:/home/postgres/ [PGSITE2] 

Same procedure again, stop the standby:

postgres@edbppas:/home/postgres/ [PGSITE1] pg_ctl -D /u02/pgdata/PGSITE1 stop -m fast
waiting for server to shut down.... done
server stopped
postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     192.168.22.243       UP     UP        
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.243

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.245       0/4B0001A8       
	Unknown     192.168.22.243       UNKNOWN          Connection to 192.168.22.243:4445 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

	One or more standby databases are not in sync with the master database.

Apply the patch:

postgres@edbppas:/u01/app/postgres/software/ [PGSITE1] ./postgresplusas-9.5.4.9-1-linux-x64.run --extract-only true --prefix /u01/app/postgres/product/95/db_1/9.5AS/
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : 1
----------------------------------------------------------------------------
Welcome to the Postgres Plus Advanced Server Setup Wizard.

----------------------------------------------------------------------------
Please specify the directory where Postgres Plus Advanced Server will be 
installed.

Installation Directory [/u01/app/postgres/product/95/db_1/9.5AS]: 

----------------------------------------------------------------------------
Setup is now ready to begin installing Postgres Plus Advanced Server on your 
computer.

Do you want to continue? [Y/n]: y

----------------------------------------------------------------------------
Please wait while Setup installs Postgres Plus Advanced Server on your computer.

 Installing Database Server
 0% ______________ 50% ______________ 100%
 #########################################

----------------------------------------------------------------------------
Setup has finished installing Postgres Plus Advanced Server on your computer.

Startup again:

postgres@edbppas:/u01/app/postgres/software/ [PGSITE1] pg_ctl -D /u02/pgdata/PGSITE1 start
server starting

Streaming restarted:

2016-10-05 11:45:36.807 GMT - 2 - 4883 -  - @ LOG:  entering standby mode
2016-10-05 11:45:36.810 GMT - 3 - 4883 -  - @ LOG:  consistent recovery state reached at 0/4B0000C8
2016-10-05 11:45:36.810 GMT - 4 - 4883 -  - @ LOG:  redo starts at 0/4B0000C8
2016-10-05 11:45:36.810 GMT - 5 - 4883 -  - @ LOG:  invalid record length at 0/4B0001A8
2016-10-05 11:45:36.810 GMT - 4 - 4881 -  - @ LOG:  database system is ready to accept read only connections
2016-10-05 11:45:36.815 GMT - 1 - 4887 -  - @ LOG:  started streaming WAL from primary at 0/4B000000 on timeline 9

Same status “Idle” as before:

postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Idle        192.168.22.243       UP     UNKNOWN   
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	(List is empty.)

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.245       0/4B0001A8       

	No standby databases were found.

Idle Node Status (idle nodes ignored in XLog location comparisons):

	Address              XLog Loc         Info
	--------------------------------------------------------------
	192.168.22.243       0/4B0001A8       DB is in recovery.

Resume:

postgres@edbppas:/home/postgres/ [PGSITE1] sudo /usr/efm-2.1/bin/efm resume efm
Resume command successful on local agent.

Fully back:

postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     192.168.22.243       UP     UP        
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.243

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.245       0/4B0001A8       
	Standby     192.168.22.243       0/4B0001A8       

	Standby database(s) in sync with master. It is safe to promote.

Works like a charm. The organizational overhead is much more than what you actually need to do. Technically this is a task of a few minutes.

 

Cet article How to patch Postgres Plus Advanced Server in a Standby configuration est apparu en premier sur Blog dbi services.

reports (cvucheckreport_*.xml) from ora.cvu resource causing excessive disk space

Syed Jaffar - Sun, 2016-10-09 06:34
On Exadata dbnodes, we noticed that Clusterware 12.1.0.2 ora.cvu resource generating XML reports every 6 hours and occupied large amounts of space on /u01 filesystem. Perhaps this is the same on non-Exadata systems too, if you are running 12.1.0.2 Clusterware.

Upon looking into MOS ID (reports from ora.cvu resource using excessive disk space (Doc ID 1964482.1) it was learned that this is an expected behavior with 12.1.0.2 where ora.cvu invokes Clusterware Health check (CHC) every 6 hrs and stores the XML reports under $GI_HOME/cvu/baseline or $ORACLE_BASE/crsdata/@global/cvu/baseline' location. However, the CVU didn't clean-up the reports periodically which ultimately causes  disk full.

As per the note, the bug is fixed as follows:

Bug 18143707 is fixed in 12.1.0.2
Bug 19703199 is fixed in 12.1.0.2.4 PSU
Bug 20177779 is fixed in 12.1.0.2.5 PSU

Quick workaround to this problem is to delete the files (xml) manually in order to release the space.

References:

reports from ora.cvu resource using excessive disk space (Doc ID 1964482.1)
ora.cvu Resource Reports not Purged (Doc ID 2054765.1)
12.1.0.2 Grid Infrastructure Patch Set Updates - List of Fixes in each GI PSU (Doc ID 1928853.1)

sql query related question

Tom Kyte - Sun, 2016-10-09 05:26
i want to compare this month data(ex:sep-2016) with previous year month data(ex:sep-2015) in same page of SQL PLUS by using date column(i.e.DAL_ARR_DATE_TIME) of my table,so please give me a solution for the same.thanks in advance. my table structur...
Categories: DBA Blogs

Implicit rollback on error in NDS and dbms_sql

Tom Kyte - Sun, 2016-10-09 05:26
Good day. Could you please explain to me why the following script returns the next output? <code> in foo_proc Static call - 1 in foo_proc NDS - 0 in foo_proc dbms_sql - 0 </code> Why the inserted row is implicitly rollbacked before th...
Categories: DBA Blogs

Physical Standby database - user sync

Tom Kyte - Sun, 2016-10-09 05:26
Will new users created on the primary database be synced to the standby database automatically?
Categories: DBA Blogs

non-cdb architecture on 12c

Tom Kyte - Sun, 2016-10-09 05:26
Tom. If we upgrade our 11g database to 12c 12.1.0.2, can we still use the non-cdb model? With 11g going into extended support very soon we would like to upgrade to 12c however, we are not familiar with PDBs yet so we would prefer to mo...
Categories: DBA Blogs

ORA-01156 When Adding Standby Redo Log in Dataguard Configuration

Pakistan's First Oracle Blog - Sat, 2016-10-08 20:20
Database version = 11.2.0.3.0

If you get following error while adding a Standby Redo Log (SRL) to a standby database in dataguard configuration:

SQL> alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M;
alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


Then it means that first you have to stop the redo apply, add the SRL and then start the redo apply. Best way to do is from dgmgrl like this:

DGMGRL> connect /
Connected.

DGMGRL> edit database 'test' set state='APPLY-OFF';
Succeeded.

SQL> alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M;

Database altered.

DGMGRL> edit database 'CONPRO_UK' set state='APPLY-ON';
Succeeded.

HTH.











Categories: DBA Blogs

POUG

Rittman Mead Consulting - Sat, 2016-10-08 13:20

I've just attended my first user group in Poland, the very-excellent POUG. This was the first international version of the conference, having been run previously just within Poland. Based on these two days, I would say it was a resounding success! There was a great atmosphere there, really engaged and enthusiastic. The size of the event, friendliness, and fun made it a very welcoming one, and reminded me a lot of my first ever conference that I attended (back in 2010!), the Rittman Mead BI Forum.

I travelled out to Warsaw on the Thursday, and attended the speakers dinner. It's always nice to see familiar faces and meet new ones - as well as enjoy some jolly good food. The next morning I went for a run at the nearby park, enjoying the sunrise over the water

Good morning Warsaw! #POUG https://t.co/aHAuYonlhE pic.twitter.com/LYgU50bIei

— Robin Moffatt (@rmoff) October 7, 2016

The conference had two tracks, focussing primarily on the database but with some BI content too, as well a talk about property graphs. The first session I attended was also one of my favourites. It was "DBA, Heal Thyself: Five Diseases of IT Organizations and How to Cure Them", presented with great humour and energy by Jim Czuprynski. Some of the uncomfortable truths about mistakes made in the field of IT were laid bare, with no prisoners taken! Whilst it was based on the database, much of it was applicable to OBIEE. Things like reliance on bespoke scripts, avoidance of new features, and general ignorance in the field were covered. You can find an article on the topic from Jim here.

After Jim's session was Hans Viehmann talking about Property Graphs. I wrote an article that was published on OTN just last week about this and it was great to get chance to meet Hans, and hear him speak.

I skipped the next session to do a bit of slide polishing and geek chat with Christian Berg, before getting lunch - which was really good:

Just a light lunch at #POUG pic.twitter.com/B3Bntf1JCU

— Robin Moffatt (@rmoff) October 7, 2016

After lunch was OBIEE presentations in both tracks, by Christian Berg, and Kiran Taylor. I sat in on Christian's "Fifty shades of #fail", which is a fun walk through some of the many ways that OBIEE gets done wrong.

My talk, "(Still) No Silver Bullets - OBIEE 12c Performance in the Real World" was the last of the day. I've given this talk quite a few times now, but still enjoy delivering it each time. The topic's one I've spent so much time working on and find so interesting, that it never gets stale! You can find the slides here and set of related links here.

.@rmoff - OBIEE performance in the real world - where is it slow? - #POUG pic.twitter.com/IanRoHBX2G

— Kiran Tailor (@KiranTailorUK) October 7, 2016

The day finished with the POUG After Party, which was at a bar in the center of Warsaw. Good beer, good food, good music - and plenty of geek talk! I really have to take my hat off to the organisers of POUG, they did a great job.

#POUG #bagpipes pic.twitter.com/ZC8WGwq8Z6

— Robin Moffatt (@rmoff) October 7, 2016

The second day of POUG brought more good presentations. I got to see Neil Chandler speak, about execution plans and how they can vary - and how to ensure they don't unless you want them too. It was really interesting, and took me back a few years to when I last looked in-depth at this kind of stuff. After Neil was Jim again, talking about analytic functions. Most of these I knew about, but one that was new to me (and I'll definitely be using) was the PERCENT syntax for FETCH FIRST - very neat.

Great stuff from @JimTheWhyGuy at #POUG. FETCH FIRST I knew… but now I know you can use PERCENT with it too. Cool! pic.twitter.com/f10yoz8yge

— Robin Moffatt (@rmoff) October 8, 2016

The audience at POUG seemed to be predominantly DBAs and database developers, and this kind of talk is just great for spreading awareness of new functionality that is going to make people's jobs easier, and their code run faster. The final talk of the morning was from Martin Widlake, presenting a great discussion about efficient and maintainable bulk processing with SQL and PL/SQL. With his very accessible and engaging presentation style, Martin's talk was an extremely pragmatic and valuable one. Everyone loves a deep-dive geekout on system internals (don't they??), but arguably the most value to the widest section of the audience comes in learning, or being reminded of, how to code and design systems well.

Even without lots of BI content, I found the conference very useful. Whilst Oracle CBO internals may not be my day to day work, many of the topics discussed in a database context can easily be transplanted to the BI world. Performance is performance. Diagnostic approaches are tool-agnostic. As well as the presentations, the opportunity to exchange ideas and war-stories with other experts in the industry (over a beer, usually…) is the kind of thing you just don't get from reading the manuals or a bunch of PDFs.

So that was POUG, and all too soon time return home. Bravo to the organisers of POUG, and here's hoping they run the conference again next year!

Well that was it for #poug. So long @POUG_ORG and thanks for all the fish! pic.twitter.com/mv9Es95yfr

— Christian Berg (@Nephentur) October 8, 2016
Categories: BI & Warehousing

How to speed up an Insert with SELECT

Tom Kyte - Sat, 2016-10-08 11:06
Hi Tom, good morning Sir. I am trying to speed up an Insert statement where the SELECT is selecting all the source table according to this example: var_select_a_insert := 'insert into lwm_usrappods.ITEM_LOC_SOH select to_number...
Categories: DBA Blogs

High number of misses in library cache during execute - where to dig for explanations?

Tom Kyte - Sat, 2016-10-08 11:06
I'm relatively familiar with using SQL_TRACE and tkprof for tuning, but I recently spotted something in a tkprof report for one of our customers that raised my suspicions. There is a lot of SQL in the file where the number of library cache executi...
Categories: DBA Blogs

Session wait time of a transaction

Tom Kyte - Sat, 2016-10-08 11:06
Dear Tom, please help me with the below. Session 1: update table set field = 'A' where field2 = 'B'; it is not committed/rollbacked Session 2: update table set field = 'C' where field2 = 'B'; The session will be waiting for sessio...
Categories: DBA Blogs

How to observe current values of a running plsql code.

Tom Kyte - Sat, 2016-10-08 11:06
Hello, I can observe the current value of a bind variable, using v$sql_bind_capture. I don't know how to do it, when having no bind variable. Having a simple code like this below, I would like to see a VALUE of rec.column1 being passed to my_functi...
Categories: DBA Blogs

BULK Delete

Tom Kyte - Sat, 2016-10-08 11:06
Hi Tom, This is my first question to you and hope to receive a positive response :) Straight to the Question: I have a table named trail_log. This is a huge table and a daily growth of this table is 12GB Approx. Currently 76216160 rows. Import...
Categories: DBA Blogs

How to determine if db objects are still used?

Tom Kyte - Sat, 2016-10-08 11:06
I am working on a database that has nearly 3,900 tables, and 450 views, 90 packages, 740 procedures, and 325 functions. Some of those are called by apps and report servers that I do not have access to all the source code. I have a feeling that m...
Categories: DBA Blogs

UNDO tablespace usage

Tom Kyte - Sat, 2016-10-08 11:06
Hi Team, I want a customized query which should give me which SQL statement is taking more amount of undo tablespace size with all the details. Request you to please help me on this. Regards, Sridhar
Categories: DBA Blogs

Copy millions of blobs from one table to the other one

Tom Kyte - Sat, 2016-10-08 11:06
Hello colleagues, I've got a "small" problem, I have to copy millions of blobs from one table to another one. <b>Target</b> table: PAGE ---------------------- ID NOT NULL NUMBER(19) DOCUPLOADCODE VARCHAR2(255) MIMETYPE ...
Categories: DBA Blogs

macOS Sierra (OS X 10.12)

Tim Hall - Sat, 2016-10-08 08:15

In the 80s and 90s the Sierra was an incredibly popular, but boring saloon car from Ford. Today I upgraded to macOS Sierra, which will eventually be an incredibly popular (for Mac owners), but boring operating system from Apple.

So what’s new? You get Siri! Oh yes, you also get Siri! There is also Siri! And finally, for good measure you get Siri!

If you own multiple devices, there is some fluff, like shared clipboard, but I don’t own multiple Apple devices, so basically this OS is what I had before with a sprinkling of Siri.

What was the experience of upgrading like? An absolute nightmare! It said it was going to take about 18 minutes, but it seemed to hang for a couple of hours. I ended up doing about 5 hard reboots before it actually came up. After all that hassle and wasted time, I got El Crapitan + Siri. Amazing. Way to innovate Apple!

Of course, Apple fanboys will love it and it will change their lives… Whatever!

Cheers

Tim…

macOS Sierra (OS X 10.12) was first posted on October 8, 2016 at 2:15 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios

Yann Neuhaus - Sat, 2016-10-08 07:03

Some time ago, I had to deal with a new partitioning scenario that included sliding windows stuff for mainly archiving purpose. Regarding the customer context, I used some management scripts that include this time dropping oldest partition. We didn’t care about data oldest than 2 years. Usually in this case, I use a method that consists in dropping data by switching first the oldest partition to a staging table and then truncate it. Finally we may safely merge oldest partitions and avoid any data movement. At a first glance, it seems to be a complex process for dropping data but until SQL Server 2014 there is no way to do better in order to minimize operation logging.

 

blog 105 - 0 - partitioning

This week, I had the opportunity to work with SQL Server 2016 to learn about new partition improvements. By the way, the only thing I could find out from my different internet researches concerned the new command TRUNCATE TABLE WITH PARTITIONS.

My first feeling was it is not a very exciting feature in contrast to previous versions that provided a mixture of both performance and maintenance improvements in this field. But after investigating further, I was able to point out some advantages to use this command. Let’s go back to my sliding windows scenario. In order to drop data from my oldest partition I have to:

  • Switch the oldest partition to a staging table
  • Drop data from this staging table with TRUNCATE command in order to minimize transaction logging
  • Execute MERGE command in order to slide all partitions to the left side

 

What about introducing the new TRUNCATE TABLE command in this scenario?

In fact, it will simplify the above process by replacing step 1 and step 2 by the TRUNCATE command at the partition level. The new scenario becomes:

  • TRUNCATE TABLE at the corresponding partition
  • Execute MERGE command in order to slide all partitions to the left

 

The only instruction I need to use is as follows:

TRUNCATE TABLE [dbo].[FactOnlineSales]
WITH ( PARTITIONS (2) );

 

What about locking?

As expected, SQL Server will use a lock granularity hierarchy with a mixture of Sch-S, Sch-M and X locks regarding the corresponding locked resource. You may see two allocation units in my case because I’m using a partitioned clustered columnstore index in this demo. As a reminder, compressed columnstore segments are stored in LOB.

 

Object Resource type Resource subtype Resource description Associated entity Lock request mode OBJECT FactOnlineSales Sch-M METADATA DATA_SPACE data_space_id = 3 Columnstore2007 (filegroup that relies on partition nb 2) Sch-M HOBT Partition nb 2 Sch-M ALLOCATION_UNIT Related to data_space_id = 3 with state = DROPPED (LOB_DATA) X ALLOCATION_UNIT Related to data_space_id = 3     (IN_ROW_DATA) X KEY Records in the partition 2 X

 

 

What about logging?

Well, if I refer to the corresponding records into the transaction log file, TRUNCATE partition command seems to act as a normal TRUNCATE operation. Firstly, we may notice few records generated related to marking the concerned structures to drop and then the deferred drop mechanism comes into play by deallocating them.

 

blog 105 - 1 - truncate partitions tlog 1

blog 105 - 1 - truncate partitions tlog 2

blog 105 - 1 - truncate partitions tlog 3

blog 105 - 1 - truncate partitions tlog 4

Happy partitioning!

 

 

 

 

 

Cet article SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator