DBA Blogs

The future of work

RDBMS Insight - Wed, 2019-10-02 10:48

The people behind the American tech industry blog TechDirt have put together an anthology of stories about the future of work called (appropriately) Working Futures, and I’m excited to have a story in it.

My story, “The Auditor and the Exorcist,” was heavily influenced by the years I spent working remotely for Oracle. It tells a near-future story of working in a world with a social credit system that depends on weak AI. Of course, poor security, hacking, and bugs are all present in the future, too…

Quick summary: Pat is stuck in a soul-deadening job as a social credit auditor. Her thoroughly modern home suddenly shows every sign of being haunted. Pat doesn’t believe in ghosts, but the only thing that seems to restore her home to normalcy is the help of an online exorcist. Is Pat’s house really haunted… or is something more sinister going on?

The book is now available in both ebook and paperback format at Amazon, and if you have a Kindle Unlimited subscription, you can read it for free. Plus, you can repost any of the stories in it (non-commercially): The stories in the anthology are all released under CC license. My story is released as CC-BY-NC-ND, meaning anyone’s free to recopy it non-commercially, but not to re-release it commercially or to remix without permission. to recopy it non-commercially, but not to re-release it commercially or to remix without permission.

Image credit: Remixed from a public domain image found on publicdomainimages.net. Feel free to reuse/remix further.

Categories: DBA Blogs

How to enlarge an #Exasol database by adding a node

The Oracle Instructor - Mon, 2019-09-30 08:46

Adding a cluster node will not only increase the available storage capacity but also the total compute power of your cluster. This scale-out is a quite common operation for Exasol customers to do.

My example shows how to change an existing 2+1 cluster into a 3+0 cluster. Before you can enlarge the database with an active node, this node has to be a reserve node first. See here how to add a reserve to a 2+0 cluster. Of course you can add another reserve node to change from 3+0 to 3+1 afterwards. See here if you wonder why you may want to have a reserve node at all.

Initial state – reserve node is present

I start with a 2+1 cluster – 2 active nodes and 1 reserve node:

For later comparison, let’s look at the distribution of rows of one of my tables:

The rows are roughly even distributed across the two active nodes.

Before you continue, it would be a good idea to take a backup on a remote archive volume now – just in case.

Shutdown database before volume modification

A data volume used used by a database cannot be modified while that database is up, so shut it down first:

After going to the Storage branch in EXAoperation, click on the data volume:

Then click on Edit:

Decrease volume redundancy to 1

Change the redundany from 2 to 1, then click Apply:

Why is the redundancy reduced from 2 to 1 here? Let’s try to explain that. Initially, I had 2 active nodes with a volume using redundancy 2:

A and B are master segments while A’ and B’ are mirrored segments. If I could add a node to this volume keeping the existing segments, it would look like this:

Of course this would be a bad idea. The redundancy is reduced to 1 before the new node is added to the volume:

Only distributed master segments with no mirrors at first. Then the redundancy is again increased to 2:

This way, every master segment can be mirrored on a neighbor node. That’s why the redundancy needs to be reduced to 1.

Add new node to volume

After having decreased the volume redundancy to 1, click Edit on the volume detail page again and add n13 as a new master node to the volume and click Apply:

Increase redundancy to 2

Now click Edit again and increase the redudancy to 2:

The state of the volume shows now as RECOVERING – don’t worry, it just means that mirrored segments are now created.

Enlarge the database

Now click on the database link on the EXASolution screen:

Select the Action Enlarge and click Submit:

Enter 1 and click Apply:

The database detail page looks like this now:

Reorganize

Technically, this is a 3+0 cluster now – but the third node doesn’t contain any data yet. If we look at the same table as before, we see that no rows are on the new node:

To change that, a REORGANIZE needs to be done. Either on the database layer, on schema layer or on table layer. Most easy to perform is REORGANIZE DATABASE:

Took me about 10 Minutes on my tiny database. That command re-distributes every table across all cluster nodes and can be time consuming with high data volume. While a table is reorganized, that table is locked against DML. You can monitor the ongoing reorganization by selecting from EXA_DBA_PROFILE_RUNNING in another session.

Final state

Let’s check the distribution of the previous table again:

As you can see above, now there are rows on the added node. Also EXAoperation confirms that the new node is not empty any more:

On a larger database, you would see that the volume usage of the nodes is less than before per node and every node is holding roughly the same amount of data. For failsafety, you could add another reserve node now.

Summary of steps
  1. Add a reserve node (if not yet existing)
  2. Take a backup on a remote archive volume
  3. Shutdown database
  4. Decrease volume redundancy to 1
  5. Add former reserve node as new master node to the volume
  6. Increase redundancy to 2
  7. Enlarge database by 1 active node
  8. Reorganize
  9. Add another reserve node (optionally)
Categories: DBA Blogs

Pivot with list of rows

Tom Kyte - Thu, 2019-09-26 06:46
We have a table which contains db_name and usernames. In the output we need list of users per DB i.e. number of columns will be equal to distinct db_name. sample output format: <b>DB1 DB2</b> USER1 USER4 USER2 USER5 USER3 Database version:...
Categories: DBA Blogs

confuse at the order of execution plan table

Tom Kyte - Thu, 2019-09-26 06:46
As we were told that "The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is executed first." howe...
Categories: DBA Blogs

Converting data types in where clause

Tom Kyte - Thu, 2019-09-26 06:46
Hi Tom, My question is regarding when a query is not having the right datatype in the where clause Example: -- Create table <code>CREATE TABLE mytable ( mynumber varchar2(20), primary key(mynumber));</code> -- Insert some rows <...
Categories: DBA Blogs

Resetting a live sequence

Tom Kyte - Thu, 2019-09-26 06:46
A sequence was about to finish, so I had make it bigger. I work on database 11.2 so I had to use a workaround described here (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597). The procedure shown in LiveSQL was ru...
Categories: DBA Blogs

Strange behavior in analytic functions with partitions

Tom Kyte - Thu, 2019-09-26 06:46
Hello, I have met strange behavior that I can't understand. I have this table <code>CREATE TABLE test_table (id NUMBER(10,0) NOT NULL, register_date DATE DEFAULT sysdate NOT NULL, row...
Categories: DBA Blogs

Issue in generating Custom Reference ID and Mapping with Form's field

Tom Kyte - Thu, 2019-09-26 06:46
Building an app for blocking a demo calendar for particular product setup. I wanted to create a custom reference ID ( CURRENT_MONTH-CURRENT_YEAR-SEQUENCE like SEPT-2019-003) which will be the Primary Key (Column BOOKING_REF) for the table (table ...
Categories: DBA Blogs

Inserting without a full list for field names

Tom Kyte - Thu, 2019-09-26 06:46
We have an issue when we perform an insert like this <code> INSERT INTO STS_RESP_LOG(STS_REQ_LOG_SYSTEM_ID,HSTRY_FLG, SNGL_STS, FRQNCY, CRT_DATE ) VALUES ( ?, ?, ?, ?, sysdate); </code> ?...
Categories: DBA Blogs

RMAN MAXPIECESIZE VS SECTION SIZE

Tom Kyte - Thu, 2019-09-26 06:46
HELLO , i've made some test to try to parallelizethe best the backup of bigdatabase and i wanted to know if it's possible to parallelize the backup of a backuppiece (multiple backupset) on mulitple channel with maxpiecesize because our SBT media is ...
Categories: DBA Blogs

Impact of Force Logging

Bobby Durrett's DBA Blog - Mon, 2019-09-23 17:29

I am working on upgrading an Oracle database from 11.2.0.4 to 19c and migrating it from HP Unix to Linux. This 15-terabyte database is too large to copy from the old to the new system during our normal weekend downtime window. It also has a ton of weekend batch updates that overlap the normal weekend change window so it would be best for our business processing if the cut over from the old to the new system was as fast as possible.

I want to use GoldenGate to minimize the downtime for the cutover using an approach similar to what is described in this Oracle document:

Zero Downtime Database Upgrade Using Oracle GoldenGate

You start GoldenGate collecting changes on the current production system and then take your time copying the 15 TB of data from the old to new system. Once you are done with the initial load you apply the changes that happened in the meanwhile. Finally, you cut over to the new system. You could even switch the direction of the replication to push changes on the new production system back to the old system to allow for a mid-week back out several days after your upgrade. Pretty cool. A teammate of mine successfully used this approach on an important database some years back.

But the database that I am working on now, unlike the one that my colleague worked on, has a lot of tables set to nologging. Under the right conditions inserts into tables set to nologging are not written to the redo logs and will be missed by GoldenGate. This Oracle article recommends setting your database to FORCE LOGGING so GoldenGate will not miss any updates:

In order to ensure that the required redo information is contained in the Oracle redo logs for segments being replicated, it is important to override any NOLOGGING operations which would prevent the required redo information from being generated. If you are replicating the entire database, enable database force logging mode.

Oracle GoldenGate Performance Best Practices

We could also switch all our application tables and partitions in the source system to logging but we have so many I think we would set the whole database to force logging.

But the big question which I touched on in my previous post is whether force logging will slow down our weekend batch processing so much that we miss our deadlines for weekend processing to complete and affect our business in a negative way. The more I investigate it the more convinced I am that force logging will have minimal impact on our weekend jobs. This is an unexpected and surprising result. I really thought that our batch processing relied heavily on nologging writes to get the performance they need. It makes me wonder why we are using nologging in the first place. It would be a lot better for backup and recovery to have all our inserts logged to the redo logs. Here is a nice Oracle Support document that lays out the pros and cons of using nologging:

The Gains and Pains of Nologging Operations (Doc ID 290161.1)

I have an entry in my notes for this upgrade project dated 8/26/19 in which I wrote “Surely force logging will bog the … DB down”. Now I think the opposite. So, what changed my mind? It started with the graph from the previous post:

Graph From Previous Post with Little Direct Writes I/O

I was really surprised that the purple line was so low compared to the other two. But I felt like I needed to dig deeper to make sure that I was not just misunderstanding these metrics. The last thing I want to do is make some production change that slows down our weekend processes that already struggle to meet their deadlines. I was not sure what other metrics to look at since I could not find something that directly measures non-logged writes. But then I got the idea of using ASH data.

In my “Fast way to copy data into a table” post I said that to copy data quickly between two Oracle tables “you want everything done nologging, in parallel, and using direct path”. I may have known then and forgotten but working on this now has me thinking about the relationship between these three ways of speeding up inserts into tables. I think there are the following two dependencies:

  • Nologging requires direct path
  • Parallel requires direct path

Oracle document “Check For Logging / Nologging On DB Object(s) (Doc ID 269274.1)” says the first one. In the second case if you have a target table set to parallel degree > 1 and you enable parallel DML you get direct path writes when you insert into the target table.

From all this I got the idea to look for direct path write waits in the ASH views. I could use ASH to identify insert statements that are using direct path. Then I could check that the target tables or partitions are set to nologging. Then I would know they are doing non-logged writes even if I did not have a metric that said so directly.

directwritesql.sql looked at all the SQL statements that had direct write waits over the entire 6 weeks of our AWR history. The output looks like this:

     select
  2  sql_id,count(*) active
  3  from DBA_HIST_ACTIVE_SESS_HISTORY a
  4  where
  5  event = 'direct path write'
  6  group by sql_id
  7  order by active desc;

SQL_ID            ACTIVE
------------- ----------
2pfzwmtj41guu         99
g11qm73a4w37k         88
6q4kuj30agxak         58
fjxzfp4yagm0w         53
bvtzn333rp97k         39
6as226jb93ggd         38
0nx4fsb5gcyzb         36
6gtnb9t0dfj4w         31
3gatgc878pqxh         31
cq433j04qgb18         25

These numbers startled me because they were so low. Each entry in DBA_HIST_ACTIVE_SESS_HISTORY represents 10 seconds of activity. So over 6 weeks our top direct path write waiter waited 990 seconds. Given that we have batch processes running full out for a couple of days every weekend 990 seconds over 6 weekends is nothing.

I took the top SQL ids and dumped out the SQL text to see what tables they were inserting into. Then I queried the LOGGING column of dba_tables and dba_tab_partitions to see which insert was going into a table or partition set to nologging.

select logging,table_name
from dba_tables
where owner='MYOWNER' and
table_name in
(
... tables inserted into ...
)
order by table_name;


select logging,table_name,count(*) cnt
from dba_tab_partitions
where table_owner='MYOWNER' and
table_name in
(
... tables inserted into ...
)
group by logging,table_name
order by table_name,cnt desc;

This simple check for LOGGING or NOLOGGING status eliminated several of the top direct path write waiters. This process reduced the list of SQL ids down to three top suspects:

SQL_ID            ACTIVE
------------- ----------
cq433j04qgb18         25
71sr61v1rmmqc         17
0u0drxbt5qtqk         11

These are all inserts that are not logged. Notice that the most active one has 250 seconds of direct path write waits over the past 6 weeks. Surely enabling force logging could not cause more than about that much additional run time over the same length of time.

I got the idea of seeing what percentage of the total ASH time was direct path write waits for each of these SQL statements. In every case it was small:

cq433j04qgb18

TOTAL_SAMPLE_COUNT DW_SAMPLE_COUNT DW_SAMPLE_PCT
------------------ --------------- -------------
              2508              25    .996810207
 
71sr61v1rmmqc

TOTAL_SAMPLE_COUNT DW_SAMPLE_COUNT DW_SAMPLE_PCT
------------------ --------------- -------------
              1817              17    .935608145

0u0drxbt5qtqk

TOTAL_SAMPLE_COUNT DW_SAMPLE_COUNT DW_SAMPLE_PCT
------------------ --------------- -------------
              8691              11    .126567714

TOTAL_SAMPLE_COUNT was all the samples for that SQL_ID value for the past 6 weeks. DW_SAMPLE_COUNT is the same count of samples that are direct write waits that we already talked about. DW_SAMPLE_PCT is the percentage of the total samples that were direct write wait events. They were all around 1% or lower which means that write I/O time was only about 1% of the entire run time of these inserts. The rest was query processing best I can tell.

Also I used my sqlstat3 script to look at the average run time for these inserts:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
71sr61v1rmmqc      3333358322 01-SEP-19 12.00.46.381 PM                1         2768879.28         892080    207085.624                  0                      0                  3.817             9028323            1045428               19337954
71sr61v1rmmqc      3333358322 08-SEP-19 10.00.43.551 AM                0         264428.594          98840     28257.339                  0                      0                  3.657              177736             143345                      0
71sr61v1rmmqc      3333358322 08-SEP-19 11.00.49.648 AM                1          2352509.9         767440    160933.191                  0                      0                      0             8729437             791837               19110340
71sr61v1rmmqc      3333358322 15-SEP-19 11.00.03.027 AM                1         3090070.21         904310    190593.062                  0                      0                  2.192             9095421             949579               19470026

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
0u0drxbt5qtqk       382840242 01-SEP-19 02.00.23.436 AM                1         29281391.6        3211050    16624311.7                  0                      0              99532.905            37076159           14440303               24479240
0u0drxbt5qtqk       382840242 08-SEP-19 02.00.11.424 AM                1         3871668.37         424670    2563007.61                  0                      0               1236.003             4622248            2457057                2468983
0u0drxbt5qtqk       382840242 15-SEP-19 03.00.12.349 AM                0         5161808.16         615520    3358994.55                  0                      0              20656.365             6251060            2801828                      0
0u0drxbt5qtqk       382840242 15-SEP-19 04.00.33.661 AM                1         2412910.02         240650    1741053.89                  0                      0                699.717             3050529            1542895                4638794

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
cq433j04qgb18      1267318024 02-SEP-19 10.00.57.871 PM                1          77132.892          51180     10719.692                  0                      0                  2.003              460346              47055                 772468
cq433j04qgb18      1267318024 03-SEP-19 10.00.55.601 PM                1         116064.154          68350      9808.483                  0                      0              15746.609              911571              20422                1256808
cq433j04qgb18      1267318024 04-SEP-19 10.00.31.071 PM                1         106594.074          64030      6328.462                  0                      0              15603.102              777779              14945                1561172
cq433j04qgb18      1267318024 05-SEP-19 10.00.13.265 PM                0          44435.247          31810      2760.438                  0                      0                365.132              139637               5111                 257770
cq433j04qgb18      1267318024 09-SEP-19 10.00.18.346 PM                1         791385.928         324050    171504.931                  0                      0               7484.358             6430665             600703               14262960
cq433j04qgb18      1267318024 10-SEP-19 10.00.29.224 PM                0         1685763.14         676210    304045.354                  0                      0                283.296            11884045             838290               16268667
cq433j04qgb18      1267318024 11-SEP-19 10.01.00.800 PM                0         369644.825         172120     42679.357                  0                      0                  3.929             2443772             151369                3901044
cq433j04qgb18      1267318024 12-SEP-19 10.00.28.499 PM                0          30381.614          25630      1191.884                  0                      0                 422.55               98580               3389                 184812
cq433j04qgb18      1267318024 13-SEP-19 10.00.07.502 PM                0         173286.567         109990     11461.865                  0                      0                 359.37             1475324              63073                2360818
cq433j04qgb18      1267318024 16-SEP-19 10.00.52.175 PM                1         190203.822          93680     47585.666                  0                      0                122.658             1221886             348327                2955258

These queries run at most a couple of hours. If direct path writes are 1% of their total run time, I estimated that force logging would add about 1% to the elapsed time or about 2 minutes per execution.

The final step was to try to run one of these top nologging I/O inserts in a test environment with and without force logging to see if the test matches the expected performance slowdown. I was not able to run 0u0drxbt5qtqk without setting up a more elaborate test with the development team. My test of cq433j04qgb18 ran considerably faster with force logging than without it so I think other factors were hiding whatever effect force logging had. But 71sr61v1rmmqc had some nice results that matched my estimates well. This is on a Delphix clone of production so the data was up to date with prod but the underlying I/O was slower.

71sr61v1rmmqc results running 5 times normal 5 times force logging

The individual run times are in seconds and the averages are listed in seconds and in minutes. I ran the insert 5 times with no force logging and 5 times with it alternating. I dropped the primary key and unique index of the target table to keep from getting constraint errors. I rolled back the insert each time. It averaged about 1.2 minutes more out of 40 minutes of run time which is about a 3% increase. My estimate from ASH was about 1% so this test matches that well.

The final test remains. In some upcoming production weekend, I will put in a change to flip the database to force logging and see how it goes. My tests were run on a test system with a different storage system and with no other activity. We might see different results on a heavily loaded system with a queue for the CPU. But, after all this analysis and testing I feel confident that we won’t be able to tell that force logging is enabled. Unfortunately, we sometimes have performance issues anyway due to plan changes or data volume so the force logging switch might get blamed. But I feel confident enough to push for the final test and I think we ultimately will pass that test and be able to use force logging to enable GoldenGate to support a short cut over time for our migration and upgrade project.

Bobby

P.S. A good question came in as a comment about direct path write waits and asynchronous I/O. The system I am testing on does not support async I/O because it is HP Unix and a filesystem. This older blog post talks a bit about async and direct I/O on HP-UX:

https://www.bobbydurrettdba.com/2013/04/26/db_writer_processes-dbwr_io_slaves-with-no-asynch-io-on-hp-ux/

So, your mileage may vary (YMMV) if you do these same queries on a system with asynchronous writes. Linux filesystems support async writes and on HP-UX our RAC system on ASM supports it. It is one of the challenges of writing blog posts. Other people may be in different situations than I am.

Categories: DBA Blogs

One More Thing: New Oracle Cloud free tier better than AWS free tier

Iggy Fernandez - Mon, 2019-09-16 19:20
Larry Ellison just concluded his Oracle OpenWorld keynote with the announcement of an Oracle Cloud free tier that is better than the AWS free tier. The Oracle Cloud free tier never expires and includes the crown jewels. The slides say it all.
Categories: DBA Blogs

Presenting at UKOUG Techfest19 Conference in Brighton, UK

Richard Foote - Thu, 2019-09-12 19:07
I’m very excited to be attending my 3rd UKOUG Conference, this year re-badged as Techfest19. The fact it’s being held in Brighton is a little disconcerting for a Crystal Palace fan, but really looking forward nonetheless to what has always been one of the very best Oracle conferences on the yearly calendar. I have a […]
Categories: DBA Blogs

Estimating how much write I/O is not logged

Bobby Durrett's DBA Blog - Thu, 2019-09-12 11:30

I am trying to figure out how much non-logged write I/O an Oracle database is doing. I want to run an ALTER DATABASE FORCE LOGGING command on the database so that I can use Oracle GoldenGate(GGS) which reads updates from Oracle’s logs. GGS will miss writes that are not logged. But if I turn on force logging it may slow down applications that depend on non-logged writes for good performance. So, I want to find some Oracle performance metrics that give me an idea about how much non-logged write I/O we have so I have an estimate of how much force logging will degrade performance.

I created SQL*Plus and PythonDBAGraphs reports based on DBA_HIST_IOSTAT_FUNCTION that gives some insight into the write I/O that is not logged. Here is the Python based graphical version of the report for one recent weekend:

Possible NOLOGGING Write I/O

The purple-blue line represents Direct Writes. These may or may not be logged. The red-orange line represents writes through the DBWR process. These are always logged. The light green line represents log I/O through the LGWR process. My theory is that if the purple line is above the green line the difference must be write I/O that is not logged. But if the green line is equal to or greater than the purple line you really do not know if there was any write I/O that was not logged. But if there is non-logged write I/O it cannot be more than the amount indicated by the purple line. So, this graph does not directly answer my question about how much write I/O was not logged but it does show some numbers that relate to the question.

I did some experiments with the V$IOSTAT_FUNCTION view that populates DBA_HIST_IOSTAT_FUNCTION to see what values it gives for Direct Writes, DBWR, and LGWR using different scenarios. Here is the zip of these scripts and their output: nologgingscriptsandlogs09122018.zip. I tested four scenarios:

  1. Insert append nologging
  2. Insert append logging
  3. Insert noappend logging
  4. Insert noappend nologging

1 and 2 did Direct Writes. 3 and 4 did DBWR writes. 2, 3, and 4 all did LGWR writes.

Here are the relevant sections of the output that correspond to these statements.

Insert append nologging:

FUNCTION_NAME      WRITE_DIFF_MEGABYTES
------------------ --------------------
Direct Writes                      4660
LGWR                                 46
DBWR                                 27

Insert append logging:

FUNCTION_NAME      WRITE_DIFF_MEGABYTES
------------------ --------------------
LGWR                               4789
Direct Writes                      4661
DBWR                                 37

Insert noappend logging:

FUNCTION_NAME      WRITE_DIFF_MEGABYTES
------------------ --------------------
DBWR                               6192
LGWR                               4528
Direct Writes                         2

Insert noappend nologging:

FUNCTION_NAME      WRITE_DIFF_MEGABYTES
------------------ --------------------
DBWR                               6213
LGWR                               4524
Direct Writes                         2

This pattern is similar to that in a Ask Tom post that I wrote about a while back. That post showed the different situations in which writes were logged or not. I also got some ideas about direct writes and logging from this Oracle support document:

Check For Logging / Nologging On DB Object(s) (Doc ID 269274.1)

It sounds like inserts into tables that go through the normal processing eventually get written to disk by DBWR but inserts with the append hint write directly to the datafiles and may or may not be logged and written out by LGWR.

These tests and documents gave me the idea of building a report and graph based on DBA_HIST_IOSTAT_FUNCTION showing the values for the Direct Writes, DBWR, and LGWR FUNCTION_NAME values. The graph above shows an example of a real system. I was surprised to see how high the DBWR and LGWR values were and how low the Direct Writes were. That made me think that it would be safe to try turning on FORCE LOGGING because it likely will have minimal impact on the overall weekend processing. It gave me enough evidence to push for approval to do a controlled test of enabling FORCE LOGGING in production over an upcoming weekend. I will update this post with the results if we move forward with the test.

Bobby

Categories: DBA Blogs

Announcement: Australia/NZ “Let’s Talk Database” Events October 2019 !!

Richard Foote - Wed, 2019-09-11 22:49
I’ve very excited to announce the next series of Oracle “Let’s Talk Database” events to be run throughout Australia and New Zealand in October 2019. I’ll be discussing two exciting topics this series, “Oracle Database 19c New Features” and “Oracle Exadata X8“. As always, these sessions run between 9am-1pm, include a networking lunch and are free, […]
Categories: DBA Blogs

Oracle Database 19c Automatic Indexing: Default Index Column Order Part II (Future Legend)

Richard Foote - Tue, 2019-09-10 20:59
In Part I, we explored some options that Oracle might adopt when ordering the columns within an Automatic Index by default, in the absence of other factors where there is only the one SQL statement to be concerned with. A point worth making is that if all columns of an index are specified within SQL […]
Categories: DBA Blogs

Jami (Gnu Ring) review

RDBMS Insight - Tue, 2019-09-10 15:35

An unavoidable fact of database support life is webconferences with clients or users. Most of the time, we’re more interested in what’s going on onscreen than in each others’ faces. But every now and then we need to have a face-to-face. Skype is popular, but I recently had the chance to try out a FOSS alternative with better security: Jami.

Jami (formerly Gnu Ring) is a FOSS alternative to Skype that advertises a great featureset and some terrific privacy features. I suggested to a small group that we try it out for an upcoming conference call.

Just going by its specs, Jami (https://jami.net/) looks amazing. It’s free, open-source software that’s available on all the major platforms, including all the major Linux distros. It boasts the following advantages over Skype and many other Skype alternatives:

  • Distributed: Uniquely, there aren’t any central servers. Jami uses distributed hash table technology to distribute directory functions, authentication, and encryption across all devices connected to it.
  • Secure: All communications are end-to-end encrypted.
  • FOSS: Jami’s licensed under a GPLv3+ license, is a GNU package and a project of the Free Software Foundation.
  • Ad-free: If you’re not paying for commercial software, then you are the product. Not so with Jami, which is non-commercial and ad-free. Jami is developed and maintained by Savoir Faire Linux, a Canadian open-source consulting company.

And its listed features include pretty much everything you’d use Skype for: text messaging, voice calls, video calls, file and photo sharing, even video conference calls.

I wanted to use it for a video conference call, and my group was willing to give it a try. I had high hopes for this FOSS Skype alternative.

Installation

Jami is available for: Windows, Linux, OS X, iOS, Android, and Android TV. (Not all clients support all features; there’s a chart in the wiki.) I tried the OS X and iOS variants.

First, I installed Jami on OS X and set it up. The setup was straightforward, although I had to restart Jami after setting up my account, in order for it to find that account.

Adding contacts

One particularly cool feature of Jami is that your contact profile is stored locally, not centrally. Your profile’s unique identifier is a cumbersomely long 40-digit hexadecimal string, such as “7a639b090e1ab9b9b54df02af076a23807da7299” (not an actual Jami account afaik). According to the documentation, you can also register a username for your account, such as “natalkaroshak”.

Contacts are listed as hex strings.Unfortunately, I wasn’t able to actually find any of my group using their registered usernames, nor were they able to find me under my username. We had to send each other 40-digit hex strings, and search for the exact hex strings in Jami, in order to find each other.

The only way to add a contact, once you’ve located them, is to interact with them, eg. by sending a text or making a call. This was mildly annoying when trying to set up my contact list a day ahead of the conference call.

Once I’d added the contacts, some of them showed up in my contact list with their profile names… and some of them didn’t, leaving me guessing which hex string corresponded to which member of my group.

Sending messages, texts, emojis

Sending and receiving Skype-style messages and emojis worked very well in Jami. Group chat isn’t available.

Making and taking calls

The documented process for a conference call in Jami is pretty simple: call one person,

Only the Linux and Windows versions currently support making conference calls. Another member of our group tried to make the conference call. As soon as I answered his incoming call, my Jami client crashed. So I wasn’t able to actually receive a call using Jami for OS X.

The caller and one participant were able to hear each other briefly, before the caller’s Jami crashed as well.

Linking another device to the same account

I then tried installing Jami on my iPhone. Again, the installation went smoothly, and this let me try another very cool feature of Jami.

In Jami, your account information is all stored in a folder on your device. There’s no central storage. Password creation is optional, because you don’t log in to any server when you join Jami. If you do create a password, you can (1) register a username with the account and (2) use the same account on another device.

The process of linking my iPhone’s Jami to the same account I used with my OSX Jami was very smooth. In the OSX install, I generated an alphanumeric PIN, entered the PIN into my device, and entered the account password. I may have mis-entered the first alphanumeric PIN, because it worked on the second try.

Unfortunately, my contacts from the OSX install didn’t appear in the iOS install, even though they were linked to the same account. I had to re-enter the 40-digit hex strings and send a message to each conference call participant.

Making calls on iOS

The iOS client doesn’t support group calling, but I tried video calling one person. We successfully connected. However, that’s where the success ended. I could see the person I called, but was unable to hear her. And she couldn’t see OR hear me. After a few minutes, the video of the other party froze up too.

Conclusion

Jami looked very promising, but didn’t actually work.

All of the non-call stuff worked: installation, account creation, adding contacts (though having to use the 40-digit hex codes is a big drawback), linking my account to another device.

But no one in my group was able to successfully make a video call that lasted longer than a few seconds. The best result was that two people could hear each other for a couple of seconds.

Jami currently has 4.5/5 stars on alternativeto.net. I have to speculate that most of the reviews are from Linux users, and that the technology is more mature on Linux. For OSX and iOS, Jami’s not a usable alternative to Skype yet.

Big thanks to my writing group for gamely trying Jami with me!

Categories: DBA Blogs

Oracle GoldenGate Microservices Upgrade – 12.3.0.x/18.1.0.x to 19.1.0.0.x

DBASolved - Sun, 2019-09-08 16:45

Oracle GoldenGate Microservices have been out for a few years now. Many customers have pursued the architecture in many different industries and have this in many dfifernt use-cases and architectures. But what do you do when you want to upgrade your Oracle GoldenGate Microservices Architecture?

In a previous post, I wrote about how to upgrade Oracle GoldenGate Microservices using the GUI or HTML5 approach in this post – Upgrading GoldenGate Microservices Architecture – GUI Based (January 2018). Today, many of the steps are exactly the same as they were a year ago. The good news is that Oracle has documented the process a bit clearer in the lates upgrade document (here).

So why a new post on upgrading the architecture? Over the last few days, I’ve been looking into a problem that has been reported by customers. This problem affects the upgrade process, not so much in how to do the upgrade but when the upgrade is done.

In nutshell, the upgrade process for Oracle GoldenGate Microservices is done in these few steps:

1. Download the latest version of Oracle GoldenGate Microservices -> In this case: 19.1.0.0.1 (here); however, this approach will work with 19.1.0.0.2 as well.
2. Upload the software, if needed, to a staging area on the server where Oracle GoldenGate Microservices is running. Ideally, you should be upgrading from OGG 12c (12.3.x) or 18c (18.1.x).
3. Unzip the downloaded zip file to a temporary folder in the staging area
4. Execute runInstaller from the directory in the staging area. This will start the Oracle Universal Installer for Oracle GoldenGate.
5. Within the installation process, provide the Oracle GoldenGate Home for the Software Location.
6. Click Install to begin the installation into a New Oracle GoldenGate Home.

Note: At this point, you should have two Oracle GoldenGate Microservices Homes. One for the older version and one for the 19c version.

7. Login to the ServiceManager
8. Under Deployments -> select ServiceManager
9. Under Deployment Details -> select the pencil icon. This will open the edit field for the GoldenGate Home.
10. Edit the GoldenGate Home -> change to the new Oracle GoldenGate Microservices Home then click Apply.
This will force the ServiceManager to reboot.

At this point, you may be asking yourself, I’ve done everything but the ServiceManager has not come back up. What is going on?

If you have configured the ServiceManager as a daemon, you can try to start the ServiceManager by using the systemctl commands.

systemctl start OracleGoldenGate

 

This command will just return with nothing important. In order to find out if it start successfully or not, check the status of the service.

systemctl status OracleGoldenGate
OracleGoldenGate.service - Oracle GoldenGate Service Manager
   Loaded: loaded (/etc/systemd/system/OracleGoldenGate.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Sun 2019-09-08 21:27:59 UTC; 2s ago
  Process: 3430 ExecStart=/opt/app/oracle/product/12.3.0/oggcore_1/bin/ServiceManager (code=killed, signal=SEGV)
 Main PID: 3430 (code=killed, signal=SEGV)


Sep 08 21:27:59 OGG12c219cUpgrade systemd[1]: Unit OracleGoldenGate.service entered failed state.
Sep 08 21:27:59 OGG12c219cUpgrade systemd[1]: OracleGoldenGate.service failed.
Sep 08 21:27:59 OGG12c219cUpgrade systemd[1]: OracleGoldenGate.service holdoff time over, scheduling restart.
Sep 08 21:27:59 OGG12c219cUpgrade systemd[1]: Stopped Oracle GoldenGate Service Manager.
Sep 08 21:27:59 OGG12c219cUpgrade systemd[1]: start request repeated too quickly for OracleGoldenGate.service
Sep 08 21:27:59 OGG12c219cUpgrade systemd[1]: <strong>Failed to start Oracle GoldenGate Service Manage</strong>r.
Sep 08 21:27:59 OGG12c219cUpgrade systemd[1]: Unit OracleGoldenGate.service entered failed state.
Sep 08 21:27:59 OGG12c219cUpgrade systemd[1]: OracleGoldenGate.service failed.

 

As you can tell the ServiceManager has failed to start. Why is this?

If you look at the output of the last systemctl status command, you see that the service is still referencing the old Oracle GoldenGate Microservices home.

Now the question becomes, how to I fix this?

The solution here is simple. Go to the deployment home for the ServiceManager and look under the bin directory. You will see teh registerServiceManager.sh script. Edit this script and change the variable OGG_HOME to match the new Oracle GoldenGate Home for 19c.

$ cd /opt/app/oracle/gg_deployments/ServiceManager/bin
$ ls
registerServiceManager.sh
$ vi registerServiceManager.sh


#!/bin/bash

# Check if this script is being run as root user
if [[ $EUID -ne 0 ]]; then
  echo "Error: This script must be run as root."
  exit
fi


# OGG Software Home location
OGG_HOME="/opt/app/oracle/product/12.3.0/oggcore_1” <— Change to reflect new OGG_HOME

Wit the registerServiceManager.sh file edit, go back and re-run the file as the root user.

# cd /opt/app/oracle/gg_deployments/ServiceManager/bin
# ./registerServiceManager.sh
Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
----------------------------------------------------
     Oracle GoldenGate Install As Service Script
----------------------------------------------------
OGG_HOME=/opt/app/oracle/product/19.1.0/oggcore_1
OGG_CONF_HOME=/opt/app/oracle/gg_deployments/ServiceManager/etc/conf
OGG_VAR_HOME=/opt/app/oracle/gg_deployments/ServiceManager/var
OGG_USER=oracle
Running OracleGoldenGateInstall.sh…

With the service now updated, you can start and check the service.

# systemctl start OracleGoldenGate
# systemctl status OracleGoldenGate
OracleGoldenGate.service - Oracle GoldenGate Service Manager
   Loaded: loaded (/etc/systemd/system/OracleGoldenGate.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2019-09-08 21:39:58 UTC; 2s ago
 Main PID: 21946 (ServiceManager)
    Tasks: 13
   CGroup: /system.slice/OracleGoldenGate.service
           └─21946 /opt/app/oracle/product/19.1.0/oggcore_1/bin/ServiceManager

Sep 08 21:39:58 OGG12c219cUpgrade systemd[1]: Started Oracle GoldenGate Service Manager.
Sep 08 21:39:58 OGG12c219cUpgrade ServiceManager[21946]: 2019-09-08T21:39:58.509+0000 INFO | Configuring user authorization secure store path as '/opt/app/oracle/gg_deployments/Serv...ureStore/'.
Sep 08 21:39:58 OGG12c219cUpgrade ServiceManager[21946]: 2019-09-08T21:39:58.510+0000 INFO | Configuring user authorization as ENABLED.
Sep 08 21:39:58 OGG12c219cUpgrade ServiceManager[21946]: Oracle GoldenGate Service Manager for Oracle
Sep 08 21:39:58 OGG12c219cUpgrade ServiceManager[21946]: Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447
Sep 08 21:39:58 OGG12c219cUpgrade ServiceManager[21946]: Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
Sep 08 21:39:58 OGG12c219cUpgrade ServiceManager[21946]: Linux, x64, 64bit (optimized) on May  8 2019 18:17:50
Sep 08 21:39:58 OGG12c219cUpgrade ServiceManager[21946]: Operating system character set identified as UTF-8.
Hint: Some lines were ellipsized, use -l to show in full.


At this point, you can now log back into the ServiceManager and confirm that the upgrade was done successfully.

Note: If you have your ServiceManager configured to be manually started and stopped, then you will need to edit the startSM.sh and stopSM.sh file. The OGG_HOME has to be changed in these files as well.

Enjoy!!!

Categories: DBA Blogs

September 27 Arizona Oracle User Group Meeting

Bobby Durrett's DBA Blog - Wed, 2019-09-04 10:30

The Arizona Oracle User Group (AZORA) is cranking up its meeting schedule again now that the blazing hot summer is starting to come to an end. Our next meeting is Friday, September 27, 2019 from 12:00 PM to 4:00 PM MST.

Here is the Meetup link: Meetup

Thank you to Republic Services for allowing us to meet in their fantastic training rooms.

Thanks also to OneNeck IT Solutions for sponsoring our lunch.

OneNeck’s Biju Thomas will speak about three highly relevant topics:

  • Oracle’s Autonomous Database — “What’s the Admin Role?”
  • Oracle Open World #OOW 19 Recap
  • Let’s Talk AI, ML, and DL

I am looking forward to learning something new about these areas of technology. We work in a constantly evolving IT landscape so learning about the latest trends can only help us in our careers. Plus, it should be interesting and fun.

I hope to see you there.

Bobby

Categories: DBA Blogs

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars !!

Richard Foote - Tue, 2019-09-03 06:44
It’s with great excitement that I announce I’ll finally be returning to London, UK in March 2020 to run both of my highly acclaimed seminars. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and […]
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs