Hot Topics Of My Oracle Diary

Hot Topics Of My Oracle Diary - 2

Restricting “root” access to database

Today’s first topic is about access restriction. Bazlur asked on OTN forum, if he can restrict root users’ access to database. He’s the vendor (owner of the database) who doesn’t want the customer (owner of the os) to connect his database. (It seems familiar to me:))

The result comes with Oracle Database Vault with no other option.

Restore Only One Tablespace From a Cold Backup to Another Database?

This topic is about partial restore of a database backup. It’s not possible to restore “just” 1 tablespace from a full RMAN backup, but if you have time and space issues and you need just 1 tablespace, you can restore just SYSTEM, SYSAUX, UNDO + a particular TS. Metalink doc 102786.1 (Partial Restore Of A Database) describes the steps clearly for this case. The “purpose” part of this document also describes a possible scenario:

“The purpose of this article is to show how to partially restore a database backup. When you've accidentally dropped some objects or deleted rows from a table you can restore a backup of that database as a copy. Using the archivelog files you can rollforward until a time just before the drop or delete. After the rollforward you can export the data from that copy of the database. That export can be used to import the data into the live database. When the database is very large and you're only after a few objects it might not make sense to restore the full backup.”

Data vs. Information

Here comes an an “academical” discussion about the difference between data and information. I’ve gone through this a year ago in my post graduate class. It’s just a theoretical concept but i think it’s important to know the difference and use these words in the correct way.

What they said:

“Data is what you find in one particular column. What you call "record" (which Chris Date would call "tuple") IS information, because in one row of a table you create information by relating data (that's why it's called "relational"). When in table EMP you say that King is President and earns a miserable 5000 (not even 7 times more than the lowest salary - this guy should be locked up), that's information. King, President and 5000 mean nothing by themselves and are just data. Data isn't information, but two related pieces of data are.”

“In the IT field when we talk about data we generally mean what in common usage might be better understood as raw data. Some data might convey information, but often the data needs to be organized and filtered before it can be considered useful information. Placing raw data into a properly constructed set of tables would help identify information. Joins and the application of business logic to the table columns would result in more information being available.”

Oracle to SQL Server

This discussion is about connection from Oracle to SQL server on hp-ux box.

Dick Goulet describes two method.

“Method 1 would be to acquire a copy of the Transparent Gateway to SQL*Server from Oracle. It installs on the sql server box and then acts like a remote database. Very easy, but potentially expensive as we're talking about software form Oracle, although some bird recently told me that Oracle is "giving" this software away. If you can believe that!!

Method 2 is to configure Heterogeneous services, basically ODBC, between Oracle and Sql*Server. If you’re running Oracle in Windows that might be easy, but if you’re on Unix, well lots of luck as I have not managed to get it working yet (which is where the expense is, namely your time chasing it).

And Amar Kumar fills the gap of running ODBC on Unix:

“I recently finalized a solution using hsodbc+unixodbc+freetds (ora 9i on solaris 9/10) . Took me some time to figure out but once set it is quite stable. I will put a note on this on my site soon.”

We’ll be waiting for his post..

Why extra standby redo log group?

And my favorite comes. Yong Huang asks this question and never accepts any suggestion without a proof:) I remember me asking this question to myself when i first met dataguard and reading its documentation. But it was just seconds that i couldn’t find a feasible answer and didn’t need to investigate.

At the end Yong Huang brings the discussion to valuable point by himself. He’re is his last post:

“I have some interesting finding. I opened an SR. Oracle support immediately called back and told me that the recommendation for an extra SRL group is for maximum protection (even though documentation doesn't say so). Fine. But he went on and said even if we have >2 groups on primary, only 2 SRL groups on standby will be used. Skeptical about that, I added one more group to primary so it has 3 now, and added one more SRL group to standby so it has 4 now. I opened 3 sqlplus sessions doing massive delete and rollback in a tight loop on 3 different tables. I'm very excited to find that SRL status check once every one to two seconds does occasionally show more than 3 groups active. In fact, in my test, of all 112 samplings, the first group becomes active with a probability of 82/(40+82)=67%, the second group 64/(58+64)=52%, the third 14/(108+14)=11%, and the fourth, i.e. the extra, group 4/(118+4)=3%. StandbyRedoLogStatus.txt, which is summarized below, records the repeated queries of v$standby_log.

C:\>grep "^ 4" StandbyRedoLogStatus.txt | grep -c UNASSIGNED
40
C:\>grep "^ 4" StandbyRedoLogStatus.txt | grep -c ACTIVE
82
C:\>grep "^ 5" StandbyRedoLogStatus.txt | grep -c UNASSIGNED
58
C:\>grep "^ 5" StandbyRedoLogStatus.txt | grep -c ACTIVE
64
C:\>grep "^ 6" StandbyRedoLogStatus.txt | grep -c UNASSIGNED
108
C:\>grep "^ 6" StandbyRedoLogStatus.txt | grep -c ACTIVE
14
C:\>grep "^ 7" StandbyRedoLogStatus.txt | grep -c UNASSIGNED
118
C:\>grep "^ 7" StandbyRedoLogStatus.txt | grep -c ACTIVE
4

The extra group being ACTIVE is such a rare event that I want to share my joy with you:

SQL> /
GROUP# THREAD# SEQUENCE# USED ARC STATUS FIRST_CHANGE# FIRST_TIME
4 1 682 10130432 NO ACTIVE 954336 20090410 11:21:49
5 1 684 512 YES ACTIVE 954466 20090410 11:21:50
6 1 683 10350592 NO ACTIVE 954347 20090410 11:21:50
7 1 685 0 YES ACTIVE 954756 20090410 11:21:52

That particular sampling is so rare (and precious!) in that all 4 groups are ACTIVE at the same time. By the way, I find that if the file system on the standby server where archive logs are stored becomes full, the all SRL showing ACTIVE phenomenon will also show up and persist. But in the above case, that was not due to space full condition and only appeared in one sampling.

The above test is done with recovery still going. Actually, recovery or not doesn't make difference. I guess my earlier attempt to see the extra SRL active failed is because I still didn't have enough redo per second. I can't think of any other explanation.”