Entries by Zohar Elkayam

,

SQL Riddle: Find the Sum of ASCII Codes of Employee Names (solution)

Zahar Hilkevich (from the blog – https://sqlpatterns.wordpress.com – cool blog, you should check it out) posted a riddle on Facebook.
The question was:

“For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.”

EMPNO ENAME       SUM_ASCII
----- ---------- ----------
 7788 SCOTT             397
 7876 ADAMS             358
 7566 JONES             383
 7499 ALLEN             364
 7521 WARD              302
 7934 MILLER            453
 7902 FORD              299
 7369 SMITH             389
 7844 TURNER            480
 7698 BLAKE             351
 7782 CLARK             365
 7654 MARTIN            459
 7839 KING              297
 7900 JAMES             368

Read more →

/1 Comment/by

My Upcoming Speaker Events

This is a heads up for some upcoming events I will be appearing on in the next few weeks. I was also accepted (as a standby session) to UKOUG Scotland (which I was at, last year) but unfortunately I will not be able to attend as it collides with some of my other speaking events.

Read more →

/0 Comments/by

RMAN no files found to be unknown to the database error

Here’s a weird error message for my collection…

I’ve been working with a customer to setup a RMAN backup on his standby database (backup is going to SBT). After a while, we decided that we couldn’t avoid using the RMAN catalog so we created one.
Now, when using the catalog, we can backup the database from either the primary or standby instances and that will register to a shared catalog so we could restore from either database.

RMAN> LIST DB_UNIQUE_NAME OF DATABASE;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
1       ORCL     1415951511       PRIMARY          ORCL
1       ORCL     1415951511       STANDBY          ORCLDG

The thing is, that if we change the DB_UNIQUE_NAME of the backup pieces (using RMAN’s CHANGE command), they will now be “owned” by the wrong database server and the files will not be available when running CROSSCHECK command.

RMAN> CHANGE BACKUP FOR DB_UNIQUE_NAME orcldg RESET DB_UNIQUE_NAME TO orcl;

change backup piece db_unique_name
backup piece handle=/data/fast_recovery_area/ORCLDG/backupset/2016_05_01/o1_mf_nnndf_TAG20160501T174121_cld5dkrj_.bkp RECID=15 STAMP=910719681
change backup piece db_unique_name
backup piece handle=/data/fast_recovery_area/ORCLDG/autobackup/2016_05_01/o1_mf_s_910715737_cld5dm03_.bkp RECID=16 STAMP=910719682
Changed 2 objects db_unique_name

Read more →

/0 Comments/by

Automatic DB Startup: The Linux Part (OEL 6 and 7)

This is part 2 of the automatic startup article. In the previous part, we talked about the basic building blocks for the automatic Oracle database and listener startup. We talked about the orastart and orashut scripts and the /etc/oratab that control which instances are automatically started.

In this part, we will put everything together and see how to configure Linux to use the scripts for automatic start. I will demonstrate two version of Linux here. I used Oracle Enterprise Linux 6 and 7 – which are similar to RedHat Enterprise Linux 6 and 7 (but free). These startup procedures are similar to most of the other distributions including CentOS and Ubuntu.
Read more →

/11 Comments/by

Automatic Startup for Oracle on Linux

A few years back, I worked with a junior DBA who was asked to create a new instance on a brand new machine. He created the instance using DBCA and everything went smoothly – or so he thought. A few weeks later, after the system became production, a planned maintenance rebooted that Linux server, and once the machine came back up – the database and listener processes were nowhere to be found.

So that junior DBA, stressed because he thought the database was lost called me in the middle of the night. My first sleepy question for him was “did you even start the database?” and his panicked reply was “of course not – it should be done automatically, I used DBCA!”…

Well, one of the things new DBA’s find hard to realize is that after installing and creating a database (even if we’re using DBCA), it will not start automatically. I’ve seen too many new DBA’s being puzzled by the logic behind automatic startup of Oracle databases on Linux (and UNIX in general) than I care to admit so I decided to put it here for their reference…

In this article I will describe the building blocks for automatic startup, and in my next post I will show the Linux commands needed to put everything together.

Read more →

/1 Comment/by

Fine-Grained Audit and ORA-28134 Error

I have been working on a customer database hardening. Database security isn’t really my cup of tea but it seems to be very popular these days. One of the requirement from the customer was the ability to monitor the database operation (which is very easy with the simple Oracle Audit features).

After we did that, the customer needed another auditing. He has sensitive data in one of the tables and he wanted some kind of auditing of the table access. This is one of the more central of his application so he asked the application should be excluded from the monitoring.

My first thought was to use the simple auditing for that but a short research showed that once an object is being monitored, there is no exclude feature to it. Another solution that came to mind is to use the ancient Fine-grained audit (DBMS_FGA). I know I’m coming a bit late to the party, and I know Oracle 12c has new and exciting ways of auditing but the customer was using Oracle 11.2.0.3 and is not planning on upgrading soon.

Read more →

/0 Comments/by