Tip - List all of the tables in the Shareplex replication

Some time ago a co-worker showed me how to do this with a query. Today I have a need to do it, but I forget. I have to get the table list from the configuration file. Now I blog this as a reference for future:

--- for port 2200 ---
select owner, object_name, objid
from splex.shareplex_objmap s, dba_objects o
where s.objid = o.object_id 
order by owner, object_name;

--- for port 2201 ----
select owner, object_name, objid
from splex_cpf.shareplex_objmap s, dba_objects o
where s.objid = o.object_id 
order by owner, object_name;

How can I confirm there was an I/O problem?

Last Friday afternoon , our production support team ( this team manages application servers in my working environment) reported that arounnd 2:18 PM there were application queuing and some job time-out issues. Naturally, as a knee-jerk reaction, the problem was described as "Database is slow". Later, some "SCSI trans" errors are found somewhere , which indicated there were some I/O problem. So my question is as a DBA, how can I confirm there is an I/O problem?

At first, I tried to look into the AWR data. I modified awr_io_file.sql script by Karl Arao , in hope to report Average time per read time for the data file during the problem period. However, the script was running more than 20 min without results. I killed the job and decided to research on this mining AWR data approach later. BTW, one drawback I am aware of is that even I can get this data, it will be averaged over 15 min (our AWR snapshot interval). Averaging could hide the real problem.

After googling a while, I found that 'sar' utility in my Solaris platform can shed some light on I/O issue in the recent past history.

If we do 'man sar', we can found the following description about sar -f option:

sar [-aAbcdgkmpqruvwy] [-e time] [-f filename]  [-i sec]  [-s time]

sar  extracts  data  from  a  previously  recorded filename,
either the one specified by the -f option  or,  by  default,
the    standard    system    activity    daily   data   file
/var/adm/sa/sadd for the current day dd.  The  starting  and
ending  times  of the report can be bounded using the -e and
-s arguments with time specified in the  form  hh[:mm[:ss]].
The  -i option selects records at sec second intervals. Oth-
 erwise, all intervals found in the data file are reported.

This is the first time that I am aware of that there are 'standard system activity daily data file'
at /var/adm/sa/. As of now, I can see the following files:

myservnm:/var/adm/sa [orclsid] $ ls -l 
total 2177984
-rw-r--r--   1 sys      sys      241097472 Feb 18 23:55 sa18
-rw-r--r--   1 sys      sys      241097472 Feb 19 23:55 sa19
-rw-r--r--   1 sys      sys      241097472 Feb 20 23:55 sa20
-rw-r--r--   1 sys      sys      241097472 Feb 21 23:55 sa21
-rw-r--r--   1 sys      sys      78691536 Feb 22 07:45 sa22

So it looks like we have 5 days data.

What I did last Friday afternoon was:

sar -A  -e 14:28  -s 14:10   > ~/sar1410_1428.txt

If you don't specify the -f, it will extract from the current day's file, i.e. sa18 last Friday.
In the output file, we can see various info recorded at 5 min snapshot

For example, CPU utilization:

SunOS myservnm 5.10 Generic_142900-05 sun4u    02/18/2011
 
14:10:02    %usr    %sys    %wio   %idle
14:15:03      17      14       0      69
14:20:04      10      11       0      78
14:25:05      20      18       0      62
 
Average       16      15       0      70

In the device activity section, I can find at 14:20 snapshot some lines have very high avserv time.
while normally I saw avserv < 30 ms in my system. for examples:

14:10:02   device        %busy   avque   r+w/s  blks/s  avwait  avserv
 
14:15:03   sd7               1     0.0       1      12     0.0     9.0
           sd7,e             1     0.0       1      12     0.0     9.2
           sd8              32     0.3      46     426     0.0     7.4
....
14:20:04   sd7               1     0.0       1      16     0.0     9.1
           sd7,e             1     0.0       1      16     0.0     9.3
...          
sd1133,e          1     0.0       1      26     0.0    12.9
           sd1134           44     0.8       3      73    78.6   238.1
           sd1134,e         44     0.8       3      73    79.1   239.6
           sd1135           42     0.4       1      20     0.0   485.3
           sd1135,e         42     0.4       1      20     0.0   494.7
           sd1136           42     0.4       0      25     0.0  1000.9
           sd1136,e         42     0.4       0      25     0.0  1042.2
           sd1142            1     0.0       1      40     0.0    13.0
...
  sd1204,e          4     0.0       3      81     0.0    16.0
           sd1205           43     0.4       1      22     0.0   440.3
           sd1205,e         43     0.4       1      22     0.0   448.0
           sd1206           43     0.8       2      60    85.3   254.3
           sd1206,e         43     0.8       2      60    85.9   256.1
           sd1207            1     0.0       1      25     0.0    12.7

This webpage  describes the meaning of some sar options and report fields.
This article called "Identifying disk I/O-bound systems" has information about intepreting sar report.

I am happy I have another tool under my tool-belt.

Open a HTML file from Cygwin command line

Cygwin allows me have a UNIX-link enviroment inside my Windows XP environment. As a Cygwin user, sometimes I would like to open a HTML file from Cygwin command line. To achieve this, first of all I create the following file and put under ~/bin.

---- start of ie.bat -----
@start "" /b "C:\Program Files\Internet Explorer\iexplore.exe" %*
---- end of ie.bat  ------

Secondly, in Cygwin, if we type pwd, we get UNIX-like current path name, i.e. with forward slashs, for example:

$ pwd
/cygdrive/c/Denis_Files/VZ2009

I need to convert it to Windows path name, so I create a shell script as follows:

$ cat ~/bin/pwdw
pwd | sed s#/cygdrive/c#c:# |sed  -e 's#\/#\\#g'

Now if I type 'pwdw' in the Cygwin command windows, I got:

$ pwdw
c:\Denis_Files\VZ2009

To start a HTML file, such as AWR report, what I need to do is:

ie.bat 'c:\Denis_Files\VZ2009\awrrpt_20101003_1300_1330.html'

Just a small tip that makes my life easier :-).

Privileges to enable a non-DBA user to generate AWR report

The following privileges seem working for the stated purpose:

GRANT SELECT ON SYS.V_$DATABASE TO xxx
/
GRANT SELECT ON SYS.V_$INSTANCE TO xxx
/
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO xxx
/
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO xxx
/
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO xxx
/
GRANT ADVISOR TO xxx
/

Effect of a SQL* Plus environment variable: set pagesize 0

Last night, I troubleshooted a ksh shell script, in which a variable is assigned a value that is returned from SQL* Plus commands. I found that without 'set pagesize 0', the value returned had an undesirable leading space. Below is a test case that demonstrate this effect.

SQL> select col1, col3 from t;
 
      COL1 C
---------- -
         1 Y


$ cat test3.sh 
#!/bin/ksh 
 
RET1=` sqlplus -s  / << EOF
set  echo off head off feed off verify off term off  
select col3 from t
where  col1=1;
EOF
`
 
echo "RET1  >>>>"$RET1"<<<<"
if [[ $RET1 == 'Y' ]]; then
  echo "Yes"
else
  echo "No"
fi
 
RET2=` sqlplus -s  / << EOF
set echo off head off feed off verify off term off 
set pagesize 0
select col3 from t
where  col1=1;
EOF
`
 
echo "RET2  >>>>"$RET2"<<<<"
if [[ $RET2 == 'Y' ]]; then
  echo "Yes"
else
  echo "No"
fi


$ ./test3.sh 
RET1  >>>> Y<<<<
No
RET2  >>>>Y<<<<
Yes

Why you need to know a job's LOG_USER when using DBMS_JOB?

Log in as SYS, I executed the following command to change a job's attributes, however, I recieved ORA-23421 error.

sys@MYDB> begin
dbms_job.change(
1001,
what=>'BEGIN DBMS_STATS.gather_database_stats(ESTIMATE_PERCENT => 30, CASCADE=>TRUE, OPTIONS=> ''GATHER STALE'', DEGREE=> 7,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1''); END;',
next_date=> sysdate+1,
interval=> 'trunc(sysdate+1)+1/48'
);
end;
/ 2 3 4 5 6 7 8 9
begin
*
ERROR at line 1:
ORA-23421: job number 1001 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 207
ORA-06512: at "SYS.DBMS_JOB", line 188
ORA-06512: at line 2


Later, I learned that every job has a LOG_USER attribute, which is the login user when the job is submitted. Even SYS can not change other user's job:


sys@MYDB> ho oerr ora 23421
23421, 00000, "job number %s is not a job in the job queue"
// *Cause: There is no job visible to the caller with the given job number.
// *Action: Choose the number of a job visible to the caller.
//

sys@MYDB> select job, log_user from dba_jobs;

JOB LOG_USER
---------- ------------------------------
... ...
1001 OPS$ORACLE


After log in as OPS$ORACLE, the above statement got executed successfully.

ALTER SESSION SET CURRENT_SCHEMA

Recently I have learned how to use ALTER SESSION SET CURRENT_SCHEMA=schmea. This setting makes subsequent unqualified references to schema objects during the session resolve to objects in the specified schema.

Sometimes, developers give us scripts without qualifying the objects with the schema name, and We DBA usually execute the scripts in our own database user account. So this setting provides a convenient way to resolve this issue, otherwise we may have to modify the script to add schema names. Before I tend to complain about it and emphasize developers should have used qualified name. Actaully, I learned this trick from a developer.

A concern is thus how can I know what is my CURRENT_SCHMEA setting? If I am not careful, I may end up drop a table t that belongs to another schema. It is not difficult to find the answer by google.

The following is the query:

z98xxx4@DBT920> ho cat sess_user_sch.sql
col current_user format a15
col current_schema format a15

select sys_context('USERENV','SESSION_USER') current_user,
sys_context('USERENV','SESSION_SCHEMA') current_schema
from dual
/


z98xxx4@DBT920> create table denis.t as select 'denis' name from dual;

Table created.

z98xxx4@DBT920> create table z98xxx4.t as select 'z98xxx4' name from dual;

Table created.
z98xxx4@DBT920> @sess_user_sch.sql
CURRENT_USER CURRENT_SCHEMA
--------------- ---------------
Z98xxx4 Z98xxx4

z98xxx4@DBT920> select * from t;

NAME
------------------------------
z98xxx4

z98xxx4@DBT920> alter session set current_schema=denis;

Session altered.

z98xxx4@DBT920> @sess_user_sch.sql
CURRENT_USER CURRENT_SCHEMA
--------------- ---------------
Z98xxx4 DENIS

z98xxx4@DBT920> select * from t;

NAME
------------------------------
denis