DBA Blogs

9 Days of Learning About Engineered Advantages

APRIL 2016 ...

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

java.lang nullpointerexception While Installing 12.1.0.2 RAC Database Software

Oracle in Action - Mon, 2016-04-18 04:41

RSS content

I have an Oracle  12.1.0.2c cluster having following 5 nodes :

  • Hub Nodes : host01, host02, host03
  • Leaf nodes: host04 and host05

I wanted to install RAC database software on the 3 hub nodes i.e. host01, host02 and host03.

I invoked the OUI as oracle user (Owner of Database Home)

[oracle@host01 database_12_1_0_2]$ ./runInstaller

and chose the option to install RAC database software only on the 3 hub nodes.

After all the pre-requisite checks were successful , I clicked the Install button.
I got the error: java.lang nullpointerexception

On clicking OK, the OUI aborted.

To troubleshoot, I ran the OUI in debug mode as :

[oracle@host01 database_12_1_0_2]$ ./runInstaller -debug -J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2 -J-DSRVM_TRACE_LEVEL=2 -J-DFULLTRACE

The trace file showed that the leaf node host05 was not responding:

[setup.flowWorker] [ 2016-04-18 14:33:22.771 IST ]
[CRSCTLUtil.getClusterNodeConfigRoles:1198] outputs[4] = CRS-4404: The following nodes did not reply within the allotted time:
[setup.flowWorker] [ 2016-04-18 14:33:22.772 IST ]
[CRSCTLUtil.getClusterNodeConfigRoles:1200] values length = 2
[setup.flowWorker] [ 2016-04-18 14:33:22.772 IST ]
[CRSCTLUtil.getClusterNodeConfigRoles:1198] outputs[5] = host05
[setup.flowWorker] [ 2016-04-18 14:33:22.772 IST ]
[CRSCTLUtil.getClusterNodeConfigRoles:1200] values length = 1

I realized that although I wanted to install database software on 3 nodes only, all the nodes
needed to be up and running whereas in my case, the node host05 was down at that time.

When I started the node host05 and reinvoked OUI, the database software was installed
successfully.

References:
http://www.hhutzler.de/blog/oracle-installer-and-rac-installation/



Tags:  

Del.icio.us
Digg

Copyright © ORACLE IN ACTION [java.lang nullpointerexception While Installing 12.1.0.2 RAC Database Software], All Right Reserved. 2016.

The post java.lang nullpointerexception While Installing 12.1.0.2 RAC Database Software appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Online Relocation of Database File : ASM to FileSystem and FileSystem to ASM

Hemant K Chitale - Sun, 2016-04-17 10:44
There have been few published examples of the online datafile relocation feature in 12c.  The examples I've seen are on filesystem.

Here I show online relocation to/from ASM and FileSystem.

SQL> connect system/oracle
Connected.
SQL> create tablespace test_relocate;

Tablespace created.

SQL> create table test_relocate_tbl
2 tablespace test_relocate
3 as select * from dba_objects;

Table created.

SQL> select tablespace_name, bytes/1024
2 from user_segments
3 where segment_name = 'TEST_RELOCATE_TBL';

TABLESPACE_NAME BYTES/1024
------------------------------ ----------
TEST_RELOCATE 13312

SQL> select file_name, bytes/1024
2 from dba_data_files
3 where tablespace_name = 'TEST_RELOCATE';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024
----------
+DATA/NONCDB/DATAFILE/test_relocate.260.909444793
102400


SQL>
SQL> alter database move datafile
2 '+DATA/NONCDB/DATAFILE/test_relocate.260.909444793'
3 to '/oradata/NONCDB/test_relocate_01.dbf';

Database altered.

SQL> !ls -l /oradata/NONCDB
total 102408
-rw-r----- 1 oracle asmdba 104865792 Apr 17 23:39 test_relocate_01.dbf

SQL> 
SQL> alter database move datafile  
2 '/oradata/NONCDB/test_relocate_01.dbf'
3 to '+DATA';

Database altered.

SQL> select file_name, bytes/1024
2 from dba_data_files
3 where tablespace_name = 'TEST_RELOCATE';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024
----------
+DATA/NONCDB/DATAFILE/test_relocate.260.909445261
102400


SQL>
SQL> !ls -l /oradata/NONCDB
total 0

SQL>


Note that I was courageous enough to not use the KEEP keyword (which is optional !).
.
.
.

Categories: DBA Blogs

Links for 2016-04-16 [del.icio.us]

Categories: DBA Blogs

What’s Holding You Back From Giving Back?

Pythian Group - Fri, 2016-04-15 10:19

This week in honour of National Volunteer Week, I’m reflecting on the importance that volunteering has had in my life.

I’ve learned from each and every one of my experiences. From wrapping holiday presents at the mall, to helping source articles for an industry magazine, to wish granting, recruiting committee and board members, and providing HR advice and counsel. These experiences eventually led me to become a member of the board of directors for a number of organizations, and I was even named Board Chair.

Ironically, the rewards and benefits that I have received from the experiences far outweigh any amount of time I have given the many organizations I have supported over the years. Volunteering has provided me the opportunity to expand my skills and experience, and take on leadership roles long before I had the credentials to be hired for them. I initially started volunteering when I moved to Ottawa, and quickly learned that there is no better way to get to know your community, meet new people and expand your network. Once I started, I never looked back. I caught the “volunteer bug.” It is an important part of my life.

I am often asked how I find the time to volunteer. I always respond with, “like anything, if it’s important to you, you can and will find the time.” As I have expanded my family and career, I seek opportunities where I can continue to share my knowledge, skills and experience in ways that do not impede on either. A perfect example of this would be career mentoring. I have been a mentor for a number of organizations including the HRPA, OCISO, and the WCT. I have been fortunate to have great mentors in the past and now pay it forward. I remain connected with many of them.

In my role as VP of HR at Pythian I was thrilled to champion our Love Your Community Programs. These programs provide our employees in over 36 countries with a volunteer day and opportunities for sponsorship – i.e. raising money for causes that are meaningful to them. The programs have allowed Pythian the opportunity to positively impact the communities where our employees live.

Volunteer Week highlights the importance of volunteering in our communities, and showcases the impact it has on the lives of both the volunteer, and the communities they support. What’s holding you back from giving back?

And because it couldn’t be said any better: “We make a living by what we get, but we make a life by what we give.”

Winston Churchill

Categories: DBA Blogs

Optimizer Stew – Parsing the Ingredients to Control Execution Plans

Pythian Group - Fri, 2016-04-15 09:01

No matter how many times I have worked with Outlines, Baselines and Profiles, I keep having to look up reminders as to the differences between these three.

There is seemingly no end to articles to the number of articles and blog that tell you what needs to be licensed, how to use them, and which version of Oracle where each made its debut appearance.

This blog will discuss none of that.  This brief article simply shows the definitions of each from the Glossary for the most current version of the Oracle databases. As of this writing that version is 12.1.0.2.

And here they are.

Stored Outline

A stored outline is simply a set of hints for a SQL statement. The hints in stored outlines direct the optimizer to choose a specific plan for the statement.

Link to Stored Outline in the Oracle Glossary

SQL plan baseline

A SQL baselines is a set of one or more accepted plans for a repeatable SQL statement. Each accepted plan contains a set of hints, a plan hash value, and other plan-related information. SQL plan management uses SQL plan baselines to record and evaluate the execution plans of SQL statements over time.

Link to SQL Plan Baseline in the Oracle Glossary

SQL profile

A SQL profile is a set of auxiliary information built during automatic tuning of a SQL statement. A SQL profile is to a SQL statement what statistics are to a table. The optimizer can use SQL profiles to improve cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.

Link to SQL Profile in the Oracle Glossary

Categories: DBA Blogs

How to solve “ORA-03135 connection lost contact” error?

VitalSoftTech - Thu, 2016-04-14 20:54
Users are connected to the database via TNS and abruptly get disconnected. What is the cause of the "ORA-03135 connection lost contact" error? How can this be avoided?
Categories: DBA Blogs

Partner Webcast – Transition to the New Integration Model with Oracle SOA Cloud Service

Do you want to fully integrate your enterprise, using the same integration tool and skills for both cloud and on premises deployment? Oracle’s hybrid integration platform allows you to extract...

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

Log Buffer #469: A Carnival of the Vanities for DBAs

Pythian Group - Wed, 2016-04-13 09:40

This Log Buffer Edition digs deep into the realms of Oracle, SQL Server and MySQL and brings together a few of the top blog posts.

Oracle

We’ve all encountered a situation when you want to check a simple query or syntax for your SQL and don’t have a database around. Of course, most of us have at least a virtual machine for that, but it takes time to fire it up, and if you work from battery, it can leave you without power pretty quickly.

View Criteria is set to execute in Database mode by default. There is option to change execution mode to Both. This would execute query and fetch results from database and from memory.  Such query execution is useful, when we want to include newly created (but not committed yet) row into View Criteria result. Newly created row will be included into View Criteria resultset.

Upgrading database hardware in an organization is always a cumbersome process. The most time consuming step is, planing for the upgrade, which mainly includes choosing right hardware for your Oracle databases. After deciding on the hardware type for your databases, rest will be taken care by technical teams involved.

Gluent New World #02: SQL-on-Hadoop with Mark Rittman

The pre-12c implementation of DCD used TNS packages to “ping” the client and relied on the underlying TCP stack which sometimes may take longer. Now in 12c this has changed and DCD probes are implemented by TCP Stack. The DCD probes will now use the TCP KEEPALIVE socket.

 

SQL Server

Snippets will allow you to code faster by inserting chunks of code with few key strokes.

One of more common concerns among database administrators who consider migrating their estate to Azure SQL Database is their ability to efficiently manage the migrated workloads.

A SQL Server Patching Shortcut

Move an Existing Log Shipping Database to a New Monitor Server

Knee-Jerk Performance Tuning : Incorrect Use of Temporary Tables

 

MySQL

MySQL 5.7 sysbench OLTP read-only results: is MySQL 5.7 really faster?

7 Galera Cluster presentations in Percona Live Santa Clara 18-21.4. Meet us there!

Generate JSON Data with dbForge Data Generator for MySQL v1.6!

Extending the SYS schema to show metadata locks

EXPLAIN FORMAT=JSON wrap-up

Categories: DBA Blogs

Links for 2016-04-12 [del.icio.us]

Categories: DBA Blogs

Installing SQL Server 2016 – Standalone Instance – New Features

Pythian Group - Tue, 2016-04-12 10:48

In this article I am going to go through a typical install of SQL Server 2016, and explain some of the best practices to follow when setting up a production server. I will also take a look at the new features when installing SQL Server 2016 compared to older versions.

The version of SQL Server I am using in SQL Server 2016 RC2. That means that some of the features may change between the time of writing and the retail release of SQL Server 2016.

 

Let’s Get Started!

The first screen you come to after opening the installation media:

Initial Screen

  1. Click the Installation button highlighted, and then the first option in the list New SQL Server Standalone Installation.

 

New for SQL Server 2016
For the Seasoned SQL Server DBA you will notice a few additions to this screen.

  • SQL Server Management Tools can now be installed from this screen. The files and binaries will be downloaded when you click this link, as they are no longer bundled with the installation media.
  • SQL Server Data Tools can also be installed from this screen.
  • Additionally, a standalone instance of R can be installed. R is a statistical programming language embedded into SQL server 2016, making it easy for data scientists and BI professionals to get a good level of analysis without leaving the SQL Server environment.

 

  1. Select the edition of SQL SERVER you would like you would like to install, I chose developer as it’s a full featured installation of SQL Server that can be used for development only, and not in a production environment. You could also enter a key in here instead of selecting a version.

Edition

  1. Read and accept the licence.
  2. SQL Server will then check a few rules to make sure you can install SQL server on your hardware.
  3. Select whether or not to allow Microsoft to check for updates and click next.
  4. SQL Server Installation will then install some setup files needed for installation and perform a few more checks. As you can see below, a warning has appeared asking me to make sure the correct ports are open on the firewall. Click Ok and then Next to proceed with the installation.

Rule Check

  1. The next screen is where we want to select all the features we want to install. As my machine is being set up as a dev machine to test SQL 2016 features, I am installing all the features. In a production environment only install the features that are needed. You can also select where to install the binaries and the root instance from this screen. Click next once you have selected the settings needed.

Features

New for SQL Server 2016

  • The ability to install a standalone instance of R is now available in the Shared Features Section.
  • The ability to install R services in the database engine is now available.
  • The ability to install Polybase Query Service for external sources is now available to install. Polybase query service allows users to query big data sources such as Hadoop using common T-SQL statements. If you are planning on installing the Polybase feature, then the Java Runtime SRE needs to be installed first.

 

  1. The next screen is where you will need to name the instance, if this is not the only SQL server installation on this hardware. It also confirms the installation directory as per the previous screens. As this is the only installation of SQL Server on this machine, I am going to leave these settings as default. Click next when ready to proceed.

Default Instance

  1. The Next Screen is the Server Configuration screen. You should run each service under a domain account with a strong password. Whether you use managed accounts or do not enforce password expiration is up to you. However, these accounts are going to be running your services and should always be available. As this is a test machine not connected to a domain, I will leave the defaults. You can also select your default server collation from this window by clicking on the tab at the top highlighted in yellow. It is important also to set the start-up type parameters to allow services to start automatically on reboot if needed.

The ability to allow Perform Volume Maintenance tasks to be checked from this screen is a new feature. This is a best practice among SQL server DBA to allow instant file initialization. Previously this had to be done outside of the installation window.

Server Config

 

New for SQL Server 2016

  • The ability to allow Perform Volume Maintenance tasks to be checked from the Server Configuration Screen.

 

  1. Next up is the database configuration screen, and we are going to step through the tables.

In the first tab you will want to add all the users that require sysadmin access. To administer the server, click on the Add Current user and Add buttons. I always use mixed mode authentication so I can still get to the server if Active Directory plays up, and I add a complex password for security. When your done, click on the Data Directories tab at the top.

Database Config1

This is the screen where you set up all of your default directories for your databases.

Best practice states that we should put Log files on separate disks to Data Files, as there are two different access patterns for these, so they would be more performant on separate disks. Backups should also be on their own disks where possible. Additionally, the OS should also have its own drive separate from all SQL server files. As this is a test server and I only have a c drive, I will leave them as default. Click TempDB tab when ready.

Database Config 2

 

New For SQL 2016

  • This is new in SQL 2016, and previously had to be configured after install. This screen allows us to create files for temp db. Best practice stated there should be 1 file per logical core up to a maximum of 8 as I have 4 cores in my machine I have created 4 files. You can also spread the files over more than one disk if needed. Once you’re happy with your selections click next.

Database Config 3

 

  1. The next screen is to configure Analysis Services. I have configured mine in Multidimensional mode adding myself as sysadmin and setting directories using same best practice as database engine. Look out for a further blog article on SQL server Analysis Services.

SSAS

 

  1. Leave the default options for Reporting Services. Again keep an eye out for another article on Reporting Services.
  2. In the next screen you have to configure the users capable of using the Distributed Replay Controller.
  3. Give a name to the DRC.

DRC_Name

  1. On the next Screen you will need to accept the terms of the features being installed by clicking accept, and then next.
  2. Finally, you can click install and that’s it! SQL Server 2016 and all its new features are installed.

Final

 

Here is a great link on some of the new features available in SQL Server 2016

Categories: DBA Blogs

SQL Server 2016 : A New Security Feature – Always Encrypted

Pythian Group - Tue, 2016-04-12 09:15

Security. This word is so important when it comes to data, and there is a reason why. Every business has it’s vital data, and this data has to be accessed only by those who are authorized. Back in 2009, I wrote an article on what measures to take when it comes to securing SQL Server.

There were days when we used to have a third party tool to encrypt the data inside SQL Server. Later, Microsoft introduced Transparent Data Encryption (TDE) bundled with the release of SQL Server 2008. You may be wondering why it is so important to encrypt the data. Inside our database, there may be a case that the customer/application has to enter and store the sensitive information such as Social Security Number (SSN) or Financial/Payment Data, which should not be read in plain text, even by a DBA. With this requirement, a strong encryption and/or data masking comes into the picture.

With the launch of SQL Server 2016 Release Candidate 0 (RC0) , Microsoft has introduced two new features that are my personal favorite – 1)  Always Encrypted and 2) Dynamic Data Masking. Today I am going to walk you through the Always Encrypted feature.

First and foremost, we need to have SQL Server 2016 RC0 installed so that we can test this feature. You can download the RC0 here.  Once you are ready with RC0, create a test database and a table with an Encrypted column to store the sensitive data. There are few prerequisites that I will list for you here. If you want, you can use the sample schema from MS.

  1. Create a sample database
  2. Create Column Master Key
  3. Generate a self-signed certificate (well, you will need to install this certificate on the machine where the application will run)
  4. Configure Column Encryption Key
  5. Create a test table with Always Encrypted column
  6. Create an application to Insert data into the sample table we created in previous step

I have created a sample app and a demo script for the reference which you can download here. Basically, what we have to remember is that we can not insert the value inside the Always Encrypted table directly, we will need to use the tool/app, and the data will always be encrypted when it goes inside the database. This will ensure that the intruder can not get the data as it travels to the database in a cipher text form.

Here is some further reading on this topic. Enjoy reading and testing an excellent feature of SQL Server 2016 RC0.

Categories: DBA Blogs

SQL On The Edge #9 – Azure SQL Database Threat Detection

Pythian Group - Mon, 2016-04-11 15:30

Despite being well documented for several years now, every now and then we still run into clients that have bad experiences because of SQL injection attacks. If you’re not familiar, a SQL injection attack happens when an attacker exploits an application vulnerability in how they pass queries and data into the database and insert their own malicious SQL code to be executed. If you want to see different examples and get the full details, the Wikipedia page is very comprehensive.

Depending on how the application is configured, this kind of attack can go all the way from enabling attackers to see data they shouldn’t, to dropping an entire database if your application is allowed to do so. The fact that it’s an application based vulnerability also means that it really depends on proper coding and testing of all inputs in the application to prevent it. In other words, it can be very time-consuming to go back and plug all the holes if the application wasn’t securely built from the ground up.

Built-in Threat Detection

To attack this issue, and as part of the ongoing security story of SQL Server, Microsoft has now invested in the feature called Database Threat Detection. When enabled, the service will automatically scan the audit records generated from the database and will flag any anomalies that it detects. There are many patterns of injections so it makes sense to have a machine be the one reading all the SQL and flagging them. MS is not disclosing the patterns or the algorithms in an effort to make working around the detection more difficult.

What about on-premises?

This feature right now is only available on Azure SQL Db. However, we all know that Azure SQL Db is basically the testing grounds for all major new features coming to the box product. I would not be surprised if the threat detection eventually makes it to the on-premises product as well (my speculation though, nothing announced about this).

Pre-requisites
For this new feature you will need Azure SQL Db, you will also need to have auditing enabled on the database. The current way this works is by analyzing the audit records so it’s 100% reactive, nothing proactive. You will need a storage account as well since that’s where the audit logs get stored. The portal will walk you through this whole process, we’ll see that in the demo video.

Current State
As I mentioned, right now the tool is more of a reactive tool as it only lets you know after it has detected the anomaly. In the future, I would love to see a preventive configuration where one can specify a policy to completely prevent suspicious SQL from running. Sure, there can always be false alarms, however, if all the application query patterns are known, this number should be very low. If the database is open to ad-hoc querying then a policy could allow to only prevent the queries or even shut down the database after several different alerts have been generated. The more flexible the configuration, the better, but in the end what I want to see is a move from alerting me to preventing the injection to begin with.

In the demo, I’m going to go through enabling Azure SQL threat detection, some basic injection patterns and what the alerts look like. Let’s check it out!

 

Categories: DBA Blogs

Enter the Exadata X6

Pythian Group - Mon, 2016-04-11 15:20

Data is exploding and Exadata is catching up. With the proliferation of cloud technology and in-memory databases; Oracle Exadata X6-2 and X6-8 has it all. It seems to be an ideal platform for hyper-convergence for any data center running Oracle products.

Following are some of the salient features of Oracle X6:

  • The compute nodes have twenty two-core Intel Xeon E5-2699 v4 processors
  • The memory is DDR4 and of size 256Gb and it can be expanded to 768Gb.
  • The local storage can now be upgraded to 8 drives from default of 4.
  • The cell servers have ten-core Intel Xeon E5-2630 v4 processors.
  • Flashcache has become massive here reaching up to 12.8TB. Full rack has 179TB of flash.
  • There will be up to 1.7 PB of disk capacity (raw) per rack.
  • The infiniband network will have 40Gb/second. There is no change to Infiniband. In X5 it became active/active – but that’s the only difference.
  • On the software side, there are many improvement but one thing which caught my eye is the feature that enables Storage Indexes to be moved along the data when a disk hits predictive failure or true failure. This surely will improve performance by a long way.

With X6, Exadata has surely come a long way forward.

Categories: DBA Blogs

New A-Team Mobile Persistence Accelerator (AMPA) for Mobile Application Framework

The recent Oracle MAF 2.3 release already available on OTN, is a major update of MAF coming less than 6 months after the last major release. This release has several new & exciting features,...

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

FBDA -- 6 : Some Bug Notes

Hemant K Chitale - Sun, 2016-04-10 10:27
Some MoS documents on FBDA Bugs

1.  Bug 16454223  :  Wrong Results  (more rows than expected)

2.  Bug 16898135  :  FBDA does not split partitions  (resulting in rows not being purged)

3.  Bug 18294320  :   ORA-01555 (ORA-2475) on SMON_SCN_TIME

4.  Bug 22456983  :   Limit on SMON_SCN_TIME affecting FBDA

5.  Document 2039070.1 :  Known Issues with Flashback Data Archive
.
.
.




Categories: DBA Blogs

FBDA -- 5 : Testing AutoPurging

Hemant K Chitale - Sun, 2016-04-10 10:06
Tracking data changes after one row added (ID_COLUMN=2000) on 06-Apr

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-APR-16 10.53.20.328132 PM +08:00

SQL> select scn_to_timestamp(startscn), scn_to_timestamp(endscn), count(*)
2 from sys_fba_hist_93250
3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
4 order by 1,2;

SCN_TO_TIMESTAMP(STARTSCN)
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ENDSCN)
---------------------------------------------------------------------------
COUNT(*)
----------
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM
450

02-APR-16 11.32.55.000000000 PM
03-APR-16 11.45.24.000000000 PM
550

02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM
5

02-APR-16 11.46.11.000000000 PM
03-APR-16 11.45.24.000000000 PM
445

03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM
5

03-APR-16 11.45.24.000000000 PM
04-APR-16 11.05.33.000000000 PM
1000

06-APR-16 10.40.43.000000000 PM
06-APR-16 10.42.54.000000000 PM
1


7 rows selected.

SQL>
SQL> select count(*) from sys_fba_tcrv_93250;

COUNT(*)
----------
1002

SQL>


More changes on 07-Apr


SQL> insert into test_fbda
2 select 3000, to_char(3000), trunc(sysdate)
3 from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> update test_fbda
2 set date_inserted=date_inserted
3 where id_column=3000;

1 row updated.

SQL> delete test_fbda
2 where id_column < 1001 ;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> l
1 select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
2 from sys_fba_hist_93250
3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
4* order by 1,2,3
SQL> /

STARTTIME ENDTIME COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM 02-APR-16 11.46.11.000000000 PM 450
02-APR-16 11.32.55.000000000 PM 03-APR-16 11.45.24.000000000 PM 550
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.41.33.000000000 PM 5
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.45.24.000000000 PM 445
03-APR-16 11.41.33.000000000 PM 03-APR-16 11.45.24.000000000 PM 5
03-APR-16 11.45.24.000000000 PM 04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM 06-APR-16 10.42.54.000000000 PM 1
07-APR-16 10.27.35.000000000 PM 07-APR-16 10.28.03.000000000 PM 1
07-APR-16 10.28.03.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000

10 rows selected.

SQL>
SQL> l
1 select id_column, trunc(date_inserted), count(*)
2 from test_fbda
3 group by id_column, trunc(date_inserted)
4* order by 1
SQL> /

ID_COLUMN TRUNC(DAT COUNT(*)
---------- --------- ----------
2000 06-APR-16 1
3000 07-APR-16 1

SQL>


I see two new 1000 row sets (04-Apr and 07-Apr).  I should expect only one.

Now that rows for ID_COLUMN less than 1001 have been deleted on 07-Apr, we have to see if and when they get purged from the History table.


On 09-Apr:

SQL> insert into test_fbda
2 select 4000, to_char(4000),trunc(sysdate)
3 from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> update test_fbda
2 set date_inserted=date_inserted
3 where id_column=4000;

1 row updated.

SQL> commit;

Commit complete.

SQL> l
1 select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
2 from sys_fba_hist_93250
3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
4* order by 1,2,3
SQL> /

STARTTIME ENDTIME COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM 02-APR-16 11.46.11.000000000 PM 450
02-APR-16 11.32.55.000000000 PM 03-APR-16 11.45.24.000000000 PM 550
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.41.33.000000000 PM 5
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.45.24.000000000 PM 445
03-APR-16 11.41.33.000000000 PM 03-APR-16 11.45.24.000000000 PM 5
03-APR-16 11.45.24.000000000 PM 04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM 06-APR-16 10.42.54.000000000 PM 1
07-APR-16 10.27.35.000000000 PM 07-APR-16 10.28.03.000000000 PM 1
07-APR-16 10.28.03.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM 09-APR-16 11.10.48.000000000 PM 1

11 rows selected.

SQL>
SQL> select * from user_flashback_archive
2 /

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
1 3
02-APR-16 11.24.39.000000000 PM
02-APR-16 11.24.39.000000000 PM



SQL>


As on the morning of 10-Apr (after leaving the database instance running overnight) :

SQL> select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
2 from sys_fba_hist_93250
3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
4 order by 1,2,3
5 /

STARTTIME ENDTIME COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM 02-APR-16 11.46.11.000000000 PM 450
02-APR-16 11.32.55.000000000 PM 03-APR-16 11.45.24.000000000 PM 550
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.41.33.000000000 PM 5
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.45.24.000000000 PM 445
03-APR-16 11.41.33.000000000 PM 03-APR-16 11.45.24.000000000 PM 5
03-APR-16 11.45.24.000000000 PM 04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM 06-APR-16 10.42.54.000000000 PM 1
07-APR-16 10.27.35.000000000 PM 07-APR-16 10.28.03.000000000 PM 1
07-APR-16 10.28.03.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM 09-APR-16 11.10.48.000000000 PM 1

11 rows selected.

SQL> select systimestamp from dual
2 /

SYSTIMESTAMP
---------------------------------------------------------------------------
10-APR-16 08.51.29.398107 AM +08:00

SQL>
SQL> select * from user_flashback_archive
2 /

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
1 3
02-APR-16 11.24.39.000000000 PM
02-APR-16 11.24.39.000000000 PM



SQL>


So auto-purge of the data as of earlier days (02-Apr to 06-Apr) hasn't yet kicked in ?  Let's try a manual purge.

SQL> alter flashback archive fbda purge before timestamp (sysdate-4);

Flashback archive altered.

SQL> select * from user_flashback_archive;

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
1 3

05-APR-16 11.52.16.000000000 PM



SQL>
SQL> ! sleep 300
SQL> l
1 select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
2 from sys_fba_hist_93250
3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
4* order by 1,2,3
SQL> /

STARTTIME ENDTIME COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM 02-APR-16 11.46.11.000000000 PM 450
02-APR-16 11.32.55.000000000 PM 03-APR-16 11.45.24.000000000 PM 550
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.41.33.000000000 PM 5
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.45.24.000000000 PM 445
03-APR-16 11.41.33.000000000 PM 03-APR-16 11.45.24.000000000 PM 5
03-APR-16 11.45.24.000000000 PM 04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM 06-APR-16 10.42.54.000000000 PM 1
07-APR-16 10.27.35.000000000 PM 07-APR-16 10.28.03.000000000 PM 1
07-APR-16 10.28.03.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM 09-APR-16 11.10.48.000000000 PM 1

11 rows selected.

SQL>


Although USER_FLASHBACK_ARCHIVE shows that a purge till 05-Apr (the 11:52pm timestamp is strange) has been done, I still see older rows in the History table.  The query on the active table does correctly exclude the rows that should not be available. 


SQL> select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
2 from sys_fba_hist_93250
3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
4 order by 1,2,3;

STARTTIME ENDTIME COUNT(*)
---------------------------------- ---------------------------------- ----------
02-APR-16 11.32.55.000000000 PM 02-APR-16 11.46.11.000000000 PM 450
02-APR-16 11.32.55.000000000 PM 03-APR-16 11.45.24.000000000 PM 550
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.41.33.000000000 PM 5
02-APR-16 11.46.11.000000000 PM 03-APR-16 11.45.24.000000000 PM 445
03-APR-16 11.41.33.000000000 PM 03-APR-16 11.45.24.000000000 PM 5
03-APR-16 11.45.24.000000000 PM 04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM 06-APR-16 10.42.54.000000000 PM 1
07-APR-16 10.27.35.000000000 PM 07-APR-16 10.28.03.000000000 PM 1
07-APR-16 10.28.03.000000000 PM 07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM 09-APR-16 11.10.48.000000000 PM 1

11 rows selected.

SQL> select * from user_flashback_archive;

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME
------------------ ----------------- ---------------------------------------------------------------------------
LAST_PURGE_TIME STATUS
--------------------------------------------------------------------------- -------
SYSTEM
FBDA
1 3
05-APR-16 11.52.16.000000000 PM


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
10-APR-16 10.52.12.361412 PM +08:00

SQL> select count(*) from test_fbda as of timestamp (sysdate-3);

COUNT(*)
----------
2

SQL>
SQL> select partition_position, high_value
2 from user_tab_partitions
3 where table_name = 'SYS_FBA_HIST_93250'
4 order by 1;

PARTITION_POSITION HIGH_VALUE
------------------ --------------------------------------------------------------------------------
1 MAXVALUE

SQL>



Support Document 16898135.1 states that if Oracle isn't maintaining partitions for the History table, it may not be purging data properly.  Even an ALTER FLASHBACK ARCHIVE ... PURGE doesn't purge data (unless PURGE ALL is issued).  I'd seen this behaviour in 11.2.0.4 . The bug is supposed to have been fixed in 12.1.0.2  but my 12.1.0.2 environment shows the same behaviour.   The fact that my test database has very little activity (very few SCNs being incremented) shouldn't matter. The "Workaround" is, of course, unacceptable.
.
.
.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs