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
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.
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
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 →
Hacking Oracle Data Redaction
Last month Oracle ACE Director Oded Raz published an article about Data Redaction. This month, Oded will explain some of the vulnerabilities of data redaction and how to “hack it”. I would like to thank Oded for his contribution and invite him to publish more things in the future.
Zohar
On my last article, I have introduced you the new oracle security feature – Data Redaction, selective, on-the-fly redaction of sensitive data in SQL query results prior to display by applications so that unauthorized users cannot view the sensitive data. Although I welcome this feature and think it’s a much-needed addition to Oracle database security features it has some limitations that you need to be aware of before using it to protect sensitive data.
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.