I just came across this really nice new feature in 12.2 (and looked back to find it was in 12.1). If you work with the data dictionary and with the *_CONSTRAINTS table, feast your eyes on this description:
SQL> desc dba_constraints
Name Null? Type
------------------------------------------------
OWNER VARCHAR2(128)
CONSTRAINT_NAME NOT NULL VARCHAR2(128)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(128)
SEARCH_CONDITION LONG
SEARCH_CONDITION_VC VARCHAR2(4000) R_OWNER VARCHAR2(128)
R_CONSTRAINT_NAME VARCHAR2(128)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(128)
INDEX_NAME VARCHAR2(128)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
ORIGIN_CON_ID NUMBER
Of course, I am sure you noticed the new column - SEARCH_CONDITION_VC! A Search condition we can easily search! No more of this:
select a.owner, a.table_name, a.search_condition, b.column_name, count(*)
from dba_constraints a, dba_cons_columns b
where a.owner=b.owner
and a.table_name=b.table_name
and a.constraint_name=b.constraint_name
and a.search_condition_vc like '%''t'',''f''%'
group by a.owner, a.table_name, a.search_condition, b.column_name;
group by a.owner, a.table_name, a.search_condition, b.column_name
*
ERROR at line 7:
ORA-00997: illegal use of LONG datatype
Now we can do this!!
select a.owner, a.table_name, a.search_condition_vc, b.column_name, count(*)
from dba_constraints a, dba_cons_columns b
where a.owner=b.owner
and a.table_name=b.table_name
and a.constraint_name=b.constraint_name
and a.search_condition_vc like '%''t'',''f''%'
group by a.owner, a.table_name, a.search_condition_vc, b.column_name;
OWNER TABLE_NAME SEARCH_CONDITION_VC COLUMN_NAME COUNT(*)
---------- ------------------------------ ---------------------- ---------------- --
PROD LCONFIG ride IN ('t','f') RIDE 1
PROD VENT pvent in ('t','f','p','s','c') VENT 1
Very nice....
Monday, May 7, 2018
Friday, April 13, 2018
SSH and X11 Forwarding... A simple cook book...
This post is about using SSH and X-Forwarding when using Oracle X enabled tools like the Database Configuration Assistant. SSH provides a powerfully secure way to access Linux servers. X provides a powerfully secure way of providing a graphical interface into Linux programs. But, because of the security features in these tools, DBA's can sometimes have problems getting them to work. I hope I can address some of these issues in this post, so you can use SSH and X more effectively.
In this post I am going to assume some basic things are already in place. For example, I'll assume your Linux admin has already setup and enabled X on your Linux hosts and that there are no firewalls blocking X connections and the like.
First I'll quickly discuss a few non-SSH connection options that you might want to consider using at times. SSH may not always be the best answer. Then we will talk more about using SSH and X11 forwarding connections between a PC and the database servers in your organization.
Other Connection Options
There are several solutions to the X question. I'm going to discuss a couple of options quickly, but I'll save the details on these for later posts. In some cases, a better graphical option to X is to use VNC. VNC can be a better solution, especially if you are working remotely. This is because it protects you from things like some network failure between the Linux server and your PC. Or, alternatively, it lets you start something like an install at the office, disconnect and head home, and then re-connect to the session. To use VNC, you will have to have the VNC server running on your Linux host. Then you need to download a VNC client to your laptop and install it. Once that is done, you simply connect to the VNC client and viola, you have a nice Linux GUI right in front of you.
If you are using a non-GUI tool (for example, you have a long running shell script or PL/SQL script that you don't want interrupted) then consider using the Linux Screen utility (as opposed to, say, nohup). Screen provides you the ability to start a Linux session and then send it into the background. It is much more reliable (in my experience) than nohup. Screen is something I'll probably pick up in a later post.
Easy SSH and X11 Forwarded Connections
X11 Forwarding provides the ability to run Linux programs that require a GUI interface on a remote client machine. Oracle software like the Oracle Universal Installer (OUI) or the Oracle Database Configuration Assistant (DBCA) require the use of an X11 connection because of their GUI nature. Part of the problem is that some DBA's don't use these tools on a regular basis, so figuring out how to get X working properly is a problem that seems to reoccur.If your going to use SSH and X (and, honestly, I usually do), then I hope these next few bits will make it easier for you. The thing I have found is that if people do not use X a lot, they might forget one or two little things that can screw up the whole works. I hope I can provide a bit of a cookbook for you in these next few paragraphs that you can reference whenever you have the need to use SSH and X.
What Software Do You Need to Make X Connections?
Obviously, to use X you will need some software. In these examples, I'm going to assume that you are using a PC as your jumping off point and that your PC is all networked (that is, you can actually connect to your database server via the network from your PC).The software I use on my PC includes putty which provides my SSH terminal connections from the PC to the database server. I also use Xming on my PC to provide the X services that I need. They are both pretty simple to install, so I'm not going to spend a great deal of time on installing these tools.
Once you get your software downloaded, setup and running , you will configure a putty ssh session to connect to your database server. I assume you are probably already doing this (if you are using telnet to connect to your database server STOP!!!). Once you can establish a SSH connection to your database server with putty, we will need to make one additional configuration change to support X11 connections.
To do this, load the putty configuration for the server. Then click on the Enable X11 forwarding option on the Connection->SSH->X11 options page that you see here:
Make sure you save this session configuration.
Making the X Connection
Now, you are ready to make your first X connection. Simply connect to the database server like you always would. Log into the server as you normally would. Now, you should be able to start an X session if your admin has everything setup properly. To test that theory, run the xterm command from the Linux command line - this should start an X terminal session on your PC:rfreeman@machine1db1 /home/rfreeman]$ xterm
And, on your PC, Xming should bring up the following window:
If you see the window, congratulations! You are using X!
Dealing with XFailure
If you don't see the window at this point, there are a couple of things to check. First of all, check the DISPLAY environment variable and make sure it got set:[rfreeman@machine1db1 ~]$ env|grep DISPLAY
DISPLAY=localhost:11.0
If DISPLAY is not set, then the most likely problem is that you don't have your Putty connection set to enable X11 forwarding, as we discussed earlier. Possibly you forgot to save the Putty configuration change (I tend to do this from time to time). Go back and make sure you configured your Putty connection correctly.
Another possibility is that if you logged in, and then su or sudo'd to another user - your entire X configuration is probably screwed up. Exit back to your original login and try again. I will cover what to do when you need to su or sudo to another user in a moment.
If you still can't get the basic X setup to work, then it's probably time to get your Linux or Network admin's involved.
SU and SUDOing and X11
Using the su and sudo commands to switch between Linux database accounts is common. For example, you might log in as rfreeman, but you need to su or sudo to the oracle account. However, using su and sudo causes a couple problems when trying to use X. The first is an issue with the DISPLAY command and the second has to do with X11 authentication. Let's look at those issues in more detail.The DISPLAY Command
Having sudo'd to the oracle account, we try to run xterm and get the following results:oracle@machine1db1:[db12c]:xterm
xterm Xt error: Can't open display:
xterm: DISPLAY is not set
Our first troubleshooting response it to check the DISPLAY environment variable, which we find is not set:
oracle@machine1db1:[db12c]:env|grep DISPLAY
DISPLAY=
We can solve this problem by changing the DISPLAY variable to the value that it was set to when we first logged in which is shown again here:
DISPLAY=localhost:11.0
To change the value of DISPLAY use the export command as seen here:
oracle@machine1db1:[db12c]:export DISPLAY=localhost:11.0
oracle@machine1db1:[db12c]:env|grep DISPLAY
DISPLAY=localhost:11.0
Unfortunately, this will lead to another problem when you try to run xterm which I'll talk about next.
SU X11 Authentication Issues and Xauth
The next problem we see is where I see a whole lot of people get stuck. We see the next problem immediately after we set the DISPLAY command and try to run xterm again. This time the error is different as seen here (the misspellings are legitimate - go figure):oracle@machine1db1:[db12c]:xterm
PuTTY X11 proxy: Authorisation not recognised
xterm Xt error: Can't open display: localhost:11.0
In the past, I have seen users who had root access change to root and issue the xhost + command, which allows unrestricted X11 access to the database server. Please don't do this, that opens up a pretty nasty security hole. There is a better way to address this problem, and that is through the use of the xauth command.
To be able to do this, we need to do is get back to our first login. So, we are going to exit out of the oracle user account and re-enter the rfreeman account.
oracle@machine1db1:[db12c]:exit
I guess I'm funny here, but I always run xterm again, just to make sure things are still working. If they are, then double check the DISPLAY environment. Note the number at the end of the string. In our case, it's an 11, though it might be 10 or 12 or higher. Here is an example where I ran xterm again, successfully (the two messages that you see here are fine) and I checked the DISPLAY variable value.
[rfreeman@machine1db1 ~]$ xterm
Warning: Cannot convert string "nil2" to type FontStruct
xterm: cannot load font -misc-fixed-medium-r-semicondensed--13-120-75-75-c-60-iso10646-1
[rfreeman@machine1db1 ~]$ env|grep DISP
DISPLAY=localhost:11.0
Now, let me introduce you to the xauth command. The xauth command provides authorization information related to X connections. When you make an X connection, there is an authentication process that occurs and and authentication record is stored on the server.
The reason you can't start a X session when you sudo or su to a different account is that, along with the value of DISPLAY variable, you loose this X authentication record. I am going to show you how to first find your authentication record, and then merge it into the authentication list for the user after you su or sudo to that user.
First, we need to find our authentication record. We use the xauth list command to do this as seen in this example:
[rfreeman@machine1db1 ~]$ xauth list
machine1db1/unix:10 MIT-MAGIC-COOKIE-1 0bfdfdbe34a3ceae01f8434dad31047d
machine1db1/unix:11 MIT-MAGIC-COOKIE-1 d5ed6720174d1e847817b1550972768c
Now, look for the record with the :11 extension (I highlighted it in yello). This is the same extension we saw assigned to the DISPLAY variable earlier. That is the authentication record associated with our session (the :10 record belongs to someone else). Copy the entire record to the clipboard. We will be pasting that into another command shortly.
Having copied the record, su or sudo into the oracle account we connected to earlier. Having successfully connected, we set the DISPLAY environment variable, as we did earlier. Then, we use the xauth add command to add the authentication record we just copied to the authentication list of the oracle user. Here is an example of how all of this looks:
[rfreeman@machine1db1 ~]$ sudo su - oracle
oracle@machine1db1:[db12c]:export DISPLAY=localhost:11.0oracle@machine1db1:[db12c]:env|grep DISPLAY
DISPLAY=localhost:11.0
oracle@machine1db1:[db12c]:xauth add machine1db1/unix:11 MIT-MAGIC-COOKIE-1 d5ed6720174d1e847817b1550972768c
oracle@machine1db1:[db12c]:xterm
Now, the xterm session should start on your system, just as it did earlier!
SU X11 Authentication and Moving Between Servers
Once you have done your initial ssh connection to a Linux server, you may wish to ssh to other database servers - and still be able to start X sessions. To do this, you just need to remember to include the -X command when ssh'ing from one Linux server to another. Again, once you land on a server, you can use xterm to test your ability to start an X session. If you su or sudo to another account on that server, you will need to set the DISPLAY and X authentication again.Summary
I hope you have found this post helpful in helping you to navigate across Linux users and using X. Remember the basic steps:- Configure X forwarding.
- Get the correct display value from the initial session
- Record the X authentication values from the initial session
- Set the DISPLAY value in the su or sudo session
- Set the X authentication values in the su or sudo session.
- Use the -X option of the ssh command when skipping between servers to maintain X forwarding.
Subscribe to:
Posts (Atom)