Ergem PEKER 's Oracle Blog

Oracle Restart hands on

2011-10-19T16:36:00.000+03:00

Starting from Oracle Database 11g, a new product (or functionality) called Oracle Restart comes with the part of the Grid Infrastructure installation. It seems Oracle decided to use crsctl, crs_stat, and srvctl like RAC commands for also managing the processes of the single instance databases. This standardization seems handy to me as i have already get used to manage RAC databases day by day.

After upgraded one of the development databases in our data center from 10.2.0.4 to 11.2.0.2 as well as the ASM instance, i decided to spend some of my time to play with this new functionality.

As on the RAC installations status of the services can be investigated with the crs_stat -t command. I think it is understandable that there is not vip, ons, gsd services here as this is not a RAC database.

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    OFFLINE               
ora....ER.lsnr ora....er.type OFFLINE   OFFLINE               
ora.asm        ora.asm.type   OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6 

[oracle@rhel6]:/oracle > crs_stat
NAME=ora.DG_DB_ASM.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.asm
TYPE=ora.asm.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on rhel6

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=ONLINE
STATE=ONLINE on rhel6

The processes of the CRS (it is "HAS" for single instance) is again controlled by crsctl as it is in the RAC installations. You can use check, start, stop options to manage the processes as usual. A small note; CRS processes in the RAC installation is not installed for the single instance installations. For the single instance installations, there is the HAS processes stands for "High Availability Services" and covers the cssd and diskmon processes.

[oracle@rhel6]:/oracle > crsctl check has
CRS-4638: Oracle High Availability Services is online
[oracle@rhel6]:/oracle > crsctl check css
CRS-4529: Cluster Synchronization Services is online
[oracle@rhel6]:/oracle > crsctl check resource ora.cssd

[oracle@rhel6]:/oracle > crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rhel6'
CRS-2673: Attempting to stop 'ora.cssd' on 'rhel6'
CRS-2677: Stop of 'ora.cssd' on 'rhel6' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rhel6'
CRS-2677: Stop of 'ora.diskmon' on 'rhel6' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rhel6' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[oracle@rhel6]:/oracle > crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

After my upgrade process Oracle Restart could not be able to manage the upgraded database. By using srvctl i added the database resource to the repository so that i can manage the database services by using srvctl command line tool. One of the nicest option is, by using the "-a" option and supplying dependent diskgroups of the database makes Oracle Restart to start the ASM and mount the related diskgroups before starting up the database.

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle > srvctl add database -h          

Adds a database configuration to be managed by Oracle Restart.

Usage: srvctl add database -d  -o  
  [-m ] 
  [-p ] 
  [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] 
  [-s ] 
  [-t ] 
  [-n ] 
  [-y {AUTOMATIC | MANUAL}] 
  [-a ""]
-d       Unique name for the database
-o          ORACLE_HOME path
-m               Domain for database. Must be set if database has DB_DOMAIN set.
-p               Server parameter file path
-r                 Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s        Startup options for the database. Examples of startup options are open, mount, or nomount.
-t         Stop options for the database. Examples of shutdown options are normal, transactional, immediate, or abort.
-n         Database name (DB_NAME), if different from the unique name given by the -d option
-y             Management policy for the database (AUTOMATIC or MANUAL)
-a ""      Comma separated list of disk groups
-h                       Print usage

[oracle@rhel6]:/oracle > srvctl add database -d ORCLT -o /oracle/orahome1
[oracle@rhel6]:/oracle >
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle > srvctl start database -d ORCLT
[oracle@rhel6]:/oracle > ps -ef | grep smon
oracle  9109530        1   0 15:46:08      -  0:00 ora_smon_ORCLT
oracle 11075806        1   0 15:43:50      -  0:00 asm_smon_+ASM
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle >                                                                                                     
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > srvctl status database -d ORCLT
Database is running.

[oracle@rhel6]:/oracle >            
                    
[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.ORCLT.db   ora....se.type ONLINE    ONLINE    rhel6   
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > srvctl stop database -d ORCLT

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   OFFLINE   ONLINE    rhel6   
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6

The second handy feature is "enable" and "disable" of the srvctl which configures the related objects restart options on host restart or restart of the process on failure.

[oracle@rhel6]:/oracle > srvctl enable -h 

The SRVCTL enable command enables the named object so that it can run under 
  Oracle Restart for automatic startup, failover, or restart.

Usage: srvctl enable database -d 
Usage: srvctl enable service -d  -s ""
Usage: srvctl enable asm
Usage: srvctl enable listener [-l ]
Usage: srvctl enable diskgroup -g 
Usage: srvctl enable ons [-v]
Usage: srvctl enable eons [-v]

Shutting down everything nicely with Oracle Restart.

[oracle@rhel6]:/oracle > srvctl stop database -d ORCLT
[oracle@rhel6]:/oracle > srvctl stop diskgroup -g DG_DB_ASM
[oracle@rhel6]:/oracle > srvctl stop asm
[oracle@rhel6]:/oracle > srvctl stop listener

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type OFFLINE   OFFLINE               
ora....ER.lsnr ora....er.type OFFLINE   OFFLINE               
ora.asm        ora.asm.type   OFFLINE   OFFLINE               
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6

resources:
http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/restart001.htm
$ srvctl -h

Random Data Generation and DBMS_RANDOM

2011-09-12T23:47:00.000+03:00

Generating random data itself is already a subject of its own. But here i can only write about the needs of random data in a database environment and how a database developer or administrator can generate the random data to fulfill his/her requirements. I personally used random data generation in three main purposes up to now.

DATAMASKING

Almost in every company i have worked for, datamasking is a must-have procedure for test data generation, especially after restoring production data to a prelive or day-1 database. In common sense just truncating the sensitive data or updating the columns to NULL maybe enough for security purposes. But in some individual cases application logic needs to continue which depends on the data availability. In such cases there are two main roadmaps, hashing the real data and un-identify it or some random data may be generated for representing the real sensitive data. The generated data may also ensure the formatting of the known structures such as credit card information, postal code or telephone number of the customers.

PASSWORD GENERATION

After applying the new password policy to our database, forgotten passwords by the end users are not recovered that easily because of the password complexity functions (by applying password verify function) and the password_reuse_max parameter of the profile. This means that, the portal used by the level-1 support is not enough as the procedure lies behind the button is simply changes the password to a default value which is not changed from the beginning of the procedure at all, which causes ORA-28007: the password cannot be reused exception.

TEST DATA GENERATION

In order to test any functionality or any new features by myself, generating data for newly created tables is a necessity. Formatted varchar2 and number columns are very handy to test some functionalities and how some functionalities behave in large amount of structured table data.

Dictionary views can be used for this purpose but If it is not enough for the individual test conditions then generation a random data within the desired format is crucial.

DBMS_RANDOM PACKAGE

For all the mentioned purposes, oracle database has a built-in package which is DBMS_RANDOM to generate random data. There are several functions in this package which can be used to obtain same results with some manipulation of the output values. Main functions to produce random number would be;

DBMS_RANDOM.VALUE() whichs output value is NUMBER datatype, this means you can produce up to 32 digit numbers.

select dbms_random.value() as result from dual;

RESULT
------
0.33363563290178533954768590716355821427

DBMS_RANDOM.VALUE(arg1 number, arg2 number) returns a random value between the supplied limits. The following sql should return a random number between 1 and 20.

select dbms_random.value(1,20) as result from dual;

RESULT
------
14.25291834803147861791906299688905822678

DBMS_RANDOM.STRING() function can also be used in order to generate a random character or a string. Function has two parameters one for the type of characters to be used and the second parameter for the length of the string. The following function definition is copied here from and Oracle 11gR2 databases DBMS_RANDOM package spec. It explains itself very well and nothing needs to be added.

FUNCTION string (opt char, len NUMBER)

/* 
"opt" specifies that the returned string may contain:

'u','U': upper case alpha characters only
'l','L': lower case alpha characters only
'a','A': alpha characters only (mixed case)
'x','X': any alpha-numeric characters 'p','P': any printable characters
*/
RETURN VARCHAR2;  
--string of  characters

So, if you need to produce some random string with 8 characters long and all characters are alpha numeric and lower case then it is easy by using the DBMS_RANDOM.STRING() function:

select dbms_random.string('l',8) as result from dual;

RESULT
------
yhxctomv

DBMS_RANDOM BY EXAMPLES

All the explanations of the examples can be found in the document that i shared from here. You can also find the link at the bottom of this post to the same document.

--------------------------
--masking the card numbers
--Ex.1
--------------------------
select 
lpad(round(dbms_random.value*power(10,4)),4,0) || '-' ||
lpad(round(dbms_random.value*power(10,4)),4,0) || '-' ||
lpad(round(dbms_random.value*power(10,4)),4,0) || '-' ||
lpad(round(dbms_random.value*power(10,4)),4,0)  as card_number
from dual 
connect by level <=5; 
/*
CARD_NUMBER
-----------
2877-6639-0728-5456
6026-6002-2218-9038
7679-8441-0899-2826
8294-6783-6110-7988
1836-0407-9206-3333
*/

--------------------------
--masking the card numbers
--Ex.2
--------------------------
select 
ltrim(to_char(dbms_random.value(1,9999),'0000')) || '-' ||
ltrim(to_char(dbms_random.value(1,9999),'0000')) || '-' ||
ltrim(to_char(dbms_random.value(1,9999),'0000')) || '-' ||
ltrim(to_char(dbms_random.value(1,9999),'0000'))  as card_number
from dual 
connect by level <=5; 
/*
CARD_NUMBER
-----------
1558-9846-7194-5325
5109-3233-0641-9209
3081-5946-9840-6615
4400-9638-6333-9113
2928-9883-1771-0465
*/

--------------------------
--masking the card numbers
--Ex.3
--------------------------
select 
ltrim(replace(to_char(round(dbms_random.value*power(10,16)),'0000,0000,0000,0000'),',','-')) as card_number
from dual
connect by level <=5;
/*
CARD_NUMBER
-----------
0157-8125-6418-6025
3829-9039-1357-9048
2876-1086-5371-8152
2775-1748-2591-2523
2058-2404-1101-5320
*/

--------------------------
--masking the card numbers
--Ex.4
--------------------------
select 
substr(abs(dbms_random.random),1,4) || '-' ||
substr(abs(dbms_random.random),1,4) || '-' ||
substr(abs(dbms_random.random),1,4) || '-' ||
substr(abs(dbms_random.random),1,4)  as card_number
from dual 
connect by level <=5; 
/*
CARD_NUMBER
-----------
8639-7576-1359-3965
1317-1525-2526-1796
1043-5881-1000-7113
2106-3239-8662-3769
1461-7473-5870-6829
*/

--------------------------
--masking the phone number
--Ex.5
--------------------------
select 
  '+' || 
  round(DBMS_RANDOM.VALUE(1,99)) || '-' ||
  round(DBMS_RANDOM.VALUE(10,99)) || '-' ||
  round(DBMS_RANDOM.VALUE(1000000,9999999)) as phone_number
from dual
connect by level <= 5;
/*
PHONE_NUMBER
------------
+8-44-9146987
*/

----------------------
--masking the postcode
--Ex.6
----------------------
select 
  round(dbms_random.value(1000,9999)) || '-' || 
  dbms_random.string('U',2) as postcode 
from dual;
/*
POSTCODE
--------
4997-QP
*/

What if, you have a password verify function which commits the passwords will be at least 8 characters long and must contain alphanumeric characters and this password complexity merged with a profile which has a password_lifetime of two month and password_reuse_max is four. If there is a predefined automatic case which explained detailly in the paper mentioned before which needs random password generation then there is the example which can be used;

-----------------
--random password
--Ex.7
-----------------
select  
  DBMS_RANDOM.STRING('A',1) || 
  round(DBMS_RANDOM.VALUE()*10) || 
  DBMS_RANDOM.STRING('X',6) as password 
from dual;
/*
PASSWORD
--------
w7N3C1YG
*/

TEST DATA GENERATION

I generally use two different methods while generating test data to fill the test tables. One of them is by using the DBMS_RANDOM package and the other is filling the bulk data in the columns with the same output as you can find in the following examples.

In this first part of the following example, the code tries to simulate a username bu using lower case string and with random lengths between five and fifteen. The firstname starts with uppercase by using INICAP() function and the lastname is fully in uppercase by using the UPPER() inline function. The second part is not that clever and it just creates the same data over and over which can substitude a customer name or a username.

----------------------
--test data generation
--Ex.8
----------------------
select 
  initcap(dbms_random.string('L',round(dbms_random.value(5,15)))) || ' ' || 
  upper(dbms_random.string('L',round(dbms_random.value(5,15)))) as name
from dual
connect by level <= 5;
/*
NAME
----
Abzvsidgbcfa AGUGIR
Wvuogptkxwhdwa IPOOXTVBLLCNPV
Yiwcgh SGPFKJYCDISO
Radshiyidcrst ZNKNSEYUZXVWY
Daxeqzugq LKJILZJEYULVI
*/

select
initcap(lpad('x',round(dbms_random.value(5,15)),'x')) || ' ' || 
upper(lpad('y',round(dbms_random.value(5,15)),'y')) as name
from dual
connect by level <= 5;
/*
NAME
----
Xxxxxxxxxxxx YYYYYYYYYYY
Xxxxxxxxxxxxxxx YYYYYYYYYYYYY
Xxxxxxxxxxxxxx YYYYYYYYY
Xxxxxxxx YYYYYYYYYYY
Xxxxxx YYYYYYYY
*/


select 
  initcap(lpad('x',9,'x')) || ' ' ||
  upper(lpad('y',9,'y')) as name
from dual
connect by level <= 5;
/*
NAME
----
Xxxxxxxxx YYYYYYYYY
Xxxxxxxxx YYYYYYYYY
Xxxxxxxxx YYYYYYYYY
Xxxxxxxxx YYYYYYYYY
Xxxxxxxxx YYYYYYYYY
*/

GENERATING MEANINGFUL DATA

Upto now, the generated data was completely dummy. For example while masking the credit card number, it wasn’t important if this was a valid card number or not, or if the names are real names or not. All the data generated were dummy random data which does not make any sense, they are just string or numbers formed by number characters or digits.

But by using the following example random data can be generated which makes sense (or a little sensible than the previous methods).

In this small example, there is a lookup table (which can be extended as far as the individual case needs) and the phone number generator picks up a random country code from the lookup table to generate the phone number. Every random phone number generated will have a valid country code by using the following example. Of course this example can be extended for the area codes as well.

--------------------------
--generating sensible data
--Ex.9
--------------------------
create table t_country_codes 
  (key number(2), 
   code number(2), 
   country varchar2(20));
   
insert into t_country_codes values (1,1,'United States');
insert into t_country_codes values (2,31,'Netherlands');
insert into t_country_codes values (3,44,'United Kingdom');
insert into t_country_codes values (4,49,'Germany');
insert into t_country_codes values (5,90,'Turkey');
commit;

select 
  '+' || code || '-' || 
  round(dbms_random.value(10,99)) || '-' || 
  round(DBMS_RANDOM.VALUE(100000,999999)) as phone_number
from 
  t_country_codes 
where 
  key=(select round(DBMS_RANDOM.VALUE(1,5)) from dual);
/*  
PHONE_NUMBER
------------
+31-54-732777
*/

This post is taken from the paper i wrote for an internal use and enchanced for my blog post. You can download the paper from the following link if you have google docs access. Generating Random Data in Oracle 11g Database

Howto Recover Archive Gap in Streams Configuration

2011-08-08T11:16:00.000+03:00

When i realized that my archive logs are not shipping to the destination database (both the databases are version 10.2.0.4) which is using downstream capture process for streams replication it was too late that i already missed around 20 archived logs. I fixed the problem which was originated from the different service definition in the log_archive_dest_2 system parameter and the TNS alias. But what about the missing archived logs?

--SOURCE DB
SQL> select name, value from v$parameter where name = 'log_archive_dest_2';

NAME               VALUE
----               -----
log_archive_dest_2 SERVICE=ODSD ASYNC NOREGISTER
                   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   
                   DB_UNIQUE_NAME=ODSD

After correcting the entry in the tnsnames.ora file log shipping started from where it paused. I tested it by simply archiving the current redolog.

--SOURCE DB
SQL> alter system archive log current;
System altered.

SH> ls
-rw-r-----    1 oracle   dba      43451392 Aug 05 09:00 1_21759_657122256.dbf
-rw-r-----    1 oracle   dba       8708608 Aug 05 09:57 1_21760_657122256.dbf

--DESTINATION DB
SH> ls
-rw-r-----    1 oracle   dba      15930368 Aug  4 16:38 1_21727_657122256.dbf
-rw-r-----    1 oracle   dba       8708608 Aug  5 09:58 1_21760_657122256.dbf

Now the question how can i recover the gap between the last archivelog and the one created approximately 12 hours ago. First i should define the exact archived logs should be carried from the source to the target. I will check the capture process and see which SCN is it waiting for. As i am using a downstream capture process i should check the capture process in the target database.

--DESTINATION DB
SQL> select capture_name, state from v$streams_capture;

CAPTURE_NAME       STATE
------------       -----
CAPTURE_TABLE_GRP1 WAITING FOR DICTIONARY REDO: SCN 7523421102323

I find the SCN number which is waited in the target. I should find which archived log is this scn in the source database?

--SOURCE DB
SQL> SELECT 
  name, dest_id, sequence#, first_change#, next_change#, completion_time 
FROM V$ARCHIVED_LOG where 7523421102323 between first_change# and next_change#

NAME                               DEST_ID SEQUENCE# FIRST_CHANGE# COMPLETION_TIME
----                               ------- -------- ------------- ---------------
/oracle/.../1_21728_657122256.dbf  1       21728    7523421102321 04/08/11 16:38:52
ODSD,                              2       21728    7523421102321 04/08/11 16:38:54
/oracle/.../1_21729_657122256.dbf  1       21729    7523421102323 04/08/11 16:39:10

It seems that after sequence# 21728 archived logs are not shipped to the destination database. What i will do is to copy these archived logs manually to the destination host from the target by using OS commands.

After copying the archived logs i have to register the archived logs for the streams configuration. The beginning of the register command is similar to the one we already know. And a small oppss!...

--DESTINATION DB
SQL> alter database register logical logfile '/oracle/admin/ODSD/archive/1_21759_657122256.dbf';

ORA-16225: Missing LogMiner session name for Streams

SQL> select name, source_database from DBA_LOGMNR_SESSION;

ID NAME               SOURCE_DATABASE
-- ----               ---------------  
2  CAPTURE_TABLE_GRP1 CORED.CEB.LOCAL

SQL> alter database register logical logfile '/oracle/admin/ODSD/archive/1_21759_657122256.dbf' for 'CAPTURE_TABLE_GRP1';

Database altered.

SQL> select logmnr_session_id, name from DBA_LOGMNR_LOG; --or dba_registered_archived_log

LOGMNR_SESSION_ID NAME
----------------- ----
2                 /oracle/admin/ODSD/archive/1_21759_657122256.dbf
2                 /oracle/admin/ODSD/archive/1_21760_657122256.dbf
2                 /oracle/admin/ODSD/archive/1_21761_657122256.dbf

Lets check the capture process again. And the second oppss!..

--DESTINATION DB
SQL> select capture_name, state from v$streams_capture;

no rows selected.

SQL> select capture_name, status, captured_scn, applied_scn from dba_capture;

CAPTURE_NAME  STATUS CAPTURED_SCN APPLIED_SCN
------------  ------ ------------ -----------
CAPTURE_TABLE_GRP1 ABORTED 7523421102323 7523421102323

It seems the capture process is aborted while we are registering the archived logs. Maybe we need to restart it.

SQL> exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_TABLE_GRP1');

SQL> select capture_name, state from v$streams_capture;

CAPTURE_NAME       STATE
------------       -----
CAPTURE_TABLE_GRP1 WAITING FOR DICTIONARY REDO: SCN 7523573032011

It seems the problem is solved and the capture process moves on with the next scn and the archived logs.

Hasty DBA's Most Probable Errors while testing TDE

2011-07-19T12:43:00.006+03:00

Here I tested Oracle Transparent Data Encryption in 10gR2 (10.2.0.4) database which is installed on AIX 6.1 to see how it works and if there are some undocumented difficulty while using the technology. As i was too hasty and careless, i have seen most of the cheap errors.

In Summary;

ORA-28368: cannot auto-create wallet

Solution: Create the wallet directory (in this case default wallet directory which is $ORACLE_BASE/admin/$ORACLE_SID/wallet)

ORA-28336: cannot encrypt SYS owned objects

Solution: Do not test under SYS schema :)

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

Solution: Use encryption_password parameter to store encrypted data safe in the export dump. Else it will be plain text.

ORA-31693: Table data object "ERGEMP"."T_ENCRYPTED" failed to load/unload and is being skipped due to error:
ORA-28336: cannot encrypt SYS owned objects

Solution: Use your own user for exporting instead of SYS as you cannot encrypt SYS obejcts even in the export dumpfile.

Error: ORA-39087: directory name DATA_PUMP_DIR is invalid

Solution: Dont forget to give write permission to your user which you are exporting data with :)

The rest is as follows.

[oracle@defbora01]:/oracle/admin/CORET > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 14 12:18:08 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set encryption key identified by "pass1234";
alter system set encryption key identified by "pass1234"
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@defbora01]:/oracle/admin/CORET > mkdir wallet
[oracle@defbora01]:/oracle/admin/CORET > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 14 12:20:03 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set encryption key identified by "pass1234";

System altered.

SQL> 


/*** ***/

SQL> create table t_encrypted(col1 varchar2(50) encrypt);
create table t_encrypted(col1 varchar2(50) encrypt)
*
ERROR at line 1:
ORA-28336: cannot encrypt SYS owned objects
SQL> create user ergemp identified by "pass1234" default tablespace users;

User created.

SQL> 
SQL> 
SQL> grant connect, resource to ergemp;

Grant succeeded.

SQL> create table ergemp.t_encrypted(col1 varchar2(50) encrypt);

Table created.

SQL> insert into ergemp.t_encrypted values ('this text should be encrypted');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dba_encrypted_columns;

OWNER           TABLE_NAME                     COLUMN_NAME               ENCRYPTION_ALG                SAL
--------------- ------------------------------ ------------------------- ----------------------------- ---
ERGEMP          T_ENCRYPTED                    COL1                      AES 192 bits key              YES

SQL> select * from ERGEMP.T_ENCRYPTED;

COL1
--------------------------------------------------
this text should be encrypted

SQL>

I will test the datapump export. First i will not use the "encryption_password" parameter and search for the string of the column value if it is somewhere in the dumpfile.

And then i will run the same datapump export by using the "encryption_password" parameter to be sure the column value is not readable in the dumpfile with "string" utility.

[oracle@defbora01]:/oracle/orahome1/rdbms> expdp '/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables='ERGEMP.T_ENCRYPTED'


Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 July, 2011 11:53:18

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  '/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables=ERGEMP.T_ENCRYPTED 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ERGEMP"."T_ENCRYPTED"                      4.953 KB       1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/orahome1/rdbms/log/expdp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 11:56:53

[oracle@defbora01]:/oracle/orahome1/rdbms/log > strings expdp.dmp | grep "this text should be encrypted"
this text should be encrypted

[oracle@defbora01]:/oracle/orahome1/rdbms/log >

Without using the "encryption_password", data in the export files are not safe because the they are held as in plain text as it can be seen from the previous example.

[oracle@defbora01]:/oracle/orahome1/rdbms> expdp '/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables='ERGEMP.T_ENCRYPTED' encryption_password="pass1234"

Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 July, 2011 13:53:56

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  '/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables=ERGEMP.T_ENCRYPTED encryption_password=******** 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "ERGEMP"."T_ENCRYPTED" failed to load/unload and is being skipped due to error:
ORA-28336: cannot encrypt SYS owned objects
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/orahome1/rdbms/log/expdp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 13:55:55

[oracle@defbora01]:/oracle/orahome1/rdbms> expdp 'ergemp/********' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables='ERGEMP.T_ENCRYPTED' encryption_password="pass1234"

Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 July, 2011 13:59:17

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

SQL> grant read,write on directory DATA_PUMP_DIR to ERGEMP;

Grant succeeded.

[oracle@defbora01]:/oracle/orahome1/rdbms> expdp 'ergemp/********' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables='ERGEMP.T_ENCRYPTED' encryption_password="pass1234"

Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 July, 2011 14:08:59

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ERGEMP"."SYS_EXPORT_TABLE_01":  'ergemp/********' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables=ERGEMP.T_ENCRYPTED encryption_password=******** 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ERGEMP"."T_ENCRYPTED"                          5 KB       1 rows
Master table "ERGEMP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ERGEMP.SYS_EXPORT_TABLE_01 is:
/oracle/orahome1/rdbms/log/expdp.dmp
Job "ERGEMP"."SYS_EXPORT_TABLE_01" successfully completed at 14:10:19

[oracle@defbora01]:/oracle/orahome1/rdbms/log > strings expdp.dmp | grep "this text should be encrypted"

[oracle@defbora01]:/oracle/orahome1/rdbms/log >

By using the "encryption_password" while exporting the table the data in the column cannot be distinguished by digging the plain strings in the export file.

resources:
Oracle Advanced Security Admin Guide

Howto setup a manual logshipping in SQL server

2011-07-13T16:57:00.001+03:00

Whenever i receive an error during the setup of the SQL server logshipping i was always hopeless. But from now on i have a procedure to setup a manual logshipping schedule from primary sql server to secondary. It is important to mention that i used the following structure in SQL Server 2000 and it worked like a piece of cake.

In summary, the main steps are as follows;

1- on primary database: create a job to backup transaction logs regularly
2- on primary database: share the transaction log backup directory.
3- backup and restore the database to be log shipped from prımary to secondary database
4- on secondary database: create the following structure (tables and procedures)
5- on secondary database: create the copy and load jobs via the created structure
6- on secondary database: monitor the process

There are 3 stored procedures and 3 tables to create;

USE msdb
CREATE TABLE backup_movement_plans
(
  plan_id         UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
  plan_name       sysname          NULL,
  source_dir      NVARCHAR(256)    NOT NULL,
  destination_dir NVARCHAR(256)    NOT NULL,
  database_subdir BIT              NOT NULL DEFAULT (1)
)

USE msdb
CREATE TABLE backup_movement_plan_databases
(
plan_id  UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES       backup_movement_plans(plan_id),
  source_database      sysname          NOT NULL,
  destination_database sysname          NOT NULL,
  source_server        sysname          NOT NULL DEFAULT (@@servername),
  load_delay           INT              NOT NULL DEFAULT(0),  -- In minutes
  load_all             BIT              NOT NULL DEFAULT(1),
  retention_period     INT              NOT NULL DEFAULT(48), -- In hours
  last_file_copied     NVARCHAR(256)    NULL,
  date_last_copied     DATETIME         NULL,
  last_file_loaded     NVARCHAR(256)    NULL,
  date_last_loaded     DATETIME         NULL
)

USE msdb
CREATE TABLE backup_movement_plan_history
(
  sequence_id          INT              NOT NULL IDENTITY UNIQUE CLUSTERED,
  plan_id              UNIQUEIDENTIFIER NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
  plan_name            sysname          NOT NULL DEFAULT ('All ad-hoc plans'),
  destination_server   sysname          NOT NULL DEFAULT (@@servername),
  source_server        sysname          NOT NULL DEFAULT (@@servername),
  source_database      sysname          NOT NULL,
  destination_database sysname          NOT NULL,
  activity             BIT              NOT NULL DEFAULT (0),
  succeeded            BIT              NOT NULL DEFAULT (1),
  num_files            INT              NOT NULL DEFAULT (0),
  last_file            NVARCHAR(256)    NULL,
  end_time             DATETIME         NOT NULL DEFAULT (GETDATE()),
  duration             INT              NULL     DEFAULT (0),
  error_number         INT              NOT NULL DEFAULT (0),
  message              NVARCHAR(512)    NULL
)  

USE msdb
CREATE PROCEDURE sp_create_backup_movement_plan
    @name          sysname,
    @source_dir    VARCHAR(256),
    @dest_dir      VARCHAR(256),
    @sub_dir       BIT = 1, -- Each database has it's own sub-directory
    @load_job_freq INT = 5, -- In Minutes
    @copy_job_freq INT = 5  -- In Minutes
AS
BEGIN

BEGIN TRANSACTION
  SET       NOCOUNT             ON
  SET       QUOTED_IDENTIFIER   OFF
  SET       ANSI_NULLS          ON 

  DECLARE   @PlanID        uniqueidentifier
  DECLARE   @CopyJobName   sysname
  DECLARE   @LoadJobName   sysname
  DECLARE   @CopyCommand   VARCHAR(500)
  DECLARE   @LoadCommand   VARCHAR(500)
  DECLARE   @ReturnCode    INT

  -- Create a GUID for the plan
  SELECT @PlanID = NEWID()
    
  -- Check if a plan with the same name exists
  IF (EXISTS (SELECT * 
              FROM   msdb.dbo.backup_movement_plans
              WHERE  plan_name = @name ))
  BEGIN
    RAISERROR('A backup movement plan with the same name already exists. Specify a different name.'', 16, 1)
    GOTO QuitWithRollback
  END

  -- Insert plan in the table
  INSERT msdb.dbo.backup_movement_plans 
         (plan_id, plan_name, source_dir, destination_dir, database_subdir)
  VALUES
         (@PlanID, @name, @source_dir, @dest_dir, @sub_dir)

  SELECT @CopyJobName = N'Copy Job For ' + @name
  SELECT @LoadJobName = N'Load Job For ' + @name
  SELECT @CopyCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-CopyPlanName "' + @name + '" '' '
  SELECT @LoadCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-LoadPlanName "' + @name + '" '' '
  
  -- Create the load job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_name = @LoadJobName

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_name = @LoadJobName, 
     @step_id=1,
     @step_name = N'step1', 
     @command = @LoadCommand, 
     @subsystem = N'TSQL', 
     @on_success_step_id = 0, 
     @on_success_action = 1, 
     @on_fail_step_id = 0, 
     @on_fail_action = 2, @flags = 4

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name = @LoadJobName, 
     @freq_subday_interval = @load_job_freq, 
     @name = N'sch1', 
     @enabled = 1, 
     @freq_type = 4, 
     @active_start_date = 19980402, 
     @active_start_time = 0, 
     @freq_interval = 1, 
     @freq_subday_type = 4, 
     @freq_relative_interval = 0, 
     @freq_recurrence_factor = 0, 
     @active_end_date = 99991231, 
     @active_end_time = 235959

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_name = @LoadJobName, @server_name = N'(local)' 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Create the Copy Job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_name = @CopyJobName

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_name = @CopyJobName, 
     @step_id = 1, 
     @step_name = N'step1', 
     @command = @CopyCommand, 
     @subsystem = N'TSQL', 
     @on_success_step_id = 0, 
     @on_success_action = 1, 
     @on_fail_step_id = 0, 
     @on_fail_action = 2, 
     @flags = 4

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name = @CopyJobName, 
     @freq_subday_interval = @copy_job_freq, 
     @name = N'sch1', 
     @enabled = 1, 
     @freq_type = 4, 
     @active_start_date = 19980402, 
     @active_start_time = 0, 
     @freq_interval = 1, 
     @freq_subday_type = 4, 
     @freq_relative_interval = 0, 
     @freq_recurrence_factor = 0, 
     @active_end_date = 99991231, 
     @active_end_time = 235959

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_name = @CopyJobName, @server_name = N'(local)' 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 
END 

USE msdb
CREATE PROCEDURE sp_add_db_to_backup_movement_plan
    @plan_id       uniqueidentifier   = NULL,
    @plan_name     sysname            = NULL,
    @source_db     sysname,
    @dest_db       sysname,
    @load_delay    INT                = 0,            -- In Minutes
    @load_all      BIT                = 1,
    @source_server sysname            = @@servername,
    @retention_period INT             = 48            -- In Hours. 0 implies do not delete files 
AS
BEGIN
    SET       NOCOUNT             ON
    SET       QUOTED_IDENTIFIER   OFF
    SET       ANSI_NULLS          ON 
    DECLARE   @PlanID             uniqueidentifier

    if((@plan_id IS NULL) AND (@plan_name IS NULL))
    BEGIN
      RAISERROR('You must supply the plan name or the plan id.', 16, 1)
      RETURN(1)
    END

    IF (@plan_id IS NULL)
    BEGIN        
        IF (NOT EXISTS (SELECT * 
                        FROM   msdb.dbo.backup_movement_plans
                        WHERE  plan_name = @plan_name ) )
        BEGIN
          RAISERROR('Backup movement plan with this name was not found.', 16, 1)
          RETURN(1)
        END

        IF (SELECT COUNT(*) 
            FROM   msdb.dbo.backup_movement_plans
            WHERE  plan_name = @plan_name) > 1
        BEGIN
          RAISERROR('There are more than one backup movement plans with this name.', 16, 1)
          RETURN(1)
        END

        SELECT @PlanID = plan_id 
        FROM   msdb.dbo.backup_movement_plans
        WHERE  plan_name = @plan_name
    END
    ELSE
    BEGIN
        SELECT @PlanID = @plan_id 
        IF (NOT EXISTS (SELECT * 
                        FROM   msdb.dbo.backup_movement_plans
                        WHERE  plan_id = @plan_id ) )
        BEGIN
          RAISERROR('Backup movement plan with this id.', 16, 1)
          RETURN(1)
        END

        IF (SELECT COUNT(*) 
            FROM   msdb.dbo.backup_movement_plans
            WHERE  plan_id = @plan_id) > 1
        BEGIN
          RAISERROR('There are more than one backup movement plans with this id.', 16, 1)
          RETURN(1)
        END
    END

    IF (EXISTS ( SELECT *
                 FROM msdb.dbo.backup_movement_plan_databases
                 WHERE plan_id = @PlanID AND source_database = @source_db AND destination_database = @dest_db ))
    BEGIN
      RAISERROR('These databases are already included in this plan', 16, 1)
      RETURN(1)
    END
        
    INSERT msdb.dbo.backup_movement_plan_databases
   (plan_id, source_database, destination_database, load_delay, load_all, source_server, retention_period)
    VALUES
          (@PlanID, @source_db, @dest_db, @load_delay, @load_all, @source_server, @retention_period)
END

After creating the infrastructure you can create the transaction log backup copy and load jobs by running the following script. After running the script logshipping started via the created jobs and the process can be monitored by creating and using the following stored procedure.

exec msdb..sp_create_backup_movement_plan 
  @name = "DB01_logshipping",
  @source_dir = "\\PRMDB01\trnlogs", 
  @dest_dir = "D:\logshipping\",
  @sub_dir = "DB01",
  @load_job_freq =30,
  @copy_job_freq = 30

exec msdb..sp_add_db_to_backup_movement_plan 
  @plan_name = "DB01_logshipping",
  @source_db = "DB01", 
  @dest_db = "DB01",
  @load_delay = 10, 
  @load_all = 1,
  @source_server = 'PRMYDB01', 
  @retention_period = 30  --in days

Here is the stored procedure to monitor the log shipping process.

USE msdb
CREATE PROCEDURE dbo.sp_log_ship_status
 @p_svr         varchar( 30 ) = NULL,
 @p_db          varchar( 30 )= NULL
AS
Begin

set nocount on
DECLARE @dest_db char(30),
 @history_id int,
 @time_delta int

CREATE TABLE #table ( destination_db  CHAR(30),
   time_delta INT)

DECLARE log_ship_cursor CURSOR
 FOR SELECT destination_database 
 from backup_movement_plan_databases

OPEN log_ship_cursor

FETCH NEXT FROM log_ship_cursor into @dest_db

WHILE @@FETCH_STATUS = 0
BEGIN
  set nocount on

  select  @history_id = (select max(restore_history_id) 
                           from restorehistory 
                           where destination_database_name = @dest_db)

  select  @time_delta = (select datediff(mi, (select backup_start_date 
                           from backupset 
                           where backup_set_id = (select backup_set_id 
                                                   from restorehistory 
                                                   where restore_history_id = @history_id)), getdate()))

  INSERT INTO #table VALUES( @dest_db, @time_delta)
  FETCH NEXT from log_ship_cursor into @dest_db 
end

close log_ship_cursor
DEALLOCATE log_ship_cursor

SELECT "Primary Srv" = CONVERT(char(30),source_server),
 "Primary DB" = CONVERT(char(30),source_database),
 "Secondary DB" = CONVERT(char(30),destination_database),
 "Delta" = time_delta,
 "Load All" = CASE WHEN (load_all = 0) THEN "No" ELSE "Yes" end,
 "Load Delay" = load_delay,
 "Save Period" = retention_period,
 "Last File Copied" = CONVERT(char(75),last_file_copied),
 "Copy Logged Time" = date_last_copied,
 "Last File Loaded" = CONVERT(char(75),last_file_loaded),
 "Load Logged Time" = date_last_loaded
FROM  msdb..backup_movement_plan_databases,
 #table
WHERE (@p_svr is NULL or source_server like @p_svr)
AND (@p_db is NULL or source_database like @p_db)
AND destination_database = destination_db

drop table #table
END

-------------------------
-- it seems it is working
-------------------------
dbo.log_ship_status
/*
Primary Srv Primary DB      Secondary DB      Delta    Load All Load Delay  Save Period Last File Copied                                 Copy Logged Time        Last File Loaded                               Load Logged Time
----------- --------------- ----------------- -------- -------- ----------- ----------- ------------------------------------------------ ----------------------- -------------------------------------------    -----------------------
crmsbdb01   siebeldb        siebeldb          34       Yes      1           60          \\crmsbdb01\t-logs\siebeldb\siebeldb_tlog_201... 2011-07-13 15:10:01.610 E:\t-logs\siebeldb\siebeldb_tlog_2011071314... 2011-07-13 15:00:12.563
crmsbdb01   IVR_INTEGRATE   IVR_INTEGRATE     34       Yes      1           60          \\crmsbdb01\t-logs\IVR_INTEGRATE\IVR_INTEGRAT... 2011-07-13 15:10:03.313 E:\t-logs\IVR_INTEGRATE\IVR_INTEGRATE_tlog_... 2011-07-13 14:50:07.627
*/

Export problem and the invalid XDB library

2011-06-28T15:08:00.000+03:00

This was about to be a regular export to the filesystem which is running on AIX 5.1 Operating system and the Oracle Database version is 9.2.0.7 and the Oracle client version is 10.2.0.3. But the weird error just popped out and interesting search results came up. Here is the story of the export.

Export: Release 10.2.0.3.0 - Production on Wed Oct 3 11:52:37 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user PARITEM 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user PARITEM 
About to export PARITEM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []
EXP-00000: Export terminated unsuccessfully

It seems there is a problem with the XDB library. When i select from dba_registry to see the components and the statuses, i realized there is a problem with the XML Database component. After investigation and searching the metalink, i came up to the a document which explains how to recreate the XML database (please see the end of the post for the related resources)

--INVALID XML Database component

SQL> col comp_name format a30
SQL> set pagesize 100
SQL> select comp_name, status, version from dba_registry;

COMP_NAME                      STATUS      VERSION
------------------------------ ----------- ------------------------------
Oracle9i Catalog Views         VALID       9.2.0.7.0
Oracle9i Packages and Types    VALID       9.2.0.7.0
Oracle Workspace Manager       VALID       9.2.0.1.0
JServer JAVA Virtual Machine   VALID       9.2.0.7.0
Oracle XDK for Java            VALID       9.2.0.9.0
Oracle9i Java Packages         VALID       9.2.0.7.0
Oracle Text                    VALID       9.2.0.7.0
Oracle XML Database            INVALID     9.2.0.7.0
Spatial                        VALID       9.2.0.7.0
Oracle Ultra Search            VALID       9.2.0.7.0
Oracle Data Mining             VALID       9.2.0.7.0
OLAP Analytic Workspace        UPGRADED    9.2.0.7.0
Oracle OLAP API                UPGRADED    9.2.0.7.0
OLAP Catalog                   VALID       9.2.0.7.0

14 rows selected.

-- Dropping xml database
SQL> @?/rdbms/admin/catnoqm.sql;
SQL> drop trigger sys.xdb_installation_trigger;
SQL> drop trigger sys.dropped_xdb_instll_trigger;
SQL> drop table dropped_xdb_instll_tab;

-- Recreating the xml database
SQL> startup migrate;
SQL> @?/rdbms/admin/catproc.sql;
SQL> @?/rdbms/admin/catqm.sql;
SQL> @?/rdbms/admin/dbmsxsch.sql;
SQL> @?/rdbms/admin/catxdbj.sql; -- only in 9i
SQL> @?/rdbms/admin/xdbpatch;

SQL> select comp_name, status, version from dba_registry;

COMP_NAME                      STATUS      VERSION
------------------------------ ----------- ------------------------------
Oracle9i Catalog Views         VALID       9.2.0.7.0
Oracle9i Packages and Types    VALID       9.2.0.7.0
Oracle Workspace Manager       VALID       9.2.0.1.0
JServer JAVA Virtual Machine   VALID       9.2.0.7.0
Oracle XDK for Java            VALID       9.2.0.9.0
Oracle9i Java Packages         VALID       9.2.0.7.0
Oracle Text                    VALID       9.2.0.7.0
Oracle XML Database            VALID       9.2.0.7.0
Spatial                        VALID       9.2.0.7.0
Oracle Ultra Search            VALID       9.2.0.7.0
Oracle Data Mining             VALID       9.2.0.7.0
OLAP Analytic Workspace        UPGRADED    9.2.0.7.0
Oracle OLAP API                UPGRADED    9.2.0.7.0
OLAP Catalog                   VALID       9.2.0.7.0

14 rows selected.

Re run the export now !...

resources:
http://www.oratransplant.nl/2005/11/22/unable-to-export-char-semantic-102-database/
Note:339938.1 - Full Export From 10.2.0.1 Aborts With EXP-56 ORA-932 (Inconsistent Datatypes) EXP-0
Note:243554.1 - How to Deinstall and Reinstall XML Database (XDB)

The way of opatch 10.2.0.4 Database on AIX

2011-06-16T14:11:00.001+03:00

[oracle@]:/oracle/asmhome1/OPatch > opatch version
Invoking OPatch 10.2.0.5.1

OPatch Version: 10.2.0.5.1

OPatch succeeded.

[oracle@]:/oracle/asmhome1/OPatch > cd
[oracle@]:/oracle > . .profile

[oracle@]:/oracle > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 1 09:48:52 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@]:/oracle > 
[oracle@]:/oracle > . .profile_asm
[YOU HAVE NEW MAIL]
[oracle@]:/oracle > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 1 09:54:58 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown;
ASM diskgroups dismounted
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@]:/oracle > lsnrctl stop

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 01-JUN-2011 09:49:09

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=defbora01)(PORT=1521)))
The command completed successfully

[oracle@]:/oracle/orahome1/OPatch > su - 
root's Password: 
[root@]:/home/root> /usr/sbin/slibclean
[root@]:/home/root>

[oracle@]:/oracle > cd $ORACLE_HOME/OPatch
[oracle@]:/oracle/asmhome1/OPatch > ls
11725015                              jlib                                  opatch.pl
README.txt                            ocm                                   opatchprereqs
crs                                   opatch                                p11725015_10204_AIX5L-cpuapr2011.zip
docs                                  opatch.bat                            p6880880_102000_AIX64-5L-opatch.zip
emdpatch.pl                           opatch.ini
[oracle@]:/oracle/asmhome1/OPatch > cd 11725015 
[oracle@]:/oracle/asmhome1/OPatch/11725015 > opatch napply --skip_subset --skip_duplicate

... 
(output truncated)
...

--------------------------------------------------------------------------------
**********************************************************************
**                       ATTENTION                                  **
**                                                                  **
** Please note that this Patch Installation is                      **
** not complete until all the Post Installation instructions        **
** noted in the Readme accompanying this patch, have been           **
** successfully completed.                                          **
**                                                                  **
**********************************************************************

--------------------------------------------------------------------------------


The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

[oracle@]:/oracle/asmhome1/OPatch/11725015 > 
[oracle@]:/oracle/asmhome1/OPatch/11725015 > opatch lsinv

... 
(output truncated)
...

[oracle@]:/oracle > . .profile
[oracle@]:/oracle/orahome1/OPatch > cd 11725015 
[oracle@]:/oracle/orahome1/OPatch/11725015 > opatch napply --skip_subset --skip_duplicate

...
(output truncated)
...

[oracle@]:/oracle/orahome1/OPatch/11725015 > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 1 10:20:14 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2087480 bytes
Variable Size             637535688 bytes
Database Buffers         2566914048 bytes
Redo Buffers               14688256 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/catbundle.sql cpu apply
SQL> @?/rdbms/admin/utlrp.sql

[oracle@]:/oracle/orahome1/cpu/view_recompile > ls -l
total 24
-rwxr-xr-x    1 oracle   dba            2095 Jul 10 2008  recompile_precheck_jan2008cpu.sql
-rwxr-xr-x    1 oracle   dba            5143 Jul 10 2008  view_recompile_jan2008cpu.sql
[oracle@]:/oracle/orahome1/cpu/view_recompile > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 1 10:34:16 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @recompile_precheck_jan2008cpu.sql;

Running precheck.sql...

Number of views to be recompiled :2226
-----------------------------------------------------------------------

Number of objects to be recompiled :4347
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.

SQL> @view_recompile_jan2008cpu.sql;

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


1 row created.


Commit complete.

No. of Invalid Objects is :1848
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_CORET_01Jun2011_11_47_53.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2087480 bytes
Variable Size             637535688 bytes
Database Buffers         2566914048 bytes
Redo Buffers               14688256 bytes
Database mounted.
Database opened.
SQL> 

SQL> select bundle_series, action, version from dba_registry_history;

BUNDLE_SERIES          ACTION  VERSION
---------------------- ---------------- --------
CPU                    APPLY CPU 10.2.0.4


SQL> SELECT * FROM registry$history where ID = '6452863';

SQL> select bundle_series, action, version from registry$history where ID = '6452863';

BUNDLE_SERIES                  ACTION     VERSION
------------------------------ ---------- ------------------------------
CPU

Error in checking condition of instance on node

2010-12-30T09:36:00.005+02:00

After rebooting both rac nodes srvctl started to complain about the condition of the second node of my cluster.


[oracle@EPRHEL6 admin]$ srvctl status database -d orcl
Instance ORCL1 is running on node eprhel5
PRKO-2015 : Error in checking condition of instance on node: eprhel6

[oracle@EPRHEL6 admin]$ sqlplus system/password@ORCL2

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 00:03:11 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

srvctl also complains when i was trying to start the instance on the second node. So i decided to start the instance manually by using sqlplus.

[oracle@EPRHEL6 admin]$ sqlplus "/ as sysdba"


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 00:03:24 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> startup;
Oracle instance started.

Total System Global Area 599785472 bytes
Fixed Size     2022600 bytes
Variable Size   188744504 bytes
Database Buffers  402653184 bytes
Redo Buffers     6365184 bytes
Database mounted.
Database opened.
SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

[oracle@EPRHEL6 admin]$ sqlplus system/password@ORCL2

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 00:04:18 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

It seems there is not any problem with the instance itself. Sqlplus barely connects to the instance ORCL2. There should be a problem about the way of communication between srvctl and the instance.

[oracle@EPRHEL6 admin]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    ONLINE    ONLINE    eprhel5     
ora....L2.inst application    ONLINE    UNKNOWN   eprhel6     
ora.ORCL.db    application    ONLINE    ONLINE    eprhel5     
ora....SM1.asm application    ONLINE    ONLINE    eprhel5     
ora....L5.lsnr application    ONLINE    ONLINE    eprhel5     
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....SM2.asm application    ONLINE    ONLINE    eprhel6     
ora....L5.lsnr application    OFFLINE   OFFLINE               
ora....L6.lsnr application    ONLINE    ONLINE    eprhel6     
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6     

[oracle@EPRHEL6 admin]$ srvctl start listener -n EPRHEL6

[oracle@EPRHEL6 admin]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    ONLINE    ONLINE    eprhel5     
ora....L2.inst application    ONLINE    UNKNOWN   eprhel6     
ora.ORCL.db    application    ONLINE    ONLINE    eprhel5     
ora....SM1.asm application    ONLINE    ONLINE    eprhel5     
ora....L5.lsnr application    ONLINE    ONLINE    eprhel5     
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....SM2.asm application    ONLINE    ONLINE    eprhel6     
ora....L5.lsnr application    OFFLINE   OFFLINE               
ora....L6.lsnr application    ONLINE    ONLINE    eprhel6     
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6  

[oracle@EPRHEL6 admin]$ sqlplus system/password@ORCL1

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 00:04:35 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show parameter listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
remote_listener                      string      LISTENERS_ORCL
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

I think there is a problem with listener configuration or the listener resource itself. But everything seems fine except that OFFLINE resource. After searching google a little bit, i found a solution indicates listener configuration. I decided to recreate the listeners with netca. I will first delete listener named LISTENER from both ASM and DB homes using netca and then recreate them only using DB home. Maybe this resolves the problem.

My action plan is first stop all asm and db instances. Manually remove that OFFLINE listener which is very confusing. Remove all the listener configuration from the cluster with netca and recreate using db home. Here we go.

[oracle@EPRHEL6 db]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-DEC-2010 00:22:16

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_EPRHEL6
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                27-DEC-2010 00:02:31
Uptime                    0 days 0 hr. 19 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/asm/network/admin/listener.ora
Listener Log File         /oracle/product/asm/network/log/listener_eprhel6.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.4.226)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.4.246)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "ORCL" has 2 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Instance "ORCL2", status READY, has 2 handler(s) for this service...
Service "ORCLXDB" has 2 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_XPT" has 2 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Instance "ORCL2", status READY, has 2 handler(s) for this service...
The command completed successfully

[oracle@EPRHEL6 db]$ srvctl stop database -d orcl
[oracle@EPRHEL6 db]$ srvctl stop asm -n EPRHEL5
[oracle@EPRHEL6 db]$ srvctl stop asm -n EPRHEL6
[oracle@EPRHEL6 db]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    OFFLINE   OFFLINE               
ora....L2.inst application    OFFLINE   OFFLINE               
ora.ORCL.db    application    OFFLINE   OFFLINE               
ora....SM1.asm application    OFFLINE   OFFLINE               
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....SM2.asm application    OFFLINE   OFFLINE               
ora....L5.lsnr application    OFFLINE   OFFLINE               
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6     

[oracle@EPRHEL6 db]$ crs_getperm ora.eprhel6.LISTENER_EPRHEL5.lsnr
Name: ora.eprhel6.LISTENER_EPRHEL5.lsnr
owner:oracle:rwx,pgrp:dba:rwx,other::r--,
[oracle@EPRHEL6 db]$ crs_unregister ora.eprhel6.LISTENER_EPRHEL5.lsnr
[oracle@EPRHEL6 db]$ crs_profile -delete ora.eprhel6.LISTENER_EPRHEL5.lsnr
CRS-0170: The resource 'ora.eprhel6.LISTENER_EPRHEL5.lsnr' doesn't exist.

[oracle@EPRHEL6 db]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    ONLINE    ONLINE    eprhel5     
ora....L2.inst application    ONLINE    ONLINE    eprhel6     
ora.ORCL.db    application    ONLINE    ONLINE    eprhel5     
ora....SM1.asm application    ONLINE    ONLINE    eprhel5     
ora....L5.lsnr application    ONLINE    ONLINE    eprhel5     
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....SM2.asm application    ONLINE    ONLINE    eprhel6     
ora....L6.lsnr application    ONLINE    ONLINE    eprhel6     
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6     
[oracle@EPRHEL6 db]$ srvctl status database -d orcl
Instance ORCL1 is running on node eprhel5
Instance ORCL2 is running on node eprhel6
[oracle@EPRHEL6 db]$

It seems problem is solved.

Relocating CRS Resource

2010-12-28T13:50:00.005+02:00

I have installed a one node RAC 10gR2 on RHEL5.5 for test purposes (my 10gR2 rac on RHEL5.5 vmware installation notes). After adding the second node to the cluster successfully, i realized that the new nodes vip resource is running on the first node. I have seen this problem before on a solaris system but i hadnt got any time to write about that.

[root@EPRHEL6]# crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

[root@EPRHEL6]# crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    ONLINE    ONLINE    eprhel5     
ora.ORCL.db    application    ONLINE    ONLINE    eprhel5     
ora....SM1.asm application    ONLINE    ONLINE    eprhel5     
ora....L5.lsnr application    ONLINE    ONLINE    eprhel5     
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel5     

[root@EPRHEL6]# ping eprhel6-vip
PING eprhel6-vip (172.28.4.226) 56(84) bytes of data.
64 bytes from eprhel6-vip (172.28.4.226): icmp_seq=1 ttl=64 time=2.28 ms
64 bytes from eprhel6-vip (172.28.4.226): icmp_seq=2 ttl=64 time=1.03 ms
64 bytes from eprhel6-vip (172.28.4.226): icmp_seq=3 ttl=64 time=0.131 ms

[root@EPRHEL6]# ifconfig -a
eth0      Link encap:Ethernet  HWaddr 00:0C:29:DE:D8:FD  
inet addr:172.28.4.246  Bcast:172.28.4.255  Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fede:d8fd/64 Scope:Link
UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
RX packets:36798 errors:0 dropped:0 overruns:0 frame:0
TX packets:13478 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000 
RX bytes:21057458 (20.0 MiB)  TX bytes:10660215 (10.1 MiB)

eth1      Link encap:Ethernet  HWaddr 00:0C:29:DE:D8:07  
BROADCAST MULTICAST  MTU:1500  Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000 
RX bytes:0 (0.0 b)  TX bytes:0 (0.0 b)

[root@EPRHEL5]# ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 00:0C:29:B7:92:45  
inet addr:172.28.4.245  Bcast:172.28.4.255  Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:feb7:9245/64 Scope:Link
UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
RX packets:9074707 errors:0 dropped:0 overruns:0 frame:0
TX packets:1212938 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000 
RX bytes:1173926429 (1.0 GiB)  TX bytes:1041963477 (993.6 MiB)

[root@EPRHEL5]# ifconfig eth0:1
eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:B7:92:45  
inet addr:172.28.4.225  Bcast:172.28.4.255  Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

[root@EPRHEL5]# ifconfig eth0:2
eth0:2    Link encap:Ethernet  HWaddr 00:0C:29:B7:92:45  
inet addr:172.28.4.226  Bcast:172.28.4.255  Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

I suppose this is because of the network settings of the newly added node and somehow crs could not assign the vip ip address to the nic card. crs_relocate may work on this.

[root@EPRHEL5]# crs_relocate ora.eprhel6.vip
Attempting to stop `ora.eprhel6.vip` on member `eprhel5`
Stop of `ora.eprhel6.vip` on member `eprhel5` succeeded.
Attempting to start `ora.eprhel6.vip` on member `eprhel6`
Start of `ora.eprhel6.vip` on member `eprhel6` succeeded.
[root@EPRHEL5]# crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    ONLINE    ONLINE    eprhel5     
ora.ORCL.db    application    ONLINE    ONLINE    eprhel5     
ora....SM1.asm application    ONLINE    ONLINE    eprhel5     
ora....L5.lsnr application    ONLINE    ONLINE    eprhel5     
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6  

Now ifconfig on my new node should show the vip ip address information.

[root@EPRHEL6 network-scripts]# ifconfig -a
eth0      Link encap:Ethernet  HWaddr 00:0C:29:DE:D8:FD  
inet addr:172.28.4.246  Bcast:172.28.4.255  Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fede:d8fd/64 Scope:Link
UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
RX packets:36798 errors:0 dropped:0 overruns:0 frame:0
TX packets:13478 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000 
RX bytes:21057458 (20.0 MiB)  TX bytes:10660215 (10.1 MiB)

eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:DE:D8:FD  
inet addr:172.28.4.226  Bcast:172.28.4.255  Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

Basic Team Coding Extension for Oracle

2010-11-13T17:24:00.020+02:00

Our development group is complaining about override each other 's codes by recompiling the same PLSQL program unit that is actively in development with any other developer. TOAD has builtin Team Coding functionality but there are developers not using TOAD in the development department.

I developed a tiny Team Coding functionality works in the database with using a DDL trigger. By this BEFORE CREATE Trigger developers are not able to alter any program unit by without first checking it out. You can find a small example below.

Without first declaring in which schema you want Team Coding should work everything works as they should be.

connect SYSTEM/***@ORCL11G

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;

--FUNCTION FNC_TEST compiled

After declaring the schema you want to enable Team Coding.

exec PCK_TCODE_CONTROL.set_param('SCHEMA_NAME','SYSTEM','INSERT');
--anonymous block completed

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;

--ORA-20501: You are not allowed to compile the code! 
--           First PCK_TCODE_CONTROL.CHECKOUT the unit.

exec PCK_TCODE_CONTROL.checkout('FNC_TEST','SYSTEM');
--anonymous block completed

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;
--FUNCTION FNC_TEST compiled

connect u_epeker/***@ORCL11G

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;

--ORA-20500: Unit Locked by another user! 
--           Check PCK_TCODE_CONTROL.CHECK_AVAILIBILITY function

select PCK_TCODE_CONTROL.check_avail('FNC_TEST','SYSTEM') from dual;
/*
"PCK_TCODE_CONTROL.CHECK_AVAIL('FNC_TEST','SYSTEM')"
"TCODE: SYSTEM.FNC_TEST is checked out by SYSTEM on 13.11.2010 19:26:30"
*/

connect SYSTEM/***@ORCL11G

exec PCK_TCODE_CONTROL.checkin('FNC_TEST','SYSTEM');
--anonymous block completed

connect U_EPEKER/***@ORCL11G

select PCK_TCODE_CONTROL.check_avail('FNC_TEST','SYSTEM') from dual;
--null

exec PCK_TCODE_CONTROL.checkin('FNC_TEST','SYSTEM');
--anonymous block completed

select PCK_TCODE_CONTROL.check_avail('FNC_TEST','SYSTEM') from dual;
/*
"PCK_TCODE_CONTROL.CHECK_AVAIL('FNC_TEST','SYSTEM')"
"TCODE: SYSTEM.FNC_TEST is checked out by U_EPEKER on 13.11.2010 19:33:53"
*/

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;

--FUNCTION FNC_TEST compiled

As you can see people are not able to override their codes hence this functionality exists. You can add this functionality to you database by running the following script. This small script does not remember the versions of the program units. Anyone can develop version history functionality on top of this small script and repository or maybe I can handle this version history in another version.

You can find the script here

Oracle SQL Fundamentals Course Notes

2010-10-28T09:18:00.005+03:00

I was in Oracle SQL Course for last 4 days. The course went like a hands on lab so I'd like to share my course notes on Oracle SQL fundamentals.

Links:
Course Notes
Course Details

Duplicate Database with RMAN is as easy as it should be

2010-05-31T14:26:00.017+03:00

Duplicating a database with RMAN saves you from an ordinary routine consists of a hot backup, restore, recover and open the aux database procedure.



C:\Users\dtepeker> set ORACLE_SID=ORCL2
C:\Users\dtepeker> sqlplus "/ as sysdba

SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> create pfile='C:\oracle\product\initORCL2.ora' from spfile;

File created.

SQL>

C:\Users\dtepeker> more C:\oracle\product\initORCL2.ora
orcl2.__db_cache_size=369098752
orcl2.__java_pool_size=4194304
orcl2.__large_pool_size=4194304
orcl2.__shared_pool_size=142606336
orcl2.__streams_pool_size=8388608
*.audit_file_dest='C:\oracle\product\admin\ORCL2\adump'
*.background_dump_dest='C:\oracle\product\admin\ORCL2\bdump'
*.compatible='10.2.0.3.0'
*.control_files='C:\oracle\product\oradata\ORCL2\control01.ctl'
*.core_dump_dest='C:\oracle\product\admin\ORCL2\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ORCL2'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL2XDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=268435456
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\admin\ORCL2\udump'

*.db_file_name_convert=(C:\oracle\product\oradata\ORCL,C:\oracle\product\oradata\ORCL2)
*.log_file_name_convert=(C:\oracle\product\oradata\ORCL,C:\oracle\product\oradata\ORCL2)

SQL> create spfile from pfile='C:\oracle\product\initORCL2.ora';

File created.

SQL>

C:\Users\dtepeker> more C:\oracle\product\db10g\network\admin\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\db10g\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

### duplicate/destination db
ORCL2 =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dtl1optim1)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = ORCL2)
  )
)
### target/source db
ORCL =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dtl1optim1)(PORT = 1521))
  (CONNECT_DATA =
    (SID = ORCL)
  )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
  (CONNECT_DATA =
    (PRESENTATION = RO)
    (SID = PLSExtProc)
  )
)

C:\oracle\product\oradata\ORCL2>del *.dbf

C:\oracle\product\oradata\ORCL2>dir
Volume in drive C has no label.
Volume Serial Number is 3A6A-99E9

Directory of C:\oracle\product\oradata\ORCL2

05/31/2010  12:06 PM              .
05/31/2010  12:06 PM              ..
05/31/2010  11:58 AM         7,389,184 CONTROL01.CTL
05/27/2010  04:58 PM             1,317 CONTROLFILE.CTL
05/27/2010  04:52 PM               854 initORCL2.ora
04/20/2010  03:27 PM              ORCL2
05/31/2010  11:54 AM        52,429,312 REDO01.LOG
05/31/2010  11:54 AM        52,429,312 REDO02.LOG
05/31/2010  11:54 AM        52,429,312 REDO03.LOG
             6 File(s)    164,679,291 bytes
             3 Dir(s)  92,947,480,576 bytes free

C:\oracle\product\oradata\ORCL2>del *.log

C:\oracle\product\oradata\ORCL2>del *.ctl

C:\oracle\product\oradata\ORCL2>dir
Volume in drive C has no label.
Volume Serial Number is 3A6A-99E9

Directory of C:\oracle\product\oradata\ORCL2

05/31/2010  12:06 PM              .
05/31/2010  12:06 PM              ..
05/27/2010  04:52 PM               854 initORCL2.ora
04/20/2010  03:27 PM              ORCL2
             1 File(s)            854 bytes
             3 Dir(s)  93,112,172,544 bytes free

C:\oracle\product\oradata\ORCL2>

C:\oracle\product\oradata\ORCL2>rman target sys/password@ORCL auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon May 31 12:07:32 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1235781100)
connected to auxiliary database: ORCL2 (not mounted)

RMAN> duplicate target database to ORCL2;

Starting Duplicate Db at 31-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

contents of Memory Script:
{
set until scn 23097370;
set newname for datafile 1 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF";
set newname for datafile 5 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 31-MAY-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy recid=20 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_SYSTEM_5ZWW6PW1_.DBF
destination for restore of datafile 00001: C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002
input datafile copy recid=21 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_UNDOTBS1_5ZWW4TN9_.DBF
destination for restore of datafile 00002: C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00002
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003
input datafile copy recid=22 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_SYSAUX_5ZWW5XQX_.DBF
destination for restore of datafile 00003: C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00003
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004
input datafile copy recid=19 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_USERS_5ZWW7937_.DBF

destination for restore of datafile 00004: C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00004
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005
input datafile copy recid=18 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_TS_STREA_5ZWW763V_.DBF
destination for restore of datafile 00005: C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00005
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF
Finished restore at 31-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET WE8ISO8859P9

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=720446949 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=720446950 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=720446950 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=720446950 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF

contents of Memory Script:
{
set until scn 23097370;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 31-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

starting media recovery

archive log thread 1 sequence 416 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00416_0708000366.001
archive log thread 1 sequence 417 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00417_0708000366.001
archive log thread 1 sequence 418 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00418_0708000366.001
archive log thread 1 sequence 419 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00419_0708000366.001
archive log thread 1 sequence 420 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00420_0708000366.001
archive log thread 1 sequence 421 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00421_0708000366.001
archive log thread 1 sequence 422 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00422_0708000366.001
archive log thread 1 sequence 423 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00423_0708000366.001
archive log thread 1 sequence 424 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00424_0708000366.001
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00416_0708000366.001 thread=1 sequence=416
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00417_0708000366.001 thread=1 sequence=417
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00418_0708000366.001 thread=1 sequence=418
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00419_0708000366.001 thread=1 sequence=419
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00420_0708000366.001 thread=1 sequence=420
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00421_0708000366.001 thread=1 sequence=421
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00422_0708000366.001 thread=1 sequence=422
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00423_0708000366.001 thread=1 sequence=423
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00424_0708000366.001 thread=1 sequence=424
media recovery complete, elapsed time: 00:00:29
Finished recover at 31-MAY-10

contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 536870912 bytes

Fixed Size 1297724 bytes
Variable Size 159384260 bytes
Database Buffers 369098752 bytes
Redo Buffers 7090176 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET WE8ISO8859P9

contents of Memory Script:
{
set newname for tempfile 1 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF";
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF";
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF";
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to C:\ORACLE\PRODUCT\ORADATA\ORCL2\TEMP01.DBF in control file

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF recid=1 stamp=720446995

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF recid=2 stamp=720446995

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF recid=3 stamp=720446995

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF recid=4 stamp=720446996

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=720446995 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=720446995 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=720446995 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=720446996 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 31-MAY-10

RMAN>

Related:
Oracle Database Backup and Recovery Advanced Users Guide
Oracle-Base.com

Oracle Enterprise Manager gets bigger with Sun Opscenter

2010-05-02T00:20:00.000+03:00

I was on the Sun Oracle event here in Istanbul, Turkey on 28.04.2010. The main course was about engaging Oracle with Sun.

The most interesting part for me was the future of Oracle Enterprise Manager. There were a lot of clue for it is going to get bigger, not only for database and application server management but also in virtual machine (both on Solaris and Enterprise linux) and operating system management (Solaris, Redhat Linux and Suse Linux).

Oracle has already began to integrate Sun Opscenter to the Oracle Enterprise Manager. Main idea is single handed management of a datacenter with Oracle Enterprise Manager.

There are some links about this topic.

http://www.oracle.com/oms/enterprisemanager11g/webcast-067871.html
http://www.oracle.com/us/products/enterprise-manager/index.html

Google Analytics works better than i expected

2010-04-27T10:18:00.018+03:00

I installed Google Analytics site script to my blog two weeks ago and i received more than i imagine. Here are some report supplied by the dashboard today.

At first sight this is an expected result like visits, pagevisits and pages/visit ratio and so on. On the visitors tab of the dashboard you can obtain lots of information about the users locations and technical specs.

You can also investigate which search engine hits the site? and also the keywords. Another nice feature is you can also see which contents (in this case which posts) hit by the readers mostly? and with which keywords :)

As can be seen from the results Oracle Grid Custom Reports is the most visited post and Homepage follows it very closely. It seems Oracle Enterprise Manager Grid Control posts are mostly interested subject. Streams comes with the SQL Server Logical Consistency Based IO Error post which is a real pain for the admins.

Unfortunately there is no way (or I couldnt find) to share the reports anonymously like GoogleNotes or GoogleDocs. I hope this would be a new feature in a new version.

Some links:
http://www.google.com/analytics
Interesting post from google to google analytics blog

Schema replication with Oracle Streams

2010-04-02T15:37:00.027+03:00

I tested table replication with Oracle Streams before. And now i used Schema Replication with an OLTP Tester schema. The source database is 10.2.0.4 on a windows OS and the destination is 11.1.0.7 database on same host with a windows 2008 Server. I faced a couple of problems listed at the end of this post. The purpose of the post is setting up a Schema replication using streams. And start the OLTP test and monitor the streams processes. Piece of cake!.

Lets start with the schema replication with the MAINTAIN_SCHEMAS which is the easiest way. Streams does the rest of it (creating queues, captre, propagation and apply processes.. etc. ). But before running streams packages there are some preconfiguration of the database that has to be done.

- Enabling Archivelog
- Preconfiguring Oracle Streams

And after all we should be ready for streams replication.
BEGIN

DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names                 => 'U_EPEKER',
source_directory_object      => 'DATA_PUMP_DIR',
destination_directory_object => 'DATA_PUMP_DIR',
source_database              => 'ORCL',
destination_database         => 'ORCL11G',
perform_actions              => TRUE,
capture_name                 => 'CAPTURE_SCHEMA_U_EPEKER',
--capture_queue_table        => NULL,
capture_queue_name           => 'QUEUE_CAPT_SCH_U_EPEKER',
capture_queue_user           => 'U_STREAMS_ADM',
propagation_name             => 'PROP_SCH_U_EPEKER',
apply_name                   => 'APPLY_SCH_U_EPEKER',
--apply_queue_table          => NULL,
apply_queue_name             => 'QUEUE_APP_SCH_U_EPEKER',
apply_queue_user             => 'U_STREAMS_ADM',       
bi_directional               => FALSE,
include_ddl                  => TRUE,
instantiation                => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK);
END;

In case of any error following dba views can be queried and see which script has an error or in case of any hopeless situation you can recover the operation and remove the streams configuration and re-run the maintain_schemas after overcome any misconfiguration.


select * from DBA_RECOVERABLE_SCRIPT_BLOCKS;
select * from DBA_RECOVERABLE_SCRIPT_ERRORS;
execute DBMS_STREAMS_ADM.RECOVER_OPERATION('3A53A54E70764958BA0AFA68DAC0C7F0','PURGE');
exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;
-- and on the remote server
exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION@ORCL11G;

Checking the streams processes if they are set all right after the maintain_schemas procedure.


select capture_name,
       queue_name,
       rule_set_name,
       source_database,
       status 
  from dba_capture;
/*
CAPTURE_NAME                QUEUE_NAME              RULE_SET_NAME   SOURCE_DATABASE STATUS
------------                ----------              -------------   --------------- ------
CAPTURE_SCHEMA_U_EPEKER     QUEUE_CAPT_SCH_U_EPEKER RULESET$_12     ORCL            ENABLED
*/

select propagation_name,
      source_queue_name,
      destination_queue_name,
      destination_dblink,
      rule_set_name,
      status
 from dba_propagation;
/*
PROPAGATION_NAME    SOURCE_QUEUE_NAME           DESTINATION_QUEUE_NAME  DESTINATION_DBLINK  RULE_SET_NAME
---------------     ----------------            ---------------------   -----------------   -------------
PROP_SCH_U_EPEKER   QUEUE_CAPT_SCH_U_EPEKER     QUEUE_APP_SCH_U_EPEKER  ORCL11G             RULESET$_9
*/

select sid,
      serial#,
      capture_name,
      state
 from v$streams_capture;
/*
SID     SERIAL#     CAPTURE_NAME                STATE
---     ------      ------------                -----
128     8           CAPTURE_SCHEMA_U_EPEKER    CAPTURING CHANGES
*/

Everything seems operational in the capture side. I need to check if apply process also created and runs smoothly.


select apply_name,
      queue_name,
      apply_captured,
      rule_set_name,
      status
 from dba_apply@ORCL11G;
/*
APPLY_NAME          QUEUE_NAME              APPLY_CAPTURED      RULE_SET_NAME   STATUS
---------          ----------              --------------      -------------   ------
APPLY_SCH_U_EPEKER  QUEUE_APP_SCH_U_EPEKER  YES                 RULESET$_6      ENABLED
*/

select sid,
      serial#,
      state,
      apply#,
      apply_name,
      total_applied,
      total_errors
 from v$streams_apply_coordinator@ORCL11G;
/*
SID SERIAL# STATE   APPLY#  APPLY_NAME
--- ------  -----   -----   ----------
116 19550   IDLE    1       APPLY_SCH_U_EPEKER
*/

I have created the schema before which includes an OLTP Simulator procedures. I am planning to share this schema in this blog further. Anyway, here is the script to create the schema: OLTP Simulator Script

Now I will use the P_CREATE_REPOS procedure to populate the schema. I hope streams is going to replicate the transactions. This create procedure will create 5 groups and 10 user each group. After that it will going to create 10 pages for each user.

exec U_EPEKER.P_CREATE_REPOS(5,10,1);

After executing this procedure there should be 5 groups and 10 users in each group and total 50 pages for 1 page each user.

select 'source ' || count(*) as user_count from u_epeker.t_user
union all
select 'destination ' || count(*) as user_count from u_epeker.t_user@ORCL11G;
/*
USER_COUNT
source 50
destination 50
*/

select 'source ' || count(*) as group_count from u_epeker.t_group
union all
select 'destination ' || count(*) as group_count from u_epeker.t_group@ORCL11G;
/*
GROUP_COUNT
source 5
destination 5
*/

select 'source ' || count(*) as page_count from u_epeker.t_pages
union all
select 'destination ' || count(*) as page_count from u_epeker.t_pages@ORCL11G;
/*
PAGE_COUNT
source 50
destination 50
*/

Seems everything goes fine. Now i will simulate an OLTP function in the source database and monitor the streams processes. For this I will use the P_CREATE_JOBS procedure of the OLTP Simulator schema and it will implicitly call the P_SIMULATE_REQUEST procedure sequentially.

-- with U_EPEKER user
exec U_EPEKER.P_CREATE_JOBS(20,1,'SIMULATE_JOBS',1);

With these parameter this procedure should create 20 jobs all intervals are 1 second means we are filling up T_VISIT_STATS_DETAILED and yes it works. Our replication should also work.


select count(*) from U_EPEKER.T_VISIT_STATS_DETAILED
union all
select count(*) from U_EPEKER.T_VISIT_STATS_DETAILED@ORCL11G;
/*
COUNT(*)
-------
385
376

on the second run

COUNT(*)
-------
385
385
*/

so it comes a few seconds behind (the time for shipping the logs i think). What about the capture and the apply processes and i can see TOTAL_MESSAGED_CAPTURED and the TOTAL_APPLIED values are getting higher.


select state, capture_name, total_messages_captured  from v$streams_capture;
/*
STATE,CAPTURE_NAME,TOTAL_MESSAGES_CAPTURED
CAPTURING CHANGES,CAPTURE_SCHEMA_U_EPEKER,112817
*/

select state, apply_name, total_applied from v$streams_apply_coordinator@ORCL11G;
/*
STATE,APPLY_NAME,TOTAL_APPLIED
IDLE,APPLY_SCH_U_EPEKER,753
*/

Bonus: Excluding a table from the schema replication, it works!.


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES
(
table_name      => 'u_epeker.t_visit_stats_detailed',
streams_type    => 'capture',
streams_name    => 'capture_schema_u_epeker',
queue_name      => 'queue_capt_sch_u_epeker',
include_dml     => true,
include_ddl     => true,
source_database => 'orcl',
inclusion_rule  => false  --specifies the negative rule set
);
END;

exec DBMS_RULE_ADM.DROP_RULE('T_VISIT_STATS_DETAILED50',true);
exec DBMS_RULE_ADM.DROP_RULE('T_VISIT_STATS_DETAILED51',true);

I can drop the simulation jobs and streams configuration for now.


-- with u_epeker
exec U_EPEKER.P_DROP_JOBS;
exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;
-- and on the remote server
exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION@ORCL11G;

I faced some problem while creating this schema replication configuration with streams.

The weird problem was, cannot shipping the logs to the destination from the source. When I examine the alert.log file of the source database i saw lots of ORA-01031 Insufficient Privileges.
After a little seach on the otn i solved the problem with createing the passwordfile from scratch with orapwd. Strange problem and i still dont know the underlying reason, anyway it is solved.

While instantiation i got an error. As this is a schema replication dont forget to create the same tablespace on the destination or else user/schema creation fails.

And last, an ORA-00600 problem which is ORA-00600: internal error code, arguments: [kwqbmcrcpts101], [], [], [], [], [], [], [] . I figured this problem after i dropped all streams configuration from the both databases and after 2 days i released that host is run out of the disk space. I found that there are lots of dump files under bdump. After a search on otn, i think i hit a 10.2.0.3 bug which occurs on streams environment. As this is a test environment i bounced the db. The problem is solved as i have already deleted streams configuration. the subject on oracle forums

"PAUSED" state for streams capture process

2010-04-01T11:05:00.010+03:00

My test replication somehow got broken and does not apply changes to the target database (Seem a new challange for me!) First thing i checked was streams capture process and i saw the weird message following.

select sid,
serial#,
capture_name,
state
from v$streams_capture;

/*
SID SERIAL# CAPTURE_NAME STATE
-- ------- ------------ -----
121 1524 CAPTURE_SCHEMA_U_EPEKER PAUSED FOR FLOW CONTROL
*/

Now if capture process is stopped, may be i need to check what is going on the apply process. But it seems nothing is received by the capture process. I think the problem should be on the capture side.

select sid,
serial#,
state,
apply#,
apply_name,
total_applied,
total_errors
from v$streams_apply_coordinator@ORCL11G;

/*
SID SERIAL# STATE APPLY# APPLY_NAME TOTAL_APPLIED TOTAL_ERRORS
--- ------ ---- ------ ----------- ------------- ------------
110 4208 IDLE 1 APPLY_SCH_U_EPEKER 0 0
*/

I still didnt understand anything from this message "PAUSED FOR FLOW CONTROL". I did a little search on Google and the i come to Oracle Documentation as always. It seems i need to check the streams_pool_size parameter.

select queue_name, sender_name, num_msgs, unbrowsed_msgs, memory_usage, publisher_state from V$BUFFERED_PUBLISHERS;

/*
QUEUE_NAME SENDER_NAME NUM_MSGS UNBROWSED_MSGS MEMORY_USAGE PUBLISHER_STATE
--------- ----------- -------- -------------- ----------- ---------------
QUEUE_CAPT_SCH_U_EPEKER CAPTURE_SCHEMA_U_EPEKER 15057 15057 87 IN FLOW CONTROL: TOO MANY UNBROWSED MESSAGES
*/

If the memory usage stands for MB which seems 87, then there could be a problem. As with the following invesitgation it seems ASSM gives 56Mb to streams_pool_size and it seems there could be a lack of memory. I decided to increase the streams_pool_size to 200Mb.

select name, value from v$parameter where name = 'streams_pool_size';

/*
NAME VALUE
---- -----
streams_pool_size 0
*/

select name, value/1024/1024 as mb from v$parameter where name = 'sga_target';

/*
NAME MB
---- --
sga_target 512
*/

select pool, round(sum(bytes)/1024/1024) as MB from v$sgastat group by pool;
/*
POOL MB
---- --
140
java pool 12
streams pool 56
shared pool 300
large pool 4
*/

alter system set sga_target=1G scope=spfile;
alter system set streams_pool_size=200M scope=spfile;
shutdown immediate;
startup;

As soon as the database opened capture process is up again. And the publishers state is now "PUBLISHING MESSAGES"

select sid,
serial#,
capture_name,
state
from v$streams_capture;
/*
SID SERIAL# CAPTURE_NAME STATE
--- ------ ------------ -----
151 1 CAPTURE_SCHEMA_U_EPEKER CAPTURING CHANGES
*/

select queue_name, sender_name, num_msgs, unbrowsed_msgs, memory_usage, publisher_state from V$BUFFERED_PUBLISHERS;
/*
QUEUE_NAME SENDER_NAME NUM_MSGS UNBROWSED_MSGS MEMORY_USAGE PUBLISHER_STATE
---------- ----------- -------- -------------- ------------ ---------------
QUEUE_CAPT_SCH_U_EPEKER CAPTURE_SCHEMA_U_EPEKER 44 0 5 PUBLISHING MESSAGES
*/

Google Docs instead of Google Notes

2010-03-26T19:36:00.010+02:00

I used to take notes on Google Notebook which is not supported any more and has some constraints (especially on note size).

Google Notebook - Shared Notes

From now on I decided to use Google Docs. Here are my shared documents.

Oracle Documents
UNIX Documents

There are scripts and documents more than i shared, but i have to reformat them before sharing. I hope i will do this handling on my spare times.

SA200-S10 - Solaris Admin I Course Notes

2010-03-22T08:22:00.008+02:00

Last week i accomplished Sun Solaris 10 Admin I (SA200-S10) training for 5 days.

I used to admin RHEL4 and RHEL5 operating systems for years but Solaris is far beyond. Especially ZFS and filesystem snapshot utilities are pretty effective as well as service management features.

Here are my course notes

And the course details

It is good to be open and downloadable for free

testing SQL Server 2005 partitioning option

2010-02-23T11:33:00.007+02:00

This post is a version of the pervious "SQL Server Partitioning Option" explanatory presentation of mine which i wrote for a friend of mine. The presentation starts with what is partitioning, why to use table partitions, how to use the partitons and advantages. All the chapters basis on one test case which consists of partitioning a table and examine the IO performance before and after the partition operation.

What is partitioning
Partitioning is basically aggregation of the data by its meaning or value on different physical structures and this also brings retrieve times to acceptible values.

As this option can be used for archiving needs it is usually used for faster reporting and query performances.

Why to use partitioning
The mostly used area of the partitioning is increasing full table scan performances. One of the most obvious reason of the unavoidable full table scans which are; accessing more than certain amount of table data (There are different methods to avoid or increasing performance on full table scans but they are not in the scope of this article while we are concerning partitioning option of Microsoft SQL Server).

As well as performance considerations, partitioning also helps archiving in large tables especially log tables that are stored on daily/monthly or even yearly basis. There are lots of structures that holds the older data of the table (say older than 6 months) on SATA disks which are cheaper and on compressed partitions.

How to use partitioning
I have used a Windows Server 2003 Standart Edition Operating system with SQL Server 2005 Enterprise edition (By the way SQL Server Standart Edition does not support partitioning). Recommended hardware requirements were 1Gb of ram 1 CPU and minimum 10Gb of disk space which i installed my virtual machine with these configurations.

First we need a comparison sql query which I used a standart built-in sql server Adventureworks database.

select 
cat.name as category_name, 
subcat.name as subcategory_name, 
prd.* 
from 
AdventureWorks.Production.Product prd LEFT JOIN 
AdventureWorks.Production.ProductSubCategory subcat ON
(prd.ProductSubcategoryID=subcat.ProductSubcategoryID) LEFT JOIN 
AdventureWorks.Production.ProductCategory cat ON
cat.ProductCategoryID=subcat.ProductCategoryID
where
cat.name is not null and
subcat.name is not null
order by 
cat.Name,subcat.name

Now i decided to build a structure that has four different filegroups which correspond to each category so that one query can stick on four of these datafiles simultaneously. To achieve this first i will create four filegroups and add one file to each filegroups as follows (corresponds creating tablespaces on a Oracle database and adding one datafile to each tablespace).

In the real world i would want to spread the datafiles over four individual drives to increase the disk throughput but in this test case i dont have that kind of test environment.

ALTER DATABASE AdventureWorks ADD FILEGROUP [FG1]
ALTER DATABASE AdventureWorks ADD FILEGROUP [FG2]
ALTER DATABASE AdventureWorks ADD FILEGROUP [FG3]
ALTER DATABASE AdventureWorks ADD FILEGROUP [FG4]

ALTER DATABASE AdventureWorks
ADD FILE 
(NAME = N'FG1_F1',
FILENAME = N'C:\FG1_F1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [FG1]

ALTER DATABASE AdventureWorks
ADD FILE 
(NAME = N'FG2_F1',
FILENAME = N'C:\FG2_F1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [FG2]

ALTER DATABASE AdventureWorks
ADD FILE 
(NAME = N'FG3_F1',
FILENAME = N'C:\FG3_F1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [FG3]

ALTER DATABASE AdventureWorks
ADD FILE 
(NAME = N'FG4_F1',
FILENAME = N'C:\FG4_F1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [FG4]

I decided to define a partition strategy over the "category" column which has evenly distributed datasets (you can check by aggregating the data with "group by and count").

The first thing we need is a partitioning function which will create a basis for partition scheme. And then create the partitioning schema based on the parititon function and on the desired filegroups. After all i will create the table on the partition scheme which means using the partition function and all of the filegroups.

-- create the partition function
CREATE PARTITION FUNCTION [f_part_category] (char(20))
AS RANGE LEFT FOR VALUES ('Bikes', 'Components', 'Clothing', 'Accessories');

-- check the partition function
select * from sys.partition_functions

name                function_id   type_desc       
------------------  ------------- --------------- 
f_part_category     65539         RANGE           

(1 row(s) affected)

-- create the partition scheme
CREATE PARTITION SCHEME s_part_category
AS PARTITION f_part_category
TO ([FG1], [FG2], [FG3], [FG4], [FG4]);

-- check the partition scheme
select * from sys.partition_schemes

name                 data_space_id      type  type_desc                  
-------------------- -----------------  ----  -----------------
s_part_category      65601              PS    PARTITION_SCHEME           

(1 row(s) affected)

CREATE TABLE [dbo].[t_product_part](
[category_name] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[subcategory_name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProductNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MakeFlag] [bit] NOT NULL,
[FinishedGoodsFlag] [bit] NOT NULL,
[Color] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SafetyStockLevel] [smallint] NOT NULL,
[ReorderPoint] [smallint] NOT NULL,
[StandardCost] [money] NOT NULL,
[ListPrice] [money] NOT NULL,
[Size] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SizeUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WeightUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Weight] [decimal](8, 2) NULL,
[DaysToManufacture] [int] NOT NULL,
[ProductLine] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Class] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductSubcategoryID] [int] NULL,
[ProductModelID] [int] NULL,
[SellStartDate] [datetime] NOT NULL,
[SellEndDate] [datetime] NULL,
[DiscontinuedDate] [datetime] NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON s_part_category (category_name)

Now; time to test the performance issue. As the data is not heavily loaded we can check the execution plans of the before and after queries.

insert into t_product_part select * from master.dbo.v_products2

select * from t_product_part

BEFORE PARTITIONING

AFTER PARTITIONING

Conclusion
Effective IO usage with using more than one datafile simultaneously.
Remarkable increase in the IO performance.
2.3 times faster IO performance with the sample data (with only one drive)

creating user defined metric for monitoring job durations

2009-11-11T22:36:00.011+02:00

In this production system there are some number of database jobs (not Scheduler Jobs) running on different time intervals. Some of these jobs are running on every five minutes while the other running on every hour. There are also some batch jobs scheduled to be run on every night.

There was a custom developed Oracle9i script to monitor every job duration and warn the DBA if there is a broken job or a job with more than three hours of duration. As this database is upgraded to 10g now we are able to use "Notification Rules" and "User Defined Metrics" to monitor the database jobs. This approach will supply us the historical data collection and also the standart notification method with all other EM Console messages. We also will not need to compile this old package on all of the production databases instead the User Defined Metric can be applied to all of the databases with including the metric in the monitoring templates.

"User Defined Metrics" page can be reached from a link at the bottom of the main page of the Enterprise Manager DB Console just under the Related Links section shown below.

When you click to the "User-Defined Metrics" link on the main page of the EM DB Console, User Defined Metrics are listed.

You can manage the your own custom metrics by using the right top buttons. You can simply select on of the User Defined Metric and click on the "View Data" Button.

When you click on the "View Data" button the Metric Value History of the specified Metric can be seen.

Again from the main page of the "User-Defined Metrics" you can click on the "Create" Button to create a brand new User Defined Metric. There is a sample new metric creation page values which i used for the metric UDM_LONG_RUNNING_JOBS. This metric calculates the job durations and notified the DBA if there are jobs which have more than 180 Minutes of duration time.

This metric has a string value and collection SQL of the metric is as follows. If the return value contains "DURATION" string then EM Console pretends as it exceeds a Critical Treshold and throws a Critical Alert. Metric collection runs every 5 minutes. At least 1 occurance is sufficient to send a Critical Alert.

select RETURN from
(
select 'DURATION: ' || round((sysdate-this_date)*24*60,2) || 'minutes JOB: ' || job || ' -> ' || substr(WHAT,1,50) as RETURN from
(
select
CASE when round((sysdate-this_date)*24*60,2) >= 5 then 'TRUE' else 'FALSE' end as return,
round((sysdate-this_date)*24*60,2) as minutes,
this_date,
sysdate,
job,
schema_user,
last_date,
next_date,
total_time,
interval,
what
from
dba_jobs
where
broken='N' and
LAST_DATE is not NULL
order by
next_date asc
)
where return='TRUE'
group by round((sysdate-this_date)*24*60,2),job,substr(WHAT,1,50)
UNION
select 'OK' as RETURN from dual
)
where rownum=1

The last thing is, if you want an email notification when a Critical Alert triggered by this User Defined Metric you should add this metric to the notification rules which is in the preferences page of the EM Grid Control.

what is datapump doing in my system tablespace

2009-11-04T19:13:00.003+02:00

One of our datapump export jobs was continously ending unsuccessfully with the following error.

Job "SYSTEM"."SYS_EXPORT_FULL_06" stopped due to fatal error at 01:42:36

I decided to investigate the log file and seems there is a lack of available space in the system tablespace and the datapump job goes in to the resumable wait and after 2 hours (which is a default resumable timeout value) fails. The error lines as follows.

ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table SYSTEM.SYS_EXPORT_FULL_06 by 128 in tablespace SYSTEM

I think I should investigate the root cause of the problem. I want to know what objects are using the SYSTEM tablespace first. Listing the largest segments would be handy.

select 
segment_name, bytes/1024/1024 as MB from dba_segments 
where 
tablespace_name='SYSTEM' 
order by bytes desc; 

/*
SEGMENT_NAME                MB
------------                --
SYS_LOB0001316900C00039$$   3072
SYS_LOB0001344581C00039$$   3054
SYS_LOB0001343732C00039$$   3029
SYS_LOB0001320071C00039$$   2375
SYS_LOB0001344712C00039$$   1216
C_OBJ#                      816
I_COL1                      745
I_COL2                      484
*/

What are these SYSTEM generated segments?? They are allocating lots of space here.

select 
owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 as MB 
from 
dba_segments 
where 
segment_name = 'SYS_LOB0001344712C00039$$'

/*
OWNER   SEGMENT_NAME                SEGMENT_TYPE    TABLESPACE_NAME MB
-----   ------------                ------------    --------------- --
SYSTEM  SYS_LOB0001344712C00039$$   LOBSEGMENT      SYSTEM          1216
*/

select 
owner, table_name, column_name, segment_name 
from 
dba_lobs 
where segment_name = 'SYS_LOB0001344712C00039$$';

/*
OWNER   TABLE_NAME          COLUMN_NAME     SEGMENT_NAME
-----   ----------          -----------     ------------
SYSTEM  SYS_EXPORT_FULL_05  XML_CLOB        SYS_LOB0001344712C00039$$
*/

Now it is clear. Datapump jobs locked themselves because of the tables created by the jobs itself (master tables of the datapump jobs). I think when the jobs couldnt ended up with success (or failed unexpectedly), datapump cannot tidy up its behind. Let me see the datapump jobs existing in the database.

select * from dba_datapump_jobs;
select * from dba_datapump_sessions;

/*
OWNER_NAME  JOB_NAME            OPERATION       JOB_MODE        STATE
----------  --------            --------        --------        -----       
SYSTEM      SYS_EXPORT_FULL_04  EXPORT          FULL            NOT RUNNING 
SYSTEM      SYS_EXPORT_FULL_05  EXPORT          FULL            NOT RUNNING
*/

I think that is what i guess. The jobs are not running and there no sessions attached to them. I decided to drop the tables to obtain some free space to the SYSTEM tablespace.

drop table SYSTEM.SYS_EXPORT_FULL_04;

select 
segment_name, bytes/1024/1024 as MB from dba_segments 
where 
tablespace_name='SYSTEM' 
order by bytes desc; 

select to_char(trunc(sum(bytes)/1024/1024),'99,999,999') as FREE_MB from dba_free_space where tablespace_name='SYSTEM';

/*
FREE_MB
------
14,644
*/

Now it is OK. 20Gb of SYSTEM tablespace is now has more than 14Gb of free space. Let me investigate the problem further for not to fall into the same problem again. When i check the available space of filesystem which datapump job works at, there seems a little space left on the disks. Then it is possible to be an unexpected job failure.

Meanwhile, As far as i know if you are using datapump in parallel mode you should obtain more than one dumpfile. You can attach %U parameter at the end of the file then datapump will automatically create the number of the files for every parallel thread. But in this case there is only one file is specified and 6 parallel thread is trying to be used. I dont know if this is the real cause of the problem but i will definitely remove the parallel clause from the parameter file.

There is another option here that i didnt tried before which is compression. I am not sure of this but may be the compression operation of the METADATA is done in the system tablespace? Or somehow needs some space in the database. It look silly because if the compression is done in an algorithm like this it is not really efficient, but i still dont want to use this parameter so also removed the compression parameter from the parameter file.

full=y
compression=METADATA_ONLY
directory=DATA_PUMP_DIR
dumpfile=fusion_export_full.exp
logfile=fusion_export_full.log
parallel=6
userid=system/@

DRCP vs Oracle Shared Server

2009-10-28T17:13:00.004+02:00

While i was hanging out in the Oracle Database 11g New Features documents on OTN I found something interesting which is not documented on Oracle 11g New Features, Database Resident Connection Pooling (DRCP). I was surprised because this feature is very handy and usable in a lot of environment especially if you serve your data to more than one application running on more than one application server and with enormous number of connections.

Before Oracle Database 11g we were all using Oracle Shared Server architecture to lower the server side user processes and their overheads to the server resources. But with 11g the similar architecture is available now called Database Resident Connection Pooling (DRCP).

Quoted from the related document linked at the end of the this post:

"In one of our test scenarios on a 2GB system, DRCP was able to support 10 times the number of connections compared to shared servers, and 20 times the number of connections compared to dedicated servers."

The question; is DRCP a replacement of Oracle Shared Server?

Related:
Database Resident Connection Pooling (DRCP) Oracle Database 11g Technical White Paper

Some Notes on Oracle Streams Table Replication

2009-10-14T19:32:00.006+03:00

In this test, replication is made from Oracle Database version 11.1.0.7 to 11.1.0.6 database on a virtual machine. This is not a performance test but only a functional test of the streams basic abilities. This test is made on table replication basis maybe I can publish the schema replication based test in another article.

Some preparation needed first and i assume both the databases are in archivelog mode. Actually documentation says only the source database is enough for archiving redologs. That is reasonable for capturing the changes and not to miss one. But my databases are both in archivelog mode so I cover this necessity.

--check if there are unsupported column types.
select * from DBA_STREAMS_UNSUPPORTED;

--create a tablespace on the source database for test purposes
create tablespace TS_STREAMS_SRC DATAFILE 'D:\oracledb11g\oradata\orcl\TS_STREAMS_SRC_01.dbf'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--create a tablespace on the destination database for test purposes
create tablespace TS_STREAMS_DEST DATAFILE 'D:\oracledb11g\oradata\orcl\TS_STREAMS_DEST_01.dbf'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--creating the streams admin user on both of the databases.
create user U_STREAMS_ADM identified by password
DEFAULT TABLESPACE TS_STREAMS_SRC
QUOTA UNLIMITED ON TS_STREAMS_SRC;

--streams user should have dba privilege
grant connect,resource,dba to U_STREAMS_ADM;
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('U_STREAMS_ADM',TRUE);

--create database links on the databases to access both from SOURCE to DESTINATION and from DESTINATION to SOURCE

--here ORCL_SOURCE is the tns alias for the SOURCE database and ORCL_DESTINATION is the tns alias for the DESTINATION database

create database link ORCL.SOURCE.LOCAL connect to U_STREAMS_ADM_SRC identified by password using 'ORCL_SOURCE';

create database link ORCL.DESINATION.LOCAL connect to U_STREAMS_ADM_SRC identified by password using 'ORCL_DESTINATION';

--a second destination should be defined on the source database to send the redo logs to the destination database
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCL ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL';

-- creating a table replication with MAINTAIN_TABLES
-- with this configuration CAPTURE and PROPAGATION processes are on the source database
-- APPLY process is on the DESTINATION database
DECLARE
tables DBMS_UTILITY.UNCL_ARRAY;
BEGIN
tables(1) := 'scott.dept';
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
table_names => tables,
source_directory_object => NULL,
destination_directory_object => NULL,
source_database => 'ORCL.SOURCE.LOCAL',
destination_database => 'ORCL.DESTINATION.LOCAL',
capture_name => 'CAPTURE_TABLE_GRP1',
capture_queue_name => 'QUEUE_CAPT_TABLE_GRP1',
capture_queue_user => 'U_STREAMS_ADM',
propagation_name => 'PROPAGATE_TABLE_GRP1',
apply_name => 'APPLY_TABLE_GRP1',
apply_queue_name => 'QUEUE_APPLY_TABLE_GRP1',
apply_queue_user => 'U_STREAMS_ADM',
perform_actions => TRUE,
script_directory_object => NULL,
script_name => NULL,
bi_directional => FALSE,
include_ddl => TRUE,
instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
END;

The procedure worked smoothly while I was waiting for it to be end with an exception. But if there is a problem with the instantiation of the replication then the following set of commands helps you to clear the inadequate configuration and then you can run the MAINTAIN_TABLES again.

SELECT * FROM DBA_RECOVERABLE_SCRIPT_ERRORS;
execute DBMS_STREAMS_ADM.RECOVER_OPERATION('A6B753822B9C4C30A5CD87B9571ACF03','PURGE');
exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

--The insert tests just worked fine as follows.
insert into SCOTT.DEPT values (60,'IT-DEVELOPMENT','IST');
insert into SCOTT.DEPT values (70,'IT-STORAGE','IST');
insert into SCOTT.DEPT values (80,'IT-BACKUP','IST');
insert into SCOTT.DEPT values (90,'IT-WINDOWS','IST');
insert into SCOTT.DEPT values (95,'IT-UNIX','IST');
insert into SCOTT.DEPT values (96,'IT-SECURITY','IST');
commit;

--Findout the configuration and status of the processes
select * from dba_capture;
select * from dba_propagation;
select * from dba_apply;
select * from v$streams_capture;

Lets see if the configuration is flexible enough to add a new table rule to the existing set easily. I followed the documentation with the following commands in order.

--on source database
exec DBMS_CAPTURE_ADM.STOP_CAPTURE('CAPTURE_TABLE_GRP1');
exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROPAGATE_TABLE_GRP1');

--on the destination database
exec DBMS_APPLY_ADM.STOP_APPLY('APPLY_TABLE_GRP1');

--add new rule to the CAPTURE process
exec DBMS_STREAMS_ADM.ADD_TABLE_RULES(
TABLE_NAME =>'SCOTT.EMP',
STREAMS_TYPE => 'CAPTURE',
STREAMS_NAME => 'CAPTURE_TABLE_GRP1',
SOURCE_DATABASE => 'ORCL.SOURCE.LOCAL',
QUEUE_NAME => 'QUEUE_CAPT_TABLE_GRP1',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
--DDL_RULE_NAME => NULL,
--DML_RULE_NAME => NULL,
INCLUSION_RULE => TRUE,
AND_CONDITION => NULL);

--add new rule to the PROPAGATION process
exec DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
TABLE_NAME => 'SCOTT.EMP',
STREAMS_NAME => '', --if propagation exists on source_queue_name then uses the existing propagation
SOURCE_QUEUE_NAME => 'U_STREAMS_ADM.QUEUE_CAPT_TABLE_GRP1',
DESTINATION_QUEUE_NAME => 'U_STREAMS_ADM.QUEUE_APPLY_TABLE_GRP1@ORCL.DESTINATION.LOCAL',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'ORCL.SOURCE.LOCAL',
INCLUSION_RULE => TRUE,
AND_CONDITION => NULL,
QUEUE_TO_QUEUE => TRUE --TRUE=queue_to_queue FALSE=queue_to_dblink
);

--now the last thing is to add a new rule to the APPLY process
exec DBMS_STREAMS_ADM.ADD_TABLE_RULES(
TABLE_NAME =>'SCOTT.EMP',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_TABLE_GRP1',
SOURCE_DATABASE => 'ORCL.SOURCE.LOCAL',
QUEUE_NAME => 'QUEUE_APPLY_TABLE_GRP1',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
--DDL_RULE_NAME => NULL,
--DML_RULE_NAME => NULL,
INCLUSION_RULE => TRUE,
AND_CONDITION => NULL);

We can now check if the new rules are added and the conditions of the rules with the following views. We cannot see and new process configuration as i added the rules to the existing APPLY, PROPAGATION and CAPTURE processes.

DBA_RULES
DBA_RULE_SETS
DBA_STREAMS_RULES
DBA_RULE_SET_RULES

If you did something wrong wile adding the rules you can easily delete the rules by running the following procedures which are under the DBMS_RULE_ADM. Be sure what are you deleting by selecting DBA_STREAMS_RULES. You dont want to delete a rule associated with a working process.

--If you want to delete a rule you should first remove it
DBMS_RULE_ADM.REMOVE_RULE
DBMS_RULE_ADM.DELETE_RULE

I added the rules and now I should instantiate the table/tables manually. MAINTAIN_TABLES procedure makes this instantiation automatically for you but if you want to add the rules explicitly then you need to instantiate them.

--set the instantiation SCN of the SOURCE table/tables
DECLARE
iscn NUMBER; --Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'scott.emp',
source_database_name => 'ORCL.SOURCE.LOCAL',
instantiation_scn => iscn);
END;

--Create the table on the DESTINATION database.
create table scott.emp as select * from scott.emp@ORCL.SOURCE.LOCAL;

--The last thing is not to forget the SUPPLEMENTAL LOGGING for the replicated table to capture and apply the changes.
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

--After checking all the configuration again now I can start the processes i have stopped.
-- on source database.
exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_TABLE_GRP1');
exec DBMS_PROPAGATION_ADM.START_PROPAGATION('PROPAGATE_TABLE_GRP1');

--On the destination database.
exec DBMS_APPLY_ADM.START_APPLY('APPLY_TABLE_GRP1');

It seems everything worked fine as the Oracle documentation stated. Adding rules to working processes of the streams are easy accept you have to manually instantiate the tables via create table .. as .. or export/import or expdp/impdp if you are adding the rules manually. Now i want to go furher and alter a replication rule of an existing configuration of the apply process to capture only a subset of the changes.

Rules can be seen by selecting DBA_RULES, DBA_RULESETS, DBA_RULE_SET_RULES or DBA_STREAMS_RULES which i prefer to use. One should be careful which rule he/she is going to alter. There are DML and DDL rules for every process. And the rule that will be altered should be related with the correct CAPTURE PROCESS (since there can be more than one capture process).

In my case the rule that i will going to alter is BONUS85 as follows. Stopping the processes before any change in the configuration is a good idea. Without stopping and starting the processes the new configuration cannot be applied to the existing streams processes.

--On source database
exec DBMS_CAPTURE_ADM.STOP_CAPTURE('CAPTURE_TABLE_GRP1');
exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROPAGATE_TABLE_GRP1');

--On the destination database
exec DBMS_APPLY_ADM.STOP_APPLY('APPLY_TABLE_GRP1');

--Altering a rule capturing a subset of the data in the table.
execute dbms_rule_adm.alter_rule('BONUS85',':dml.get_object_owner()=''SCOTT'' AND :dml.get_object_name()=''BONUS''
AND :dml.is_null_tag()=''Y'' AND :dml.get_source_database_name() = ''ORCL.DIGITURK.LOCAL''
AND (:dml.get_value(''NEW'',''ENAME'').AccessVarchar2()=''ERGEM'')');

--On source database
exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_TABLE_GRP1');
exec DBMS_PROPAGATION_ADM.START_PROPAGATION('PROPAGATE_TABLE_GRP1');

--On the destination database
exec DBMS_APPLY_ADM.START_APPLY('APPLY_TABLE_GRP1');

--Related views for streams monitoring
select * from v$streams_apply_coordinator
select * from v$streams_apply_reader
select * from v$streams_apply_server
select * from v$streams_capture
select * from v$streams_pool_advice
select * from v$streams_transaction
select * from v$streams_monitor

As a conclusion Oracle Streams is easy to configure and use. It seems flexible to change the configuration easily and altering the existing rules to gather the subset of the changes. I did not test the performance but functional tests seems OK.

Of course there is a lot of options you can use; Downstream Capture Processes, Sync Captures, Parallel Capture/Apply Processes and so on. Oracle Documentation has sufficient information about the Streams.

Related:
Oracle Streams Replication Administrators Guide
Oracle Streams Concepts and Administration
PSOUG Streams Demo

SQL Server detected a logical consistency-based I/O error

2009-05-15T16:05:00.022+03:00

After migrating the SQL Server 2005 SP2 32-bit database to SP3 64-bit environment some queries started to complain about the following error in the SQL Server Error Logs.

Message:
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x5be5d620). It occurred during a read of page (1:718204) in database ID 7 at offset 0x0000015eaf8000 in file 'E:\MSSQL\DATA\SMPPGatewayBEP.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

It is obvious that there happened an IO inconsistency. Maybe while the backup or the restore operation. I decided to execute DBCC CHECKDB('SMPPGatewayBEP') But the following output generated by the server.

Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
DBCC results for 'SMPPGatewayBEP'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'SMPPGatewayBEP'.

After a little search on msdn i decided to try to set the database into the single user mode by running:

alter database SMPPGatewayBEP SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB('SMPPGatewayBEP', REPAIR_ALLOW_DATA_LOSS)

Now it started to repair the data pages by reading the log pages. There is thousands of lines of this output so i put the head and tail part of the output.

DBCC results for 'SMPPGatewayBEP'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352523) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352524) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352525) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352526) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352527) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:719092) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:719093) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.

...
..
.

Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
The error has been repaired.
CHECKDB found 0 allocation errors and 1857 consistency errors not associated with any single object.
CHECKDB fixed 0 allocation errors and 973 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 716 rows in 8 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 101 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 115 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 716 rows in 9 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 101 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 115 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 15 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 135 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 134 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 629 rows in 13 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".

...
..
.

Repair: The page (1:718716) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718717) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718718) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718719) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719048) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719049) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719050) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719051) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719052) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719053) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719054) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719055) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).

...
..
.

The error has been repaired.
The error has been repaired.
The error has been repaired.
There are 7270488 rows in 35601 pages for object "WSLog".
CHECKDB found 0 allocation errors and 8 consistency errors in table 'WSLog' (object ID 398624463).
CHECKDB fixed 0 allocation errors and 8 consistency errors in table 'WSLog' (object ID 398624463).
DBCC results for 'SettingsInbound'.
There are 1 rows in 1 pages for object "SettingsInbound".
DBCC results for 'SettingsDelivered'.
There are 1 rows in 1 pages for object "SettingsDelivered".
DBCC results for 'sysdiagrams'.
There are 0 rows in 0 pages for object "sysdiagrams".
DBCC results for 'ThreadMonitoring'.
There are 0 rows in 0 pages for object "ThreadMonitoring".
DBCC results for 'Settings'.
There are 1 rows in 1 pages for object "Settings".
DBCC results for 'SMPPIncoming'.
There are 95 rows in 4 pages for object "SMPPIncoming".
DBCC results for 'BlankMessages'.
There are 0 rows in 0 pages for object "BlankMessages".
DBCC results for 'TestTrace'.
There are 2134061 rows in 46701 pages for object "TestTrace".
DBCC results for 'ServiceParams'.
There are 18 rows in 1 pages for object "ServiceParams".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
CHECKDB found 0 allocation errors and 6622 consistency errors in database 'SMPPGatewayBEP'.
CHECKDB fixed 0 allocation errors and 5738 consistency errors in database 'SMPPGatewayBEP'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SMPPGatewayBEP, repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

After one hour finally the summary shows that lots of the page errors are repaired. Maybe I should run the same DBCC CHECKDB second time, to be sure if the remaining errors would be gone but we didnt have to wait for that. After setting the database into multiuser mode again and backing it up users were able to run their reports again.

alter database SMPPGatewayBEP SET MULTI_USER;

Problem with adding a new node to RAC after ocr and voting disk change

2009-02-23T22:09:00.006+02:00

After changing the ocr and voting disks with different raw devices, that used with installation, later on i faced a problem while adding a new node to my existing 2node cluster installation.

After the first step of adding a new node to a cluster (cloning my CRS_HOME (cluster home of oracle RAC) and ran all scripts), the last step in the documentation was running root.sh in the new node's CRS_HOME. But the unexpected error was;


root@radbank04 # ./root.sh
"/dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0" does not exist. Create it before proceeding.
Make sure that this file is shared across cluster nodes.
1

I am pretty sure with the raw device definitions are OK in the new node. I decided to examine the "root.sh" to see what it is running in the background. "root.sh" was consisting of two other scripts "rootinstall" and "rootconfig".


root@radbank04 # more root.sh
#!/bin/sh
/oracle/product/crs10g/install/rootinstall
/oracle/product/crs10g/install/rootconfig

I found the following part of the script in the very beginning of the rootconfig script.


SILENT=false
ORA_CRS_HOME=/oracle/product/crs10g
CRS_ORACLE_OWNER=oracle
CRS_DBA_GROUP=oinstall
CRS_VNDR_CLUSTER=false
CRS_OCR_LOCATIONS=/dev/rdsk/c6t600601607D731F00BAE716BB2CE9DD11d0s0,/dev/rdsk/c6t600601607D731F006496A6CD2CE9DD11d0s0
CRS_CLUSTER_NAME=crs
CRS_HOST_NAME_LIST=radbank02,1,radbank03,2
CRS_NODE_NAME_LIST=radbank02,1,radbank03,2
CRS_PRIVATE_NAME_LIST=radbank02-priv1,1,radbank03-priv1,2
CRS_LANGUAGE_ID='AMERICAN_AMERICA.WE8ISO8859P1'
CRS_VOTING_DISKS=/dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
CRS_NODELIST=radbank02,radbank03
CRS_NODEVIPS='radbank02/radbank02-vip/255.255.255.0/bge0,radbank03/radbank03-vip/255.255.255.0/bge0'

Somehow (my second somehow in the 10gR2 RAC environment) crs records was showing the old ocr and voting disks. The new disks i have replaced in my existing RAC environment before was totally different. I decided to modify the rootconfig file and replace the old defined ocr and voting disks with the new ones as follows. An rerun the rootconfig file. I dont know if this action is recommended by oracle but i think this is the only change that i have in my hand.


SILENT=false
ORA_CRS_HOME=/oracle/product/crs10g
CRS_ORACLE_OWNER=oracle
CRS_DBA_GROUP=oinstall
CRS_VNDR_CLUSTER=false
#CRS_OCR_LOCATIONS=/dev/rdsk/c6t600601607D731F00BAE716BB2CE9DD11d0s0,/dev/rdsk/c6t600601607D731F006496A6CD2CE9DD11d0s0
CRS_OCR_LOCATIONS=/dev/rdsk/c6t600601607D731F00AE6F711488FEDD11d0s0,/dev/rdsk/c6t600601607D731F00AF6F711488FEDD11d0s0
CRS_CLUSTER_NAME=crs
CRS_HOST_NAME_LIST=radbank02,1,radbank03,2
CRS_NODE_NAME_LIST=radbank02,1,radbank03,2
CRS_PRIVATE_NAME_LIST=radbank02-priv1,1,radbank03-priv1,2
CRS_LANGUAGE_ID='AMERICAN_AMERICA.WE8ISO8859P1'
#CRS_VOTING_DISKS=/dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
CRS_VOTING_DISKS=/dev/rdsk/c6t600601607D731F00A8833C0377FEDD11d0s0,/dev/rdsk/c6t600601607D731F00A6833C0377FEDD11d0s0,/dev/rdsk/c6t60
0601607D731F00A7833C0377FEDD11d0s0
CRS_NODELIST=radbank02,radbank03
CRS_NODEVIPS='radbank02/radbank02-vip/255.255.255.0/bge0,radbank03/radbank03-vip/255.255.255.0/bge0'

root@radbank04 # /oracle/product/crs10g/install/rootconfig

Checking to see if Oracle CRS stack is already configured
OCR LOCATIONS =  /dev/rdsk/c6t600601607D731F00AE6F711488FEDD11d0s0,/dev/rdsk/c6t600601607D731F00AF6F711488FEDD11d0s0
OCR backup directory '/oracle/product/crs10g/cdata/crs' does not exist. Creating now
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory '/oracle/product' is not owned by root
WARNING: directory '/oracle' is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :   
node 1: radbank02 radbank02-priv1 radbank02
node 2: radbank03 radbank03-priv1 radbank03
clscfg: Arguments check out successfully.

NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
        radbank02
        radbank03
        radbank04
CSS is active on all nodes.

root@radbank04 # ./crs_stat -t

Name           Type           Target    State     Host        
------------------------------------------------------------
ora.monsc.db   application    ONLINE    ONLINE    radbank03   
ora....c1.inst application    ONLINE    ONLINE    radbank02   
ora....c2.inst application    ONLINE    ONLINE    radbank03   
ora.montc.db   application    ONLINE    ONLINE    radbank02   
ora....ntc1.cs application    ONLINE    ONLINE    radbank02   
ora....c1.inst application    ONLINE    ONLINE    radbank02   
ora....tc1.srv application    ONLINE    ONLINE    radbank02   
ora....ntc2.cs application    ONLINE    ONLINE    radbank03   
ora....c2.inst application    ONLINE    ONLINE    radbank03   
ora....tc2.srv application    ONLINE    ONLINE    radbank03   
ora....ntc3.cs application    ONLINE    ONLINE    radbank03   
ora....tc1.srv application    ONLINE    ONLINE    radbank02   
ora....tc2.srv application    ONLINE    ONLINE    radbank03   
ora....SM1.asm application    ONLINE    ONLINE    radbank02   
ora....02.lsnr application    ONLINE    ONLINE    radbank02   
ora....02.lsnr application    ONLINE    ONLINE    radbank02   
ora....k02.gsd application    ONLINE    ONLINE    radbank02   
ora....k02.ons application    ONLINE    ONLINE    radbank02   
ora....k02.vip application    ONLINE    ONLINE    radbank02   
ora....SM2.asm application    ONLINE    ONLINE    radbank03   
ora....03.lsnr application    ONLINE    ONLINE    radbank03   
ora....03.lsnr application    ONLINE    ONLINE    radbank03   
ora....k03.gsd application    ONLINE    ONLINE    radbank03   
ora....k03.ons application    ONLINE    ONLINE    radbank03   
ora....k03.vip application    ONLINE    ONLINE    radbank03   
ora....k04.gsd application    ONLINE    ONLINE    radbank04   
ora....k04.ons application    ONLINE    ONLINE    radbank04   
ora....k04.vip application    ONLINE    ONLINE    radbank04

My new node is part of my existing cluster now. Now time to add the ORACLE_HOME (database installation) and the instances with the services.

Resources:

Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide 10g Release 2 (10.2)

Add/Remove ASM disks and asm_power_limit

2009-02-23T21:25:00.009+02:00

Before going live on production our storage team decided to reconfigure their storage and raidsets for better performance. So i gave the raw devices back from asm and of course used the opportunity to test the asm_power_limit parameter.

From the following listing you can find how to list asm disks, adding and removing disks from a disk group and watch the rebalance operation from v$asm_operation view.

By changing the init parameter "asm_power_limit", asm rebalance operations are significantly getting faster. The difference can obviously be seen by setting the parameter to 10 (instead of default value 1) and then giving the same operations that need rebalance. Of course this was not an online system so we may want asm to exhaust all the IO for rebalancing. But in case of production systems there can be unwanted IO performance degradation of the application by this kind of aggressive setting.


SQL> select group_number,state,name,total_mb from v$asm_disk;

GROUP_NUMBER STATE    NAME                   TOTAL_MB
------------ -------- -------------------- ----------
0 NORMAL                               924
0 NORMAL                               924
0 NORMAL                               924
0 NORMAL                               924
1 NORMAL   DATA_0009                614300
1 NORMAL   DATA_0008                614290
1 NORMAL   DATA_0002                614290
1 NORMAL   DATA_0001                614290
1 NORMAL   DATA_0000                614290
1 NORMAL   DATA_0007                614290
1 NORMAL   DATA_0006                614290
1 NORMAL   DATA_0005                614290
1 NORMAL   DATA_0004                614290
1 NORMAL   DATA_0003                614290

14 rows selected.

SQL> alter diskgroup DATA drop disk DATA_0000;

Diskgroup altered.

SQL> alter diskgroup DATA drop disk DATA_0001;

Diskgroup altered.

SQL> select group_number,state,name,total_mb,label,path from v$asm_disk;

GROUP_NUMBER STATE    NAME              TOTAL_MB LABEL PATH
------------ -------- --------------- ---------- ----- --------------------------------------------------
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A8833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A7833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A6833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
1 NORMAL   DATA_0009           614300       /dev/rdsk/c6t600601607D731F00D994060263E8DD11d0s0
1 NORMAL   DATA_0008           614290       /dev/rdsk/c6t600601607D731F00D894060263E8DD11d0s0
1 NORMAL   DATA_0002           614290       /dev/rdsk/c6t600601607D731F006A37A4E0A0E7DD11d0s0
1 DROPPING DATA_0001           614290       /dev/rdsk/c6t600601607D731F006937A4E0A0E7DD11d0s0
1 DROPPING DATA_0000           614290       /dev/rdsk/c6t600601607D731F006837A4E0A0E7DD11d0s0
1 NORMAL   DATA_0007           614290       /dev/rdsk/c6t600601607D731F007608DB3DA0E7DD11d0s0
1 NORMAL   DATA_0006           614290       /dev/rdsk/c6t600601607D731F007508DB3DA0E7DD11d0s0
1 NORMAL   DATA_0005           614290       /dev/rdsk/c6t600601607D731F007408DB3DA0E7DD11d0s0
1 NORMAL   DATA_0004           614290       /dev/rdsk/c6t600601607D731F007308DB3DA0E7DD11d0s0
1 NORMAL   DATA_0003           614290       /dev/rdsk/c6t600601607D731F007208DB3DA0E7DD11d0s0


SQL> select * from v$asm_operation;

OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES
----- ---- ---------- ---------- ---------- ---------- ---------- -----------
REBAL RUN          10         10       7198      12996        260          22
REBAL RUN          10         10       7679      12979        242          21
REBAL RUN          10         10       9286      12930        201          18
REBAL RUN          10         10      11647      12899        237           5

SQL> select * from v$asm_operation;

no rows selected

SQL> select group_number,state,name,total_mb,label,path from v$asm_disk;

GROUP_NUMBER STATE    NAME              TOTAL_MB LABEL PATH
------------ -------- --------------- ---------- ----- --------------------------------------------------
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A8833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A7833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A6833C0377FEDD11d0s0
0 NORMAL                       614290       /dev/rdsk/c6t600601607D731F006937A4E0A0E7DD11d0s0
0 NORMAL                       614290       /dev/rdsk/c6t600601607D731F006837A4E0A0E7DD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
1 NORMAL   DATA_0009           614300       /dev/rdsk/c6t600601607D731F00D994060263E8DD11d0s0
1 NORMAL   DATA_0008           614290       /dev/rdsk/c6t600601607D731F00D894060263E8DD11d0s0
1 NORMAL   DATA_0002           614290       /dev/rdsk/c6t600601607D731F006A37A4E0A0E7DD11d0s0
1 NORMAL   DATA_0007           614290       /dev/rdsk/c6t600601607D731F007608DB3DA0E7DD11d0s0
1 NORMAL   DATA_0006           614290       /dev/rdsk/c6t600601607D731F007508DB3DA0E7DD11d0s0
1 NORMAL   DATA_0005           614290       /dev/rdsk/c6t600601607D731F007408DB3DA0E7DD11d0s0
1 NORMAL   DATA_0004           614290       /dev/rdsk/c6t600601607D731F007308DB3DA0E7DD11d0s0
1 NORMAL   DATA_0003           614290       /dev/rdsk/c6t600601607D731F007208DB3DA0E7DD11d0s0


SQL> alter diskgroup DATA add disk '/dev/rdsk/c6t600601607D731F006837A4E0A0E7DD11d0s0';

Diskgroup altered.

SQL> select group_number,state,name,total_mb,label,path from v$asm_disk;

GROUP_NUMBER STATE    NAME              TOTAL_MB LABEL PATH
------------ -------- --------------- ---------- ----- --------------------------------------------------
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A8833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A7833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A6833C0377FEDD11d0s0
0 NORMAL                       614290       /dev/rdsk/c6t600601607D731F006937A4E0A0E7DD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
1 NORMAL   DATA_0009           614300       /dev/rdsk/c6t600601607D731F00D994060263E8DD11d0s0
1 NORMAL   DATA_0008           614290       /dev/rdsk/c6t600601607D731F00D894060263E8DD11d0s0
1 NORMAL   DATA_0002           614290       /dev/rdsk/c6t600601607D731F006A37A4E0A0E7DD11d0s0
1 NORMAL   DATA_0007           614290       /dev/rdsk/c6t600601607D731F007608DB3DA0E7DD11d0s0
1 NORMAL   DATA_0006           614290       /dev/rdsk/c6t600601607D731F007508DB3DA0E7DD11d0s0
1 NORMAL   DATA_0005           614290       /dev/rdsk/c6t600601607D731F007408DB3DA0E7DD11d0s0
1 NORMAL   DATA_0004           614290       /dev/rdsk/c6t600601607D731F007308DB3DA0E7DD11d0s0
1 NORMAL   DATA_0003           614290       /dev/rdsk/c6t600601607D731F007208DB3DA0E7DD11d0s0
1 NORMAL   DATA_0000           614290       /dev/rdsk/c6t600601607D731F006837A4E0A0E7DD11d0s0


SQL> show parameter asm_power_limit;

NAME                            TYPE        VALUE
------------------------------- ----------- -------------------------
asm_power_limit                 integer     1

SQL> alter system set asm_power_limit=10;

System altered.

SQL> show parameter asm_power_limit;

NAME                            TYPE        VALUE
------------------------------- ----------- -------------------------
asm_power_limit                 integer     10

SQL>

why auditing

2008-12-14T21:13:00.005+02:00

In general it seems that security is one of the least important routines of the dba. But in my opinion this issue should be one of the most important issues. Not only be able to supply audit records to firm-wide audit documentations but also knowing what is going on our databases to be sure everything goes straight. Auditing is not only blaming someone for a wrong action. This routine is for early clearing up for the wrong events.

I start auditing with "db, extended" AUDIT_TRAIL parameter to track the sql statements with the actions by altering the system, restarting the instance is a must as this parameter cannot be changed online:


SQL> alter system set audit_trail="db, extended" scope=spfile;
SQL> shutdown immediate,
SQL> startup;

After enabling the auditing, i decided what actions should be audited. Here i am not very selective. All DML actions should be audited to me.

AUDIT INSERT ANY TABLE, DELETE ANY TABLE, UPDATE ANY TABLE BY -user1-, -user2- ... ;

AUDIT INSERT TABLE, DELETE TABLE, UPDATE TABLE BY -user1-, -user2- ... ;

AUDIT DROP ANY TABLE, DROP ANY INDEX, DROP ANY VIEW, DROP ANY SYNONYM, DROP ANY PROCEDURE, DROP ANY SEQUENCE, DROP ANY TRIGGER, DROP ANY TYPE BY -user1-, -user2- ... ;

AUDIT ALTER ANY TABLE, ALTER ANY INDEX, ALTER ANY PROCEDURE, ALTER ANY SEQUENCE, ALTER ANY TRIGGER, ALTER ANY TYPE BY -user1-, -user2- ... ;

AUDIT CREATE ANY TABLE, DROP ANY INDEX, DROP ANY VIEW, DROP ANY SYNONYM, DROP ANY PROCEDURE, DROP ANY SEQUENCE, DROP ANY TRIGGER, DROP ANY TYPE BY -user1-, -user2- ... ;

Move sys.aud$ table to data tablespace: We dont want to oversize the SYSTEM tablespace. As far as i read from oracle documentation moving AUD$ table to another tablespace is not supported. Instead writing some control and maintenance scripts are suggesgted to be used.


select * from sys.AUD$
alter table sys.AUD$ move tablespace PQ_DATA;

List the auditing privileges and users: To check what is being audited by my database.


select * from dba_priv_audit_opts;

After two weeks of auditing, i nearly captured more than three commonly wrong routines especially using wrong tablespaces and deleting routines.

creating awr reports manually

2008-11-15T13:53:00.003+02:00

When working with the third parties on the same database there happens some confusion. The operational dba 's are responsible from the database performance and other standard operational duties like backup, security, architecture .. and so on. Vendor dba 's are also responsible to achieve SLA contract requirements and promised performance values. Both side should be causious not to overlap the other side 's duties. Here i tried to answer one of the requests of our vendor dba by supplying hourly AWR reports directly in their directories on the UNIX environment. I think they can follow database activitiy better from now on.

### creating the awr report ###
ORACLE_HOME=/oracle/product/10.2.0; export ORACLE_HOME
ORACLE_SID=montc1; export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin; export PATH

$ORACLE_HOME/bin/sqlplus "/ as sysdba" << EOF
declare
i_begin_snap_id number;
i_end_snap_id number;
i_db_id number;
i_inst_num number;
begin

--select the db_id and the instance_number of the instance,
--these values are going to be used to create the awr_report
select DBID, INSTANCE_NUMBER into i_db_id, i_inst_num from dba_hist_database_instance where rownum=1;

--find the snap_id of the time being
select SNAP_ID into i_end_snap_id from dba_hist_snapshot where trunc(end_interval_time,'HH24') = trunc(sysdate,'HH24') order by SNAP_ID desc;

--set the begin snap_id of the previous hour as we are generation hourly awr reports
i_begin_snap_id := i_end_snap_id - 1;

--generate and write the report to a table
insert into avea_sysmon.temp_awr_output select output from table (SYS.DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(i_db_id, i_inst_num, i_begin_snap_id, i_end_snap_id));
commit;

end;
/
EOF

### creating report name ###
report_name=/moneta_home/avea_data/awr/awr_report_`date | awk '{print $3"_"$2"_"$6"_"$4}'`.html
export report_name

### spooling previously created report to the file specified ###
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" << EOF
set linesize 1500;
set echo off;
set heading off;
set termout on;

spool $report_name;
select s_output from avea_sysmon.temp_awr_output;
truncate table avea_sysmon.temp_awr_output;
spool off;
EOF

### delete the older reports ###
ctl=`ls -l /moneta_home/avea_data/awr | grep .html | wc -l`
if [ $ctl -gt 110 ]
then
ls -l /moneta_home/avea_data/awr | grep .html | head -10 | awk '{print "/moneta_home/avea_data/awr/"$9}' | xargs rm
#else
#echo "dont delete anything"
fi

After i tested the script activity of the script, i used crontab to schedule this script as a job.


$ crontab -l
00,10,20,30,40,50 * * * * /oracle/epeker/check_arch.sh > /oracle/epeker/check_arch.log 2>&1
20 * * * * /oracle/awr_reports/get_awr.sh > /oracle/awr_reports/get_awr.log 2>&1

The awr reports of the specified time are now available on application directories.


$ ls -lrt /moneta_home/avea_data/awr
total 1053328
-rw-r--r--   1 oracle   dba      5229793 Nov  6 14:20 awr_report_6_Nov_2008_14:20:45.html
-rw-r--r--   1 oracle   dba      4911565 Nov  6 15:20 awr_report_6_Nov_2008_15:20:08.html
-rw-r--r--   1 oracle   dba      4865032 Nov  6 16:20 awr_report_6_Nov_2008_16:20:12.html
-rw-r--r--   1 oracle   dba      6112427 Nov  6 17:20 awr_report_6_Nov_2008_17:20:06.html
-rw-r--r--   1 oracle   dba      5057170 Nov  6 18:20 awr_report_6_Nov_2008_18:20:03.html
-rw-r--r--   1 oracle   dba      5031651 Nov  6 19:20 awr_report_6_Nov_2008_19:20:02.html
-rw-r--r--   1 oracle   dba      4798984 Nov  6 20:20 awr_report_6_Nov_2008_20:20:03.html
-rw-r--r--   1 oracle   dba      5016641 Nov  6 21:20 awr_report_6_Nov_2008_21:20:02.html
-rw-r--r--   1 oracle   dba      4895053 Nov  6 22:20 awr_report_6_Nov_2008_22:20:05.html
-rw-r--r--   1 oracle   dba      5228292 Nov  6 23:20 awr_report_6_Nov_2008_23:20:09.html
-rw-r--r--   1 oracle   dba      5018142 Nov  7 00:20 awr_report_7_Nov_2008_00:20:03.html
-rw-r--r--   1 oracle   dba      5072180 Nov  7 01:20 awr_report_7_Nov_2008_01:20:10.html

Time to start securing the listeners

2008-11-03T14:41:00.004+02:00

I decided to apply basic security issues to all of my databases listener processes. As always i prefer Oracle Documentation for a reliable reference, this time i found a detailed white paper on integrigy web site which has a lot of hits from external links.

Both two resources call attention for two most important issues. Setting a password and Logging for the listener. The other issues can be taken in account are securing lsnrctl and tnslsnr executables in $ORACLE_HOME/bin/ directory, setting ADMIN_RESTRICTIONS parameter of the listener itself, changing default port of the listener utility and so on mentioned in the referenced documents. But here i decided to secure most common tasks of setting a password for listener and logging the listener activity for my initial action.

Setting a listener password:


-bash-3.00$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 14:06:01

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> CHANGE_PASSWORD
Old password: <if not set then just hit enter>
New password: 
Reenter new password: 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Password changed for LISTENER
The command completed successfully
LSNRCTL> SAVE_CONFIG
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Old Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.bak
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 2 hr. 54 min. 43 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit

Logging the listener:


-bash-3.00$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 11:18:43

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> SET LOG_DIRECTORY /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_directory" set to /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
The command completed successfully
LSNRCTL> set log_file lsnr.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_file" set to lsnr.log
The command completed successfully
LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> SAVE_CONFIG
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Old Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.bak
The command completed successfully
LSNRCTL> exit
-bash-3.00$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 11:20:08

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 0 hr. 7 min. 44 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/admin/lsnr_log/lsnr.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

But while i was monitoring the log file of the listener i found some warning messages right before every server process created by listener. The warning is something like;

WARNING: Subscription for node down event still pending

There are some comments on forums for this warning message but the general sentence is setting the

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>

parameter to OFF in listener.ora file. By disabling this option we prevent listener to automatically registers itself to a notification service as it is unsuccessful to do because there is not one installed. Setting this parameter to OFF is worked for my environment and warning messages are cleared. Following you can find my final listener.ora file.


-bash-3.00$ cat listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/OFADB)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx-xx)(PORT = xxxx))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

#----ADDED BY TNSLSNR 03-NOV-2008 11:08:38---
PASSWORDS_LISTENER = xxxxxxxxxxxxxxxx
LOGGING_LISTENER = ON
#--------------------------------------------

#----ADDED BY TNSLSNR 03-NOV-2008 11:19:57---
LOG_FILE_LISTENER = lsnr.log
LOG_DIRECTORY_LISTENER = /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
#--------------------------------------------

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

Another handy operation of setting the password for the listener is remote administration option. The following demonstration is from my client computer to administer the server listener remotely. In a nutshell if you now the ip and port that the listeners runs on and also know the password, then you can remotely administer you listener.


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>lsnrctl status 10.4.38.32:1521

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2008 14:36:02

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=10.4.38.32))(ADDRESS=(PRO
TOCOL=TCP)(HOST=10.4.38.32)(PORT=1521)))
TNS-01169: The listener has not recognized the password
 TNS-01189: The listener could not authenticate the user

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2008 14:36:05

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> status 10.4.38.32:1521
Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=10.4.38.32))(ADDRESS=(PRO
TOCOL=TCP)(HOST=10.4.38.32)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 3 hr. 24 min. 0 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/admin/lsnr_log/lsnr.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Querying Oracle with C# and odp.net

2008-10-07T11:22:00.017+03:00

Oracle recommends odp.net (Oracle Data Provider for .net) for opening and managing connections to an Oracle Database via .NET development environment. Odp.net has its own connection and command objects as well as dataadapter and datareader structures optimized for Oracle database. Here you can find the simple way of connecting and running a command on an Oracle Database v10.2.0.3.

First of all, ODAC should be downloaded from Oracle 's odp.net download page. After installing the software according to the installation instructions you can follow the following snapshots to include .dll file in Microsoft Visual C# IDE. After that we are able to use the classes in that dll.

After importing the .dll file successfully you should easily use the classes.



/*
 * Defining variables
 */

// Connection Object 
private Oracle.DataAccess.Client.OracleConnection ocnn;

// Command object
private Oracle.DataAccess.Client.OracleCommand ocmd;

// Structures to manipulate data
private Oracle.DataAccess.Client.OracleDataAdapter oda;
private Oracle.DataAccess.Client.OracleDataReader odr;
private DataSet ods;


// ------------------------------------------------------------
// After all the definitions are made a sample try-catch block 
// that writes the sysdate to a label should be as follows
// ------------------------------------------------------------

try
    {
 // Creating the connection object
 ocnn = new Oracle.DataAccess.Client.OracleConnection();

 // Defining the connection string to connect to the specified database
 // User id and Password informations are obvious. Data Source value is the TNS alias of the database
 // There can be different values that can be used. Next example will cover another value.
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=GRID_10.4.46.70";

 // Opening the connection and information is written as a label text
 ocnn.Open();
 lblerr.Text = "connected";

 // Now we need a command object that we want to execute on the database
 ocmd = new Oracle.DataAccess.Client.OracleCommand();

 // we defşne the previously created connection as the connection property of the command
 ocmd.Connection = ocnn;

 // commandText will be run on the database
 ocmd.CommandText = "select sysdate from dual";

 // with ExecuteReader() method of the command object we run the command and return the result to a reader object.
 odr = ocmd.ExecuteReader();

 // one or more rows could be fetched from the database
 // so we are readering from the reader of all rows
 while (odr.Read())
 {
     lblerr.Text = lblerr.Text + '-' + odr[0].ToString();
 }

    }
    catch (Exception ex)
    {
 // if an exception raises we catch it here and write to our info label
 lblerr.Text = ex.Message;
    }
    finally 
    { 
 // finally, we cleaning and tidying the memory and the database connections
 if (ocnn != null)
 {
   if (ocnn.State != ConnectionState.Closed)
   {
       ocnn.Close();
       ocnn = null;
   }
 }
    }


// ------------------------------------------------------------------------------------------------
// This block uses standart Net8 connection string instead of a TNS alias and also demonstrates
// the bind variable usage
// ------------------------------------------------------------------------------------------------

try
    {
 ocnn = new Oracle.DataAccess.Client.OracleConnection();
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=(DESCRIPTION= " +
    " (ADDRESS= " +
    " (PROTOCOL=TCP)" +
    " (HOST=10.4.46.70)" +
    " (PORT=1521))" +    
    " (CONNECT_DATA=(SERVICE_NAME=GRID)))";
 ocnn.Open();
 lblerr.Text = "connected";
 
 ods = new DataSet();
 oda = new Oracle.DataAccess.Client.OracleDataAdapter();

 ocmd = new Oracle.DataAccess.Client.OracleCommand();                
 string str_sql = "select * from v$datafile where file# = :f_number ";

 ocmd.CommandType = CommandType.Text;
 ocmd.CommandText = str_sql;
 ocmd.Connection = ocnn;
 
 oda.SelectCommand = ocmd;
 
 // this parameter can also be set at the command level
 oda.SelectCommand.Parameters.Add("f_number",
   Oracle.DataAccess.Client.OracleDbType.Int32,15).Value=2;                

 // another difference is dataset object
 // in the previous example we used datareader to read from the database which fetches the data row by row basis
 // Fill() method of the dataadapter object fetches all the recordset to the client side at one time
 oda.Fill(ods);

 // i will not cover the details of the dataadapter and dataset here
 lblerr.Text = ods.Tables[0].Rows[0]["name"].ToString(); 
    }
    catch (Exception ex)
    {
 lblerr.Text = ex.Message;
    }
    finally
    {
 if (ocnn != null)
 {
     if (ocnn.State != ConnectionState.Closed)
     {
  ocnn.Close();
  ocnn = null;
     }
 }
    }

// ----------------------------------------------------------------------------
// last example demonstrates how to call a Oracle stored procedure with odp.net
// ----------------------------------------------------------------------------

try
    {
     ocnn = new Oracle.DataAccess.Client.OracleConnection();
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=GRID_10.4.46.70;";
 ocnn.Open();
 lblerr.Text = "connected";

 oda = new Oracle.DataAccess.Client.OracleDataAdapter();
 ods = new DataSet();

 ocmd = new Oracle.DataAccess.Client.OracleCommand();
 ocmd.Connection = ocnn;
 ocmd.CommandText = "epeker.p_return_cursor";

 // this time command type is a StoredProcedure
 ocmd.CommandType = CommandType.StoredProcedure;

 // this time parameters have directions
 ocmd.Parameters.Add("c_return", 
   Oracle.DataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
 ocmd.Parameters.Add("i_var", 
   Oracle.DataAccess.Client.OracleDbType.Int32).Direction = ParameterDirection.Input;
 ocmd.Parameters["i_var"].Value = 2;

 oda.SelectCommand = ocmd;

 oda.Fill(ods);

 // gw_result is a gridview object of .NET framework
 // helps to view the recordset as a table
 gw_result.DataSource = ods.Tables[0];
 gw_result.Refresh();

    }
    catch (Exception ex)
    {
 lblerr.Text = ex.Message;
    }
    finally
    {
 if (ocnn != null)
 {
     if (ocnn.State != ConnectionState.Closed)
     {
  ocnn.Close();
  ocnn = null;
     }
 }
    }


/*
---------------------------------------------
--stored procedure used with the last example
---------------------------------------------

-- package needed for the ref_type declaration
CREATE OR REPLACE package EPEKER.pck_ref_type as 
  type ref_type is ref cursor; 
end pck_ref_type;

-- procedure itself
CREATE OR REPLACE procedure EPEKER.p_return_cursor 
  (c_return out epeker.pck_ref_type.ref_type, i_var in number)
is
begin

  open c_return for
    select * from v$datafile where file#=i_var;

  exception
    when others then
      rollback;

end p_return_cursor;
*/

Some other articles for .NET
odp.net homepage

sql loader performance experiences

2008-09-02T11:13:00.004+03:00

Lots of the reporting databases has data loading scripts, using oracle SQL Loader to load mass amount of data from a text file. Loaded data is used for reporting purposes in day time. Commonly reported problem is long running loader scripts and solving this kind of problems has genarally similar approaches.

1- Dropping unnecessary indexes improves the performance of the batch loader operations hence every insert needs to update the indexes after updating the table. Fewer indexes means fewer operation for the DML statements. In my databases i examine v$sql, v$sqlarea, v$sqltext and dba_hist_sqltext views to examine the daily routine of the executed sql statements on the database to find out the index usage percentage of the statements. This gives a brief information about the index usage and to decide if i can drop any.

2- Using nologging option for the tables generates less redo logs. If the database in archive mode then less redo means less work for the archiver. log writer related wait events can also be improved. Using this option causes database not to log the operation and database admin should be cautious about the backup and restore operations. Bacause a recover operation may not recover your nologging tables data. I prefer full and incremental backups with rman in this kind of databases.

3- By setting Direct=true option of the sqlloader direct can be enabled, which loads data directly to datafiles by bypassing most of the database operations.

4- Sometimes table data is deleted periodically. This delete operation fragments the segments of the table and indexes of the table. Moving the tables and the indexes defragments the segments. If the table is fragmented, i prefer dropping the indexes first and move the table and then recreate the indexes again. One of my early postings covers this topic.

There are some other parameters; rows (row size to be commited) and unrecoverable (if used with direct=true option, redo writes are bypassed) parameters for further enhancement for loader performance. But i frequently use the mentioned parameters and they worked more than enough for most cases.

srvctl problem after 10.2.0.4 patch

2008-08-12T14:26:00.004+03:00

New migration project of the database and system group in our company is from Oracle Database 9.2.0.5 RAC on LINUX AS3 32 bit to Oracle Database RAC 10.2.0.4 on LINUX ES4 Update6 64 bit. After installing clusterware and the database 10.2.0.1 successfully, i decided to upgrade the database and the clusterware to the last level of patchset 10.2.0.4. After installing the patchset i realised that srvctl cannot open the databases with the command srvctl start database -d PQ. But i can open the databases individually with the sqlplus. The wierd thing is srvctl works with "status" and "close" parameters. When i examine the crs logs i founded the following error logs.


#cat /oracle/product/crs/log/pqvrtsrv1/crsd/crsd.log
2008-07-16 17:52:37.840: [  CRSAPP][1535326560]0StartResource error for ora.PQ.db error code = 1
2008-07-16 17:52:37.892: [  CRSRES][1535326560]0Start of `ora.PQ.db` on member `pqvrtsrv1` failed.

This one error message did not satisfy me to start taking action for the problem hence it has too few information to make a conclusion. I decided to look at the databases alert.log, if i can find any clue about the problem. But the last logs written to the alert.log was the databases closing messages. This may be a clue, because this means srvctl cannot reach the database to open it, now i can start from srvctl settings to examine.


$ srvctl config database -d PQ -a
pqvrtsrv1 PQ1 /oracle/product/db10g
pqvrtsrv2 PQ2 /oracle/product/db10g
DB_NAME: PQ
ORACLE_HOME: /oracle/product/db10g
SPFILE: /pqdata1/PQ/PQ/spfilePQ.ora
DOMAIN: null
DB_ROLE: null
START_OPTIONS: null
POLICY:  AUTOMATIC
ENABLE FLAG: DB ENABLED

Now i can see things. SPFILE configuration is wrong for this database. I clearly remember that i have changed the spfile location to /pqdata1/PQ/spfilePQ.ora. But somehow after the patch is installed, setting are confused. I changed the SPFILE configuration with the correct settings and my problem is now solved.


#srvctl modify database -d PQ -p /pqdata1/PQ/spfilePQ.ora
#srvctl start instance -d PQ -i PQ2
#srvctl status database -d PQ
Instance PQ1 is running on node pqvrtsrv1
Instance PQ2 is running on node pqvrtsrv2

Resources:

Server Control Utility Reference

Changing sysman password in OMS

2008-08-05T14:20:00.004+03:00

I had to change the sysman password today. There are nice articles on the web but in summary, if you want to change the sysman password of the Oracle Enterprise Manager Grid Control Server;

1- stop all opmn processes
2- stop the oms
3- stop the agent
4- change the password of the sysman user
5- update the $AGENT_HOME/sysman/emd/targets.xml file
6- update the $OMS_HOME/sysman/config/emoms.properties file
7- start the agent
8- start the oms
9- start all opmn processes (actually they are being started by oms)

Here are the handy articles i used;

http://oraclespin.wordpress.com/2008/07/18/how-to-change-sysman-password-in-oem-oms/
http://thetendjee.wordpress.com/2006/07/19/changing-sysman-and-dbsnmp-passwords-it-aint-that-easy/
http://dbataj.blogspot.com/2007/07/change-sysman-password.html

Problem with CRS startup after disk migration

2008-07-29T12:18:00.001+03:00

Our unix and storage group has a project for migration from old storage subsystem to a brand new one. Action plan was simple;

1- shutdown the database with

srvctl stop database -d

2- shutdown the crs with

crsctl stop crs

3- move the data from old disks to new ones
4- rename the new disks with the original names
5- open the crs with

crsctl start crs

6- startup the database with

srvctl start database -d

But right after step 4, crs was unable to open and I am called at night. When i tried to start crs with crsctl start crs, i had errors indicates crs was unable to reach the OCR disk. I tried to check the OCR with ocrcheck and trace the log file of the OCR in $CRS_HOME/log//client/


[root@raquality00 client]$ pwd
/oracle/product/10.2.0/crs/log/raquality00/client
[root@raquality00 client]# cat ocrcheck_13416.log
Oracle Database 10g CRS Release 10.2.0.1.0 Production Copyright 1996, 2005 Oracle.  All rights reserved.
2008-06-18 02:13:39.487: [OCRCHECK][2538462912]ocrcheck starts...
2008-06-18 02:13:39.488: [  OCROSD][2538462912]utstoragetype: /oradata1/orcfile.ora is on FS type 1952539503. Not supported.
2008-06-18 02:13:39.488: [  OCROSD][2538462912]utopen:6'': OCR location /oradata1/orcfile.ora configured is not valid storage type. Return code [37].
2008-06-18 02:13:39.488: [  OCRRAW][2538462912]proprinit: Could not open raw device 
2008-06-18 02:13:39.488: [ default][2538462912]a_init:7!: Backend init unsuccessful : [37]
2008-06-18 02:13:39.488: [OCRCHECK][2538462912]Failed to initialize OCR context: [PROC-37: Oracle Cluster Registry does not support the storage type configured]
2008-06-18 02:13:39.488: [OCRCHECK][2538462912]Failed to initialize ocrchek2
2008-06-18 02:13:39.488: [OCRCHECK][2538462912]Exiting [status=failed]...

Log file says there is a problem with the ocrfile.ora. But I am suspicious if the filename is true. I should be sure with the filename so I examine the install logs in $CRS_HOME/install/ directory.


[oracle@raquality00 install]$ pwd
/oracle/product/10.2.0/crs/install
[oracle@raquality00 install]$ cat paramfile.crs
ORA_CRS_HOME=/oracle/product/10.2.0/crs
CRS_ORACLE_OWNER=oracle
CRS_DBA_GROUP=oinstall
CRS_VNDR_CLUSTER=false
CRS_OCR_LOCATIONS=/oradata1/orcfile.ora
CRS_CLUSTER_NAME=raqcrs
CRS_HOST_NAME_LIST=raquality00,1,raquality01,2
CRS_NODE_NAME_LIST=raquality00,1,raquality01,2
CRS_PRIVATE_NAME_LIST=raquality00-priv,1,raquality01-priv,2
CRS_LANGUAGE_ID='AMERICAN_AMERICA.WE8ISO8859P1'
CRS_VOTING_DISKS=/oradata1/votingfile.ora
CRS_NODELIST=raquality00,raquality01
CRS_NODEVIPS='raquality00/raquality00-vip/255.255.255.0/eth0,raquality01/raquality01-vip/255.255.255.0/eth0'

Lets see if the location of the ocr file is changed by someone after installation by inspecting the /etc/oracle/ocr.loc.


[oracle@raquality00 oracle]$ pwd
/etc/oracle
[oracle@raquality00 oracle]$ cat ocr.loc
ocrconfig_loc=/oradata1/orcfile.ora
local_only=FALSE

OCR location and the filename seems correct. Now we now it hasnt been changed after the installation. It seems there may be corruption with the OCR file. Checking the backups of the OCR is handy. Maybe I will need to restore from the backup. The command I runned is ocrconfig -showbackup. The output shows the case goes bad.


[root@raquality00 client]# cat ocrconfig_9347.log
Oracle Database 10g CRS Release 10.2.0.1.0 Production Copyright 1996, 2005 Oracle.  All rights reserved.
2008-06-18 02:04:20.350: [ OCRCONF][2538462912]ocrconfig starts...
2008-06-18 02:04:20.353: [  OCROSD][2538462912]utstoragetype: /oradata1/orcfile.ora is on FS type 1952539503. Not supported.
2008-06-18 02:04:20.353: [  OCROSD][2538462912]utopen:6'': OCR location /oradata1/orcfile.ora configured is not valid storage type. Return code [37].
2008-06-18 02:04:20.353: [  OCRRAW][2538462912]proprcow: problem reading the bootblock
2008-06-18 02:04:20.353: [ OCRCONF][2538462912]Failure in overwriting OCR configuration on disk
2008-06-18 02:04:20.353: [ OCRCONF][2538462912]Exiting [status=failed]...

I am now getting doubt. Maybe there is a problem about the filesystems. I decided to check the filesystem types from fstab. But they seem OK.


[root@raquality00 raqcrs]# cat /etc/fstab
# This file is edited by fstab-sync - see 'man fstab-sync' for details
LABEL=/1                /                       ext3    defaults        1 1
LABEL=/boot1            /boot                   ext3    defaults        1 2
#LABEL=/oradata          /oradata1              ocfs2   _netdev,datavolume,nointr       0 0
LABEL=/oradata1_new     /oradata1               ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata2         /oradata2              ocfs2   _netdev,datavolume,nointr       0 0
LABEL=/oradata2_new     /oradata2               ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata3         /oradata3              ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata4         /oradata4              ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata5         /oradata5              ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata6         /oradata6              ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata7         /oradata7              ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata8         /oradata8              ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata9         /oradata9              ocfs2   _netdev,datavolume,nointr       0 0
LABEL=/oradata9_new     /oradata9               ocfs2   _netdev,datavolume,nointr       0 0
LABEL=/oradata10_new    /oradata10              ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata11        /oradata11             ocfs2   _netdev,datavolume,nointr       0 0
LABEL=/oradata11_new    /oradata11              ocfs2   _netdev,datavolume,nointr       0 0
#LABEL=/oradata12        /oradata12             ocfs2   _netdev,datavolume,nointr       0 0
LABEL=/oradata12_new    /oradata12              ocfs2   _netdev,datavolume,nointr       0 0
#/dev/mapper/vgra-lvra01        /ra01                   ext3    defaults        1 2
/dev/vgra00/lvra01      /ra01_new               ext3    defaults        1 2
none                    /dev/pts                devpts  gid=5,mode=620  0 0
none                    /dev/shm                tmpfs   defaults        0 0
none                    /proc                   proc    defaults        0 0
none                    /sys                    sysfs   defaults        0 0
LABEL=SWAP-sda2         swap                    swap    defaults        0 0
/dev/sdb1               swap                    swap    defaults        0 0
#/dev/vgora/lvora       /oracle                 ext3    defaults        1 2
/dev/vgra00/lvoracle    /oracle                 ext3    defaults        1 2
/dev/hda                /media/cdrom            auto    pamconsole,exec,noauto,managed 0 0

When i recheck the error logs of ocrcheck command i realised that there are some other error notifications after invalid storage type error line. OCR complains about reading bootblock and overwriting the file. There maybe a process holding the file.


[oracle@raquality00 install]$ ps -ef | grep crs
root     10239  7791  0 Jun18 ?        00:37:59 /oracle/product/10.2.0/crs/bin/crsd.bin reboot

Now it is more clear, maybe our admins forgot to disable crs service by executing

/etc/init.d/init.crs stop

after stopping crs with

crsctl stop crs

. And data migration is made with the running crs service. I recommended a reboot but stopping the the service may also be OK. After the reboot crs is opened successfully and databases are now up and running.


[oracle@raquality00 install]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

[oracle@raquality00 install]$ srvctl status database -d RADB
Instance RADB1 is running on node raquality00
Instance RADB2 is running on node raquality01

Alter the table to change the structure

2008-07-05T21:09:00.004+03:00

What will happen if one of the source files structure changes, and all data loading procedures and "sql loader" scripts are ready after long and busy working days. Oracle has options to change the table structure.

Altering a column


create table EPEKER.ep_test (col1 varchar(10));

insert into  EPEKER.ep_test values ('some text here');
--ORA-12899: value too large for column "EPEKER"."EP_TEST"."COL1" (actual: 14, maximum: 10)

alter table EPEKER.ep_test modify (col1 varchar2(20));
insert into  EPEKER.ep_test values ('some text here');
--1 row inserted
commit;

select * from EPEKER.ep_test;

Adding/Dropping a column


alter table EPEKER.ep_test add (col2 date);
alter table EPEKER.ep_test add (col2 date DEFAULT sysdate);

alter table EPEKER.ep_test drop (col2);

Renaming Columns


alter table EPEKER.ep_test rename column col3 to col4;

Changing Datatypes

But changing datatype of a column is not that easy. As the following example shows to change a datatype of a column, the column to be modified must be empty.


insert into EPEKER.ep_test (col3) values ('123')
commit;
alter table EPEKER.ep_test modify (col3 number)
--ORA-01439: column to be modified must be empty to change datatype

update EPEKER.ep_test set col3=NULL;
commit;
alter table EPEKER.ep_test modify (col3 number)

desc EPEKER.ep_test

Compressed Tables

You can modify and add columns to compressed tables. But dropping a column is still not supported (v10.2). To drop a column one should first MOVE NOCOMPRESS, drop the column and again MOVE COMPRESS a table.


alter table EPEKER.ep_test move compress;
alter table EPEKER.ep_test add (col3 varchar2(10));
alter table EPEKER.ep_test modify (col3 varchar2(20));

alter table EPEKER.ep_test drop (col3);
--ORA-39726: unsupported add/drop column operation on compressed tables

Oracle EM Grid Control - Custom Reports

2008-06-18T15:22:00.022+03:00

Creating Custom Grid Report for database availability

Oracle Enterprise Manager GRID Server contains built in reports to support Database Administrators to generate sightly reports. However, in my company, we need some reports that cannot be find in GRID and needs custom development. Here is how i did it.

First Thing is to click to "Reports" tab on the top, right hand side of the GRID Management Console (figure_grid_management_console). This page is where all the predefined and custom reports can be find. Now click on the "Create" Button on top of the page to create a new custom report. "Create Report Definition" page comes to the screen. There are four tabs for the definition of the report.

General
After specifiying title of the report then select category and subcategory from the comboboxes. Category and subcategory defines our newly custom report's group and standing place in the reports list of the GRID reports page. You can also create new categories by clicking the buttons between the comboboxes.

Targets section enables you to limit the available list of target selection while generating the report or you can select a specified target to be used. I used "Specified Target" and left the textbox blank because this report will apply to all database and rac targets and is not allowed to be changed.

You can enable to select time periods by end user or you can preset the time period from the "Time Period" section (figure_create_report_general_tab). This custom report always displays the last known and most updated state of the databases uptimes and downtimes.

Elements
In the Elements tab, you can define the report elements and their positions in the report page (figure_add_elements_1). Report elements are structures that displays selected data from the GRID Servers repository. Click "Add" Button to add elements. I used IPMSG_USER_CHART_FROM_SQL and IPMSG_USER_TABLE_FROM_SQL elements (figure_add_elements_2). Click on the "Set Parameters" icon on the right hand side of the listed items to obtain a statement for the elements (figure_add_elements_2). Here you can enter the sql statement that brings data into the element from Management Servers repository. Following you can find the sql statements for the elements.

IPMSG_USER_TABLE_FROM_SQL


select
 target_name,
 target_type,
 sum(up_time) up_time,
 sum(down_time) down_time,
 sum(blackout_time) blackout_time,
 trunc(sum(up_time)/(sum(nvl(up_time,1))+sum(nvl(down_time,1)))*100) availability_pct
from
(
select
 target_name,
 target_type,
 sum(trunc((nvl(end_timestamp,sysdate)-start_timestamp)*24)) total_hours,
 case availability_status
   when 'Target Down' then
     0
   when 'Target Up' then
     0
   when 'Blackout' then
     sum(trunc((nvl(end_timestamp,sysdate)-start_timestamp)*24))
 end blackout_time,
 case availability_status
   when 'Target Down' then
     0
   when 'Target Up' then
     sum(trunc((nvl(end_timestamp,sysdate)-start_timestamp)*24))
   when 'Blackout' then
     0
 end up_time, 
 case availability_status
   when 'Target Down' then
     sum(trunc((nvl(end_timestamp,sysdate)-start_timestamp)*24))
   when 'Target Up' then
     0
   when 'Blackout' then
     0
 end down_time,
 availability_status
from
 MGMT$AVAILABILITY_HISTORY
where
 target_type in ('oracle_database','rac_database') and
 availability_status in ('Target Down','Target Up','Blackout')
group by
 target_name, target_type, availability_status
order by target_name, availability_status
)
group by target_name, target_type
order by target_name

--sample output:

TARGET_NAME    TARGET_TYPE     UP_TIME   DOWN_TIME    BLACKOUT_TIME  AVAILABILITY_PCT
-----------    -----------     -------   ---------    -------------  ----------------
ACTV           rac_database    4823      0            1                100
ACTV_ACTV1     oracle_database 4823      0            1                100
ACTV_ACTV2     oracle_database 3964      0            1                100
ARCH.AVEA.COM  oracle_database 3334      0            0                100
ARCHCM         oracle_database 2210      1126         0                66
ARCHDB         rac_database    3858      0            653              100
ARCHDB_ARCHDB1 oracle_database 3514      31           652              99
ARCHDB_ARCHDB2 oracle_database 3725      0            652              100
ASPDB          oracle_database 1750      2            0                99
...

IPMSG_USER_CHART_FROM_SQL


select
 availability_status,
 sum(trunc((end_timestamp-start_timestamp)*24)) total_hours 
from
 MGMT$AVAILABILITY_HISTORY
where
 target_type in ('oracle_database','rac_database') and
 availability_status in ('Target Down','Target Up','Blackout')
group by
 availability_status

--sample output:

AVAILABILITY_STATUS   TOTAL_HOURS
-------------------   -----------
Target Up             130008
Target Down           2086
Blackout              2405

Schedule
You can schedule a report to be emailed to an email by setting schedules (figure_schedule_tab).

Access
Report creator can define user groups or users who will be allowed to access this report. Here i used user groups (figure_access_tab).

Database availability report is now ready for managers

deleting from table causes fragmentation

2008-06-13T15:19:00.006+03:00

One of our application groups periodically deletes data, which are older than 10 days, from some tables. This periodic action splits the table blocks to a wide range of datafile blocks. This causes performance problems on daily data loading and daytime reporting.

As the first action plan gather statistics for trusted analysis.

begin
dbms_stats.gather_table_stats(
    ownname=>'PQ',
    tabname=>'GPRS_CELL_GPRS',
    estimate_percent=>33,
    degree=>2,
    cascade=>true);
end;

You can see the space fragmentation from the following. Currently the tables consumes nearly 3Gb 's of datafile usage. But if you calculate the average row length with the number of table rows, datafile usage should be 328Mb.


select ceil((blocks*8)/1024) as table_mb from dba_tables 
    where table_name='GPRS_CELLGPRS' and owner='PQ'
/*
TABLE_MB
2926
*/
select ceil((avg_row_len*num_rows)/1024/1024) as table_mb from dba_tables 
    where table_name='GPRS_CELLGPRS' and owner='PQ'
/*
TABLE_MB
328
*/

After this finding, following procedure is set to run periodically to delete 10 days older data from the table. After the delete operation table is moved for defragmentation. Table indexes should also be rebuilded. After the move and rebuild operation statistics should also be gathered. Keep in mind that table move and index rebuild operations blocks user access.


create or replace procedure avea_sysmon.P_TABLE_MAINTENANCE
  (g_table_name varchar2, g_owner varchar2) AUTHID CURRENT_USER as 
  
  cursor c1 is
    select * from sys.dba_indexes where table_owner=g_owner and table_name=g_table_name;

  r1 c1%rowtype;  
begin
    
    execute immediate 'delete from PQ.GPRS_TRAFGPRS3 where ' || 
                      'to_number(substr(CDATE,1,6)) < to_char(sysdate-15,''YYMMDD'')';
    commit; 
    execute immediate 'alter table ' || g_owner || '.' || g_table_name || ' move';
    
    open c1;    
    loop
      fetch c1 into r1;
      exit when c1%NOTFOUND;  
      execute immediate 'alter index ' || r1.table_owner || '.' || 
                         r1.index_name || '  rebuild';      
    end loop;
    
    dbms_stats.gather_table_stats(ownname=>g_owner,tabname=>g_table_name,
                                  estimate_percent=>33,cascade=>true);
    
end P_TABLE_MAINTENANCE;

FOLLOW UP: (23.11.2010)

With Oracle10g there is a new way of reorganising your table segments. The best thing is there is not any unusable indexes anymore.


select owner, table_name, 
  trunc((avg_row_len*num_rows)/1024/1024) as ROWS_MB, 
  trunc((blocks*8192)/1024/1024) as TOTAL_MB 
  from dba_tab_statistics where table_name = 'WIZ_CUSTOMER_MAILBOX';
--OWNER, TABLE_ANME, ROWS_MB, TOTAL_MB
--PROD_DBA,WIZ_CUSTOMER_MAILBOX,19982,29790

alter table PROD_DBA.WIZ_CUSTOMER_MAILBOX enable row movement;
alter table PROD_DBA.WIZ_CUSTOMER_MAILBOX shrink space compact;

select owner, table_name, 
  trunc((avg_row_len*num_rows)/1024/1024) as ROWS_MB, 
  trunc((blocks*8192)/1024/1024) as TOTAL_MB 
  from dba_tab_statistics where table_name = 'WIZ_CUSTOMER_MAILBOX';
--OWNER, TABLE_ANME, ROWS_MB, TOTAL_MB
--PROD_DBA,WIZ_CUSTOMER_MAILBOX,19982,26790

A Security Hardening Approach for userproof databases

2008-05-26T21:00:00.015+03:00

What if you have some confusion about some of your database users and want to trace the users or even be notified about their actions in the database or just want to warn them about probable mistakes can be made by db users. Oracle Database software has some solutions about likely cases. Auditing is one of the most used option with tracing. DDL triggers can also be used. There are also some solutions at the opsys level.

1- Logon Triggers to Trace Users
Oracle database has logon triggers to run on any users login to the database. DBA can use this feature to set a trace file for the logged in user. tkprof utility can be used to examine users activity.


CREATE OR REPLACE TRIGGER SYS.TRG_SESSION_LOGON after logon on database
declare
  s_username varchar2(20);
  s_sessid varchar2(20);
  s_stamp varchar2(20);
begin

  -- obtain sid of the session
  select distinct(substr(sid,1,20)) into s_sessid from v$mystat;

  -- obtain username of the session
  select username into s_username from v$session where sid=s_sessid;

  -- create a timestamp to uniquely identify the trace file
  select to_char(sysdate,'DDMMYYYY_HH24MISS') into s_stamp from dual;

  -- set the tracefile size to unlimited. careful about that (!)
  execute immediate 'alter session set max_dump_file_size=unlimited';

  -- define an identifier to the tracefile to distinguish the trace files
  execute immediate 'alter session set tracefile_identifier=' || s_username || 
                    '_' || s_sessid || '_' || s_stamp;

  -- i will take 10046 trace level 8 to see the waits 
  -- (check timed_statistics init parameter!)
  execute immediate 'alter session set events ''10046 trace name context forever, level 8''';

  -- finally start the trace
  execute immediate 'alter session set sql_trace=true';
  
  exception
    when others then
      raise;
    
end TRG_LOGON;

You can find the trace files from user_dump_dest parameter


SQL> show parameter user_dump_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      D:\ORACLEDB10G\ADMIN\ORCL\UDUMP

To examine the dump file tkprof utility can be used.


tkprof orcl_ora_5412_epeker_148_22052008_162752.trc tkprof.out

tkprof.out file.

2- Database Audit
To enable audit option, audit_trail init parameter should be set.


select name, value from v$parameter where name = 'audit_trail';
alter system set audit_trail='DB_EXTENTED' scope=spfile;
--After changing audit_trail parameter bounce the database.

audit select table, insert table, update table, delete table by epeker;
audit session by epeker;

Now login to the database with the audited user and make some ddl.


select * from EPEKER.T_TABLE_DBA_TABLES;
insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10;
commit;
select * from EPEKER.T_TABLE_DBA_TABLES;

All the audit information should be in the SYS.AUD$ table


select 
  SESSIONID, USERID, USERHOST,TERMINAL, OBJ$CREATOR, OBJ$NAME, NTIMESTAMP#, SCN, SQLTEXT 
from 
  SYS.AUD$
order by NTIMESTAMP# desc;


/*
SESSIONID  USERID     OBJ$CREATOR OBJ$NAME           NTIMESTAMP#           SCN    SQLTEXT
1249       EPEKER     SYS,        USER$              5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        TS$                5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        SEG$               5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        X$KSPPCV           5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        TAB$               5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        OBJ$               5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        DBA_TABLES         5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        X$KSPPI            5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     EPEKER,     T_TABLE_DBA_TABLES 5/23/2008 11:44:10 AM 935944 select * from T_TABLE_DBA_TABLES
1268       EPEKER                                    5/23/2008 12:01:56 PM       
*/

You can revoke the audit by noaudit command.


noaudit select table, insert table, update table, delete table by epeker;
noaudit session by epeker;

3- DDL Triggers
Oracle database has many different trigger options. You have just read about "logon triggers". There is also ddl triggers can be used to disable specific users from mistakenly running dangerous ddl statements. Any user wants to execute a ddl statment (create, truncate.. etc) can be logged and the user can also be warned by the administrator. The following trigger simply disables a user to drop and/or truncate database tables.


CREATE OR REPLACE TRIGGER SYS.TRG_DDL before ddl
ON DATABASE
declare
  s_sysevent varchar2(32);
  s_owner varchar2(32);
  s_objname varchar2(32);
  s_user varchar2(32);
begin

  select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, USER 
    into s_sysevent, s_owner, s_objname, s_user from dual;
    
  insert into epeker.t_trg_ddl_test values (ora_sysevent);
  if s_sysevent in ('DROP','TRUNCATE') then
    if s_user = 'EPEKER' then
      raise_application_error(-20010,'You are not authorized to drop a segment!.. ');
      null;
    end if;
  end if;
  
end TRG_DDL;

4- database privileges
Privileges is important (maybe the most important) checklist to be careful about. Privileges and Roles should be carefully assgined and also revoked from users to minimize the mistakenly made data loss in the databases.

5- opsys suggested tasks (alias, sudo, execute privileges, op.sys groups)
Beside database auditing tasks, some securtiy hardening can be made in operating system side. Aliases can be used as of direct op. sys. commands for auditing and preventing mistakes by end users or application users. Some commands need root privileges, this kind of commands can be run through sudo in unix. Application users should not be in dba group and Oracle Home and datafiles should be protected with op. sys. file permissions. Grid agent like management and monitoring tools can be used to audit or proactively solve the likely problems.

Table Monitoring worked well

2008-05-19T14:01:00.006+03:00

I was logged in one of our forgotten databases to partition some tables and maintain dba routines. After all my work is done i decided to examine the tables which are not used any more.

First thing is to find if it worths to spend some time on it. I checked the sizes of the tables and found that one of the unused tables consume approximately 21Gb of space. That should be so valuable to drop this table on this database because of the problematic disk space usage.


select 
  segment_name, sum(bytes/1024/1024) as MB
from 
  dba_segments 
where 
  segment_name in ('WAP_STATREC','PAYFORME_TEMP','PAY4ME_TEMP') and 
  owner ='PQ'
group by 
  segment_name
order by MB desc;

SEGMENT_NAME    MB
------------    --
WAP_STATREC     21443
PAYFORME_TEMP   145
PAY4ME_TEMP     1

To obtain if these tables are actively used by developers or any application, i altered these tables to enable monitoring and logout the system.


alter table pq.wap_statrec monitoring
alter table pq.payforme_temp monitoring
alter table pq.pay4me_temp monitoring

Today, when i remembered what have i done for a few weeks earlier, i logged in the database and check the dba_tab_modifications management view to find if any modifications done to the tables.


select
  table_owner, table_name, inserts, updates, deletes, timestamp, truncated
from 
  sys.dba_tab_modifications 
where 
  table_name in ('WAP_STATREC','PAYFORME_TEMP','PAY4ME_TEMP');

TABLE_OWNER TABLE_NAME    INSERTS  UPDATES DELETES TIMESTAMP  TRUNCATED
----------- ----------    -------  ------- ------- ---------  ---------
PQ          PAYFORME_TEMP 10695476 0       0       5/7/2008   YES

As you can see only one of the tables is actively used by an application and the others are not used for 2 weeks. Now time to talk with the application operation or development group to drop their unused tables.

Handling Partitioned Tables

2008-05-06T18:43:00.009+03:00

Subject: We have lots of partitioned tables to handle manually. Every begining of the month or year we have add new partitions to prevent "ORA-14400: inserted partition key does not map to any partition". As we have a increasing number of partitioned tables, we faced this problem increasingly for last months.

I decided to write a plsql script that runs in period by a scheduled job. You can find the scheduled job settings in my previous postings. In this posting i will share the procedure that automatically creates the new months partition, archives, compresses and deletes the older partitions by the supplied retention.


CREATE OR REPLACE procedure AVEA_SYSMON.p_check_partition2
  (s_table_name IN varchar2, 
   s_schema_name IN varchar2, 
   s_archive_ts IN varchar2, 
   n_archive_ret number, 
   n_delete_ret number) 
/*As i run this procedure from a job. I want running user to own the privileges*/
AUTHID CURRENT_USER 
AS 
  s_compression varchar2(20);
  s_ts_name varchar2(20);
  
  n_max_month number;
  n_max_year number;
    
  n_next_month number;
  n_next_year number;
  
  n_this_year number;
  n_this_month number;
  
  part_next_month number;
  part_next_year number;
  
  n_ctl number;     
  
  d_next_month date;
  s_part_less_than varchar2(200);
  s_cmd varchar2(200);   
begin
  
  /*I will read the dba_tab_partitions management view to find the table partitions. 
  This procedure works with the formatted name of the partition. 
  Here you should have YYYYMM at the end of the partition name*/
  select 
    max(substr(partition_name,-6,4)) into n_max_year
  from 
    dba_tab_partitions t1 
  where 
    table_owner=s_schema_name and 
    table_name=s_table_name;
  
  -- get max month of table partition
  select 
    max(substr(partition_name,-2,2)) into n_max_month
  from 
    dba_tab_partitions t1 
  where 
    table_owner=s_schema_name and 
    table_name=s_table_name and 
    substr(partition_name,-6,4) = n_max_year;        

  -- find this month this year;
  n_this_year := to_number(to_char(sysdate,'YYYY'));
  n_this_month := to_number(to_char(sysdate,'MM'));
  
  -- find next month next year
  n_next_year := to_number(to_char(add_months(sysdate,1),'YYYY'));
  n_next_month := to_number(to_char(add_months(sysdate,1),'MM'));

  -- partition definition for next month next year should be one more
  part_next_year := to_number(to_char(add_months(sysdate,2),'YYYY'));
  part_next_month :=  to_number(to_char(add_months(sysdate,2),'MM'));

  
  -- check if next months partition exists, create it if not exists

if mod(n_this_month,12) = mod(n_max_month,12) then

s_part_less_than := 'to_date(''01.' || trim(to_char(part_next_month,'09')) || '.' || part_next_year || ''',''DD.MM.YYYY'')';

s_cmd:='alter table ' || s_schema_name || '.' || s_table_name || ' add partition ' || s_table_name || '_' || n_next_year || '_' || trim(to_char(n_next_month,'09')) || ' values less than (' || s_part_less_than || ') ';


      --dbms_output.put_line(s_cmd);
      begin
        execute immediate (s_cmd);
      exception
        when others then
          null;
      end; 
  end if;
  
  /*compress and move partitions to archive tablespace which are  
  months older*/
  begin    
      select 
        compression, tablespace_name into s_compression, s_ts_name
      from 
        dba_tab_partitions 
      where 
        table_owner=s_schema_name and 
        table_name=s_table_name and 
        substr(partition_name,-6,4)=to_char(add_months(sysdate,n_archive_ret),'YYYY') and 
        substr(partition_name,-2,2)=to_char(add_months(sysdate,n_archive_ret),'MM');
      
      /*first i should be sure if the partition to be compressed and archived 
      is exists. And move the partition to archive tablespace if it is not already 
      there and compress if it is not already compresses*/
      if s_ts_name!=s_archive_ts then
         s_cmd := 'alter table ' || s_schema_name || '.' || s_table_name || 
                  ' move partition ' || s_table_name || '_' || 
                  trim(to_char(add_months(sysdate,n_archive_ret),'YYYY')) || '' || 
                  trim(to_char(add_months(sysdate,n_archive_ret),'MM')) || 
                  ' tablespace ' || s_archive_ts;
         if s_compression!='ENABLED' then
           s_cmd := s_cmd || ' COMPRESS';              
        end if;
        s_cmd := s_cmd || ' update global indexes';
        execute immediate (s_cmd);
      end if;           
 
      s_cmd:='';
  exception
    when no_data_found then 
        null;        
  end;
  
  -- drop partitions of  year old
  select 
    count(*) into n_ctl
  from 
    dba_tab_partitions 
  where 
    table_owner=s_schema_name and 
    table_name=s_table_name and 
    substr(partition_name,-6,4)=to_char(add_months(sysdate,n_delete_ret),'YYYY') and 
    substr(partition_name,-2,2)=to_char(add_months(sysdate,n_delete_ret),'MM');  
  
  /* if the partition to be dropped exists then drop it */
  if n_ctl=1 then
      s_cmd := 'alter table ' || s_schema_name || '.' || s_table_name || 
               ' drop partition ' || s_table_name || '_' || 
               to_char(add_months(sysdate,n_delete_ret),'YYYY') || '' || 
               to_char(add_months(sysdate,n_delete_ret),'MM') || 
               ' update global indexes';
     
      execute immediate (s_cmd);
  end if; 
   
end p_check_partition2;
/

The procedure is OK now. You should now be careful to give the right parameters. Be sure not to drop something should not be dropped. In our site our delete retention is one year and archive retention is 3 months. I run this procedure like the following parameters;


begin
  p_check_partition2('PAYFORME','PQ','PQ_ARCHIVE',-3,-12);
end;

Resetting Timezone of a Grid Agent 10.2

2008-04-16T23:49:00.010+03:00

Problem: Operating system Timezone is changed after an Oracle Grid Agent installation. This problem occured on an IBM AIX 5.3 host. Maybe one of our UNIX administrators decided to change the Timezone setting $TZ in our UNIX environment. Our Oracle Grid Agent couldnt be opened after that.

#> $AGENT_HOME/bin/emctl start agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ....... failed.
The agentTZRegion value in /oracle/product/10.2.0/agent/agent10g/dwhnserp0/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.
If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(, ) to get the value propagated to repository.
Consult the log files in: /oracle/product/10.2.0/agent/agent10g/dwhnserp0/sysman/log

#> echo $TZ
MEST-2MEDT,M3.5.0,M10.5.0

#> export TZ=+2:00

#> $AGENT_HOME/bin/emctl resetTZ agent

emctl resetTZ agent command automatically changes the agentTZRegion entry of the emd.properties file. But here I also changed the last row of the file $AGENT_HOME/sysman/config/emd.properties to '+2:00' manually.
#agentTZRegion=Etc/GMT-3
agentTZRegion=+2:00

Now on the Grid Server run the following command. First parameter is the name of the target, and the second one is timezone value. Name of the target is usually nodename:portnumber.


#> sqlplus "/ as sysdba"
sqlplus> exec mgmt_target.set_agent_tzrgn('dwhnserp0:3872', '-2:00');
sqlplus> exit;
#>

You can also check the registered timezone property of the target with the following query. Again from the Grid Server:


#> sqlplus "/ as sysdba"
sqlplus> select timezone_region from mgmt_targets 
         where target_name = 'dwhserp00:3872';
sqlplus> exit;
#>

#> $AGENT_HOME/bin/emctl start agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ............... started.

#> $AGENT_HOME/bin/emctl status agent


Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.  
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.3.0
OMS Version       : 10.2.0.3.0
Protocol Version  : 10.2.0.2.0
Agent Home        : /oracle/product/10.2.0/agent/agent10g/dwhnserp0
Agent binaries    : /oracle/product/10.2.0/agent/agent10g
Agent Process ID  : 525810
Parent Process ID : 143586
Agent URL         : https://dwhnserp0:3872/emd/main
Repository URL    : https://oragrid:1159/em/upload
Started at        : 2008-04-11 16:28:27
Started by user   : oracle
Last Reload       : 2008-04-11 16:28:27
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far :     0.00
Number of XML files pending upload           :        6
Size of XML files pending upload(MB)         :     0.34
Available disk space on upload filesystem    :    41.66%
Last successful heartbeat to OMS             : 2008-04-11 16:28:46
---------------------------------------------------------------
Agent is Running and Ready

You can find the supported Timezone values in a file under sysman directory

cat $AGENT_HOME/sysman/admin/supportedtzs.lst | more
# Timezone regions with +02:00 standard offset from GMT
Africa/Cairo
Africa/Johannesburg
Africa/Khartoum
Africa/Tripoli
Africa/Windhoek
Asia/Beirut
Asia/Damascus
Asia/Gaza
Asia/Istanbul
Asia/Jerusalem
Asia/Nicosia
Asia/Tel_Aviv
EET
Egypt
Etc/GMT-2
Europe/Athens
Europe/Bucharest
Europe/Helsinki
Europe/Istanbul
Europe/Kaliningrad
Europe/Kiev
Europe/Minsk
Europe/Riga
Europe/Simferopol
Europe/Sofia
Europe/Tallinn
Europe/Vilnius
Israel
Libya
Turkey

# Timezone regions with +03:00 standard offset from GMT
Africa/Djibouti
Africa/Mogadishu
Africa/Nairobi
Asia/Aden
Asia/Amman
Asia/Baghdad
Asia/Bahrain
Asia/Kuwait

Scheduling a datapump job in Oracle 10g - Part2

2008-04-16T23:34:00.029+03:00

Previous part of this article was about exporting a table data by using dbms_datapump via plsql. You can obtain information about creating a procedure that exports a table or group of table to an operationg system file.

This part includes information about creating a Scheduled Job to run the export procedure periodically. As Scheduled Jobs come with Oracle 10g, scripts in this article will work on databases later than Oracle 10.1.x.x

The sys package we will mainly use is DBMS_SCHEDULER. First we should create a job. You can find the following command that i used to create a job. Alternate parameters are explained with comments.


BEGIN 
    DBMS_SCHEDULER.CREATE_JOB(
       job_name           =>  'log_to_table',
       job_type           =>  'STORED_PROCEDURE',   
                              
                              -- Possible values could be:
                              -- PLSQL_BLOCK, 
                              -- STORED_PROCEDURE, 
                              -- EXECUTABLE, 
                              -- CHAIN.
       job_action         =>  'D_EPEKER.p_test_01', -- procedure name to run
       start_date         =>  'sysdate', -- date_to_start_execution date
       repeat_interval    =>  'FREQ=HOURLY', -- every other day
                              
                              -- Possible values could be:
                              -- "FREQ = YEARLY" | "MONTHLY" | "WEEKLY" | 
                              -- "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" 
                              -- INTERVAL = 1 through 99 
         
       end_date           =>  'sysdate+1',     -- date_to_end_execution
       job_class          =>  'logging_class',  
       comments           =>  'job_logs_something');-- explanation of the job
END;

/*
Jobs are created disabled by default and need to be enabled to run. You can find the
following procedure to alter a Scheduled Jobs attribute. By default a scheduled job
drops itself after its execution ends. You should set "auto_drop" attribute to 
FALSE to hold the Scheduled Job after its execution ends.
*/
-- 
-- enabling a job 
-- 
BEGIN
  SYS.DBMS_SCHEDULER.ENABLE 
    (name => 'D_EPEKER.LOG_TO_TABLE');
END;

-- 
-- altering a job attribute
-- 
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => 'log_to_table',  -- name of the job
        attribute => 'auto_drop',  -- attribute name
        value => FALSE  -- new value of the attribute
        );
END;

/*
As you can schedule a job, you can also run any Scheduled Job before its Scheduled
time comes. To run or stop a job immediately you can use the following command.
*/
-- 
-- executing a job immediately 
-- 
exec DBMS_SCHEDULER.RUN_JOB('log_to_table',FALSE);
exec DBMS_SCHEDULER.STOP_JOB('log_to_table',TRUE);

/*
The most likely property of this newly "Scheduled Job" is that they can be easily
maintained and monitored by system views. You can find the related view in the
following.
*/
-- schedued jobs whichs owner is D_EPEKER
select * from dba_scheduler_jobs where owner='D_EPEKER';

-- you can also user user_scheduled_jobs view to find out the jobs and their properties
select * from user_scheduler_jobs;

-- run details of the scheduled jobs
select * from DBA_SCHEDULER_JOB_RUN_DETAILS 
  where lower(job_name) = 'log_to_file_external;

/*
There is a lot of way to determine the repeat interval. There is lots of examples 
in the following and you can also find the explanations of the frequencies.
*/
-- 
-- setting repeat_interval 
-- 
--Run every Friday
FREQ=DAILY; BYDAY=FRI;
FREQ=WEEKLY; BYDAY=FRI;
FREQ=YEARLY; BYDAY=FRI;

--Run every other Friday
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

--Run on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-1;

--Run on the next to last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-2;

--Run every 10 days
FREQ=DAILY; INTERVAL=10;

--Run daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;

--Run on the 15th day of every other month
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

--Run on the 29th day of every month
FREQ=MONTHLY; BYMONTHDAY=29;

--Run on the second Wednesday of each month
FREQ=MONTHLY; BYDAY=2WED;

--Run on the last Friday of the year.
FREQ=YEARLY; BYDAY=-1FRI;

--Run every 50 hours.
FREQ=HOURLY; INTERVAL=50;

--Run on the last day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;

--Run hourly for the first three days of every month.
FREQ=HOURLY; BYMONTHDAY=1,2,3;

--Run on the last workday of every month, excluding company holidays 
--(This example references an existing named schedule called Company_Holidays.)
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Company_Holidays; BYSETPOS=-1

--Run at noon every Friday and on company holidays.
FREQ=YEARLY;BYDAY=FRI;BYHOUR=12;INCLUDE=Company_Holidays

Scheduling a datapump job in Oracle 10g - Part1

2008-02-18T00:00:00.039+02:00

Subject: Periodically exporting a table data using dbms_datapump and scheduled with dbms_scheduler. We will first create a procedure that exports the table data and then set a scheduled job calls the procedure.

First of all we need an Oracle Directory to export the data. As dbms_datapump runs database side, dbms_datapump package uses database directories and objects to export objects.


create or replace directory expdp_dir as '/oradata/exports/';
grant read,write to public on directory expdp_dir;

One should also have EXP_FULL_DATABASE and IMP_FULL_DATABASE to export and import any other schema object except his own schema.


grant EXP_FULL_DATABASE to ;

You can find the rest of the explanations as comments in the following procedure.


DECLARE 

  -- handle number comes with DBMS_DATAPUMP.OPEN procedure
  n_dp_handle number;

BEGIN
  
  /*
  First thing to do is to open a datapump session.
  
  operation: EXPORT | IMPORT | SQL_FILE (DBMS_DATAPUMP.ADD_FILE should be run before)
  export modes: FULL | SCHEMA | TABLE | TABLESPACE | TRANSPORTABLE
  version: COMPATIBLE (default) | LATEST | 10.0.0
  */
  
  n_dp_handle := DBMS_DATAPUMP.open(
    operation=>'EXPORT',
    job_mode=>'TABLE',
    remote_link=>NULL,
    job_name=>'LOGVRM_TMP_EXPORT',
    version=>'COMPATIBLE'); 
  
  /*
  add the export file to opened datapump session
  you must use '%U' in the filename, incase of parallel export
  */
  
  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => s_file || '.dmp', --dump file name
    directory => 'EP_EXPORT'); --oracle directory 
  
  /*
  now we start filtering the objects we want to export
  name: INCLUDE_NAME_EXPR | EXCLUDE_NAME_EXPR | SCHEMA_EXPR | TABLESPACE_EXPR
        INCLUDE_PATH_EXPR | EXCLUDE_PATH_EXPR

  in the following example we filter schema, object and partition objects 
  respectively
  */
  
  DBMS_DATAPUMP.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => 'IN (''VERIM'')');
        
  DBMS_DATAPUMP.metadata_filter(
    handle => l_dp_handle,
    name   => 'NAME_EXPR',
    value  => 'IN (''BIR_GERI_ODEME_LOG_VRM'', 
                   ''BIR_TEKLIF_LOG_VRM'', 
                   ''M_D_HESAP_LOG_VRM'', 
                   ''T_GNK_TESIS_LOG_VRM'')',
    object_type => 'TABLE');
                
  DBMS_DATAPUMP.data_filter(
    handle => l_dp_handle,
    name   => 'PARTITION_EXPR',
    value  => 'IN (''PAR_TEMP'')');   
                 
  /*
  you can set parallelism in datapump jobs
  every thread of parallel job writes to its own part of file
  so dont forget to use %U parameter in the filename
  */
  
  DBMS_DATAPUMP.SET_PARALLEL(
    handle => l_dp_handle,
    degree => 1); 
  
  /*
  now we can start the job
  */  
  
  DBMS_DATAPUMP.start_job(l_dp_handle); 
  
  /*
  perodically check the expdp job for the status  
  s_le_log is the final varchar2 variable that holds the last status of the job
  you can check the 'error' string in the s_le_log variable to determine 
  if the end status has any errors
  */
  
  job_state := 'UNDEFINED';
  WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') 
  LOOP
    dbms_datapump.get_status(l_dp_handle, 
                             dbms_datapump.ku$_status_job_error +
                             dbms_datapump.ku$_status_job_status +
                             dbms_datapump.ku$_status_wip, -1, job_state, sts);
           
    IF (BITAND(sts.mask,dbms_datapump.ku$_status_wip) != 0) 
    THEN
      le := sts.wip; --work is still in progress 
    ELSE
      IF (BITAND(sts.mask,dbms_datapump.ku$_status_job_error) != 0) 
      THEN
        le := sts.error; --work get an error 
      ELSE
        le := NULL;
      END IF;
    END IF;
  END LOOP;        
 
                         
  /*
  check the log entry of the job status         
  */
  
  IF le IS NOT NULL 
  THEN 
    le_ind := le.first;
    s_le_log := ''; 
    WHILE le_ind IS NOT NULL LOOP
      s_le_log := s_le_log || ' ' || le(le_ind).LogText;
      le_ind := le.next(le_ind);
    END LOOP;
  END IF;  
  
  /*
  if job hangs drop the master table of the job 
  every datapump job creates table for itself for logging and monitoring issues
  if the job end the table should be dropped automatically, 
  if not drop the table manual else oracle doesnt allow to run the same job again
  */
  
  select 
    count(*) into n_ctl2 
  from 
    dba_datapump_jobs 
  where 
    job_name = 'LOGVRM_TMP_EXPORT' and
    owner_name='MARDATA';  

  IF n_ctl2 > 0 THEN
    execute immediate ('drop table MARDATA.LOGVRM_TMP_EXPORT');
  END IF;
  
  /*
  Check the s_le_log for any error messages
  You can also insert the log with a date column 
  to know what happened during the export  
  */
  
  IF (INSTR(LOWER(s_le_log),'error',1) > 0 OR 
      INSTR(LOWER(s_le_log),'hata',1) > 0)  
  THEN
    NULL;
  END IF;
  
  
  /*       
  log the export log to know what happened during export
  */
  
  UPDATE DBA_LOGVRM_EXPORT_LOG SET 
    ENDDATE=SYSDATE, 
    MSG=substr(s_le_log,1,1000) 
    WHERE FILENAME=s_full_file; --dump file name with full path
  COMMIT;

  
  /*
  Dont forget to detach from the job
  */
  
  DBMS_DATAPUMP.DETACH(l_dp_number);

END;

Second Part of this article will cover dbms_scheduler to schedule this procedure to perodically backup any database object with datapump.

When the Grid Agent couldnt find any target on host...

2008-01-29T22:27:00.000+02:00

Subject: Manually adding targets to EM Grid Agent installed on a host.

Problem: As the installation procedure finishes successfully on a target host, EM agent examines "/etc/oratab" file (on UNIX operating system) to find the installed oracle products on the host. What if the specified file doesnt exist or somehow agent couldnt read the file?

Solution: Before the installation, you should check "/etc/oratab" file if it exists. If not, check the "/var/oracle" directory for the oratab file and make symbolic link named oratab under "/etc" directory.

If the agent cannot find any target on the host in all circumstances you can try adding the targets manually after installation. You will find configuration file, template entry for a "database" and a "listener".

EM Agent stores target information in targets.xml file located at "$AGENT_HOME/sysman/emd" directory. A sample file without any targets can be find as follows.



<Targets AGENT_TOKEN="e7244b84afad62c94aad53fe407af2514e3b7f04">
        <Target TYPE="oracle_emd" NAME="oragrid:3872"/>
        <Target TYPE="host" NAME="oragrid"/>
</Targets>

You can add a listener as a target to targets.xml file by using the following template. (values between the three stars - *** *** - are system specific. You should use your own systems parameters instead of *** *** values)



<Target TYPE="oracle_listener" NAME="***LISTENER_NAME***">
        <Property NAME="ListenerOraDir" VALUE="$ORACLE_HOME/network/admin"/>
        <Property NAME="LsnrName" VALUE="***LISTENER_NAME***"/>
        <Property NAME="Machine" VALUE="***HOSTNAME***"/>
        <Property NAME="OracleHome" VALUE="***$ORACLE_HOME***"/>
        <Property NAME="Port" VALUE="***LISTENER_PORT***"/>
</Target>

The following template is for manually entering database target to a EM Agent. (values between the three stars - *** *** - are system specific. You should use your own systems parameters instead of *** ... *** values)



<Target TYPE="oracle_database" NAME="***DB_NAME***"/>
        <Property NAME="OracleHome" VALUE="***$ORACLE_HOME***"/>
        <Property NAME="UserName" VALUE="dbsnmp"/>
        <Property NAME="MachineName" VALUE="***HOSTNAME***"/>
        <Property NAME="Port" VALUE="1521"/>
        <Property NAME="SID" VALUE="***$ORACLE_SID***"/>
        <Property NAME="ServiceName" VALUE="***$SERVICE_NAME***"/>
        <Property NAME="password" VALUE="***PASSWORD***" ENCRYPTED="FALSE"/>
        <Property NAME="Role" VALUE="NORMAL"/>
</Target>

After you are done with your "targets.xml" file you should run emctl reload agent to read the configuration files from scratch. You can check the defined targets by running emctl config agent listtargets

Installing Grid Agent to a target host

2008-01-13T00:44:00.000+02:00

Subject: Installing Oracle Enterprise Manager Grid Control 10.2.0.3 agent on a target host.

I will examine the installation process in three main part. Preinstallation part, is a group of work should be done before executing runInstaller installation script. There is some work to to on OMS Server and on the TARGET side. The second part includes some screenshots and it is actually the time after you execute runInstaller script. Tha last part is the postinstallation part stands for some settings.

PREINSTALLATION

OMS

First you should enter a host record for the target host. IP and HOSTNAME record should be entered to /etc/hosts operating system file. One thing to remember, IP number should be the one listener executes on. Ping should answer after the configuration.

[oracle@oragrid ~]$ ping fmsserp1
ping: unknown host fmsserp1
[oracle@oragrid ~]$ su -
Password:
[root@oragrid ~]# cat /etc/hosts | grep fmsserp1
[root@oragrid ~]# vi /etc/hosts
[root@oragrid ~]# ping fmsserp1
PING fmsserp1 (10.4.9.83) 56(84) bytes of data.
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=0 ttl=254 time=0.219 ms
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=1 ttl=254 time=0.197 ms
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=2 ttl=254 time=0.198 ms
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=3 ttl=254 time=0.193 ms
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=4 ttl=254 time=0.197 ms

--- fmsserp1 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4000ms
rtt min/avg/max/mdev = 0.193/0.200/0.219/0.020 ms, pipe 2
[root@oragrid ~]# cat /etc/hosts | grep fmsserp1
10.4.9.83 fmsserp1
[root@oragrid ~]#

AGENT

Agent should also know the IP HOSTNAME pair of the OMS Server. You can also find the listening IP address to enter the OMS Server host file.

$AGENT_HOME should be exported. It is not a must but nice to have especially while searching the setup directory of the agent.

Be sure /etc/oratab file exists. On some operating systems the file exists on /var/oracle directory, if so you should create a soft link /etc/oratab for /var/oracle/oratab because agent will find the targets installed on the host by looking this file.

You can now go the installer directory to run the runInstaller. Be sure you have enough space in /tmp directory. If not you can delete older oracle installer logs.

$ hostname
fmsserp1
$ netstat -ni
Name Mtu Network Address Ipkts Ierrs Opkts Oerrs Coll
lan0:1 1500 10.4.9.0 10.4.9.83 31833330 0 2849543 0 0
lan2* 1500 none none 0 0 0 0 0
lan1 1500 192.168.0.0 192.168.0.2 6331944 0 11558318 0 0
lan0 1500 10.4.9.0 10.4.9.82 2445705484 0 1271175018 0 0
lo0 4136 127.0.0.0 127.0.0.1 206103084 0 206104962 0 0
$ ping oragrid
sh: ping: not found.
$ su -
Password:
fmsserp1:/#ping oragrid
ping: unknown host oragrid
fmsserp1:/#
fmsserp1:/#cat /etc/hosts | grep oragrid
fmsserp1:/#
fmsserp1:/#cat /etc/hosts | grep oragrid
10.4.46.70 oragrid
fmsserp1:/#tail -2 /etc/hosts
#EM Agent
10.4.46.70 oragrid
fmsserp1:/#
fmsserp1:/#id
uid=0(root) gid=3(sys) groups=0(root),1(other),2(bin),4(adm),5(daemon),6(mail),7(lp),20(users)
fmsserp1:/#exit
logout root
$ id
uid=106(oradet) gid=104(dba)
$ cd $ORACLE_HOME/network/admin
$ cat listener.ora
LISTENER_ARCHIVE =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.9.83)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.9.83)(PORT = 1526))
)

SID_LIST_LISTENER_ARCHIVE =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = AVEA93_C )
(ORACLE_HOME = /fmsarch_ora/product/9.2.0 )
(SID_NAME = AVEA93_C)
)
)

$
$ lsnrctl status LISTENER_ARCHIVE

LSNRCTL for HPUX: Version 9.2.0.6.0 - Production on 10-JAN-2008 11:42:22

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=10.4.9.83)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_ARCHIVE
Version TNSLSNR for HPUX: Version 9.2.0.6.0 - Production
Start Date 20-NOV-2007 06:19:08
Uptime 51 days 5 hr. 23 min. 14 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /fmsarch_ora/product/9.2.0/network/admin/listener.ora
Listener Log File /fmsarch_ora/product/9.2.0/network/log/listener_archive.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.4.9.83)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.4.9.83)(PORT=1526)))
Services Summary...
Service "AVEA93_C" has 1 instance(s).
Instance "AVEA93_C", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
$ echo $AGENT_HOME

$ cd
$ export AGENT_HOME=/fmsarch_ora/product/agent10g/agent10g
$ echo $AGENT_HOME
/fmsarch_ora/product/agent10g/agent10g
$ cat /etc/oratab
#

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/fmsarch_ora/product/9.2.0:N
$
$ cd oracle_setup
$ ls
HPI_Grid_Control_agent_download_10_2_0_3_0.zip hpi
agent_download.rsp.bak instructions.txt
agentdeployroot.sh
$ cd hpi
$ ls
addons agentDownload.hpi agentdeploy prereqs
agent agent_scripts.jar oui response
$ cd agent
$ ls
install runInstaller stage
$ DISPLAY=10.1.22.210:0.0
$ export DISPLAY
$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be B.11.23. Actual B.11.23
Passed

Checking swap space: must be greater than 250 MB. Actual 4096 MB Passed
Checking temp space: 257 MB available, 500 MB required. Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,at which time they will be rechecked.

Continue? (y/n) [n] n

User Selected: No
$ cd /tmp
$ ls -l | grep Ora
drwxrwxr-x 4 fmsarch fms 96 Jun 7 2006 OraInstall2006-06-07_01-03-59PM
drwxrwxr-x 4 oraarch fms 8192 Jun 7 2006 OraInstall2006-06-07_01-08-20PM
drwxrwxr-x 4 oraarch fms 8192 Jun 7 2006 OraInstall2006-06-07_01-27-41PM
drwxr-xr-x 4 oraarch fms 8192 Jun 7 2006 OraInstall2006-06-07_01-34-00PM
drwxr-xr-x 4 oraarch fms 8192 Jun 7 2006 OraInstall2006-06-07_01-50-37PM
drwxrwxr-x 4 fmsarch fms 8192 Jun 7 2006 OraInstall2006-06-07_11-42-22AM
drwxrwxr-x 4 fmsarch fms 8192 Jun 7 2006 OraInstall2006-06-07_11-44-54AM
drwxrwx--- 2 oraarch dba 96 Jan 10 09:49 OraInstall2008-01-10_09-49-20AM
$ ls -l | grep Ora | awk '{print $9}'
OraInstall2006-06-07_01-03-59PM
OraInstall2006-06-07_01-08-20PM
OraInstall2006-06-07_01-27-41PM
OraInstall2006-06-07_01-34-00PM
OraInstall2006-06-07_01-50-37PM
OraInstall2006-06-07_11-42-22AM
OraInstall2006-06-07_11-44-54AM
OraInstall2008-01-10_09-49-20AM
$ ls -l | grep Ora | awk '{print $9}' | xargs rm –r

RUNINSTALLER

This is the easiest part of the installation once you pass the "system check" screen. You should only know the IP address of the OMS Server and registration password, the one you obtain while installing the Grid Server.

After the root.sh script runs successfully there is only a few things to do.

$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be B.11.23. Actual B.11.23
Passed

Checking swap space: must be greater than 250 MB. Actual 4096 MB Passed
Checking Temp space: must be greater than 500 MB. Actual 640 MB Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2008-01-10_09-52-44AM. Please wait ...

fmsserp1:/fmsarch_ora/product/agent10g/agent10g #
$ id
uid=109(oraarch) gid=104(dba) groups=105(fms)
$ su - root
Password:
fmsserp1:/#
fmsserp1:/#cd /fmsarch_ora/product/agent10g/agent10g
fmsserp1:/fmsarch_ora/product/agent10g/agent10g#./root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oraarch
ORACLE_HOME= /fmsarch_ora/product/agent10g/agent10g

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

POSTINSTALLATION

Agent software uses dbsnmp user to obtain information from the database instances. So you should unlock the user if it is locked. Here I also change the password for security.

After unlocking and chaning the password of the dbsnmp user you should also inform the agent software about the changes. Rest of the subject is as follows :)

fmsserp1:/fmsarch_ora/product/agent10g/agent10g#exit
logout root
$ id
uid=109(oraarch) gid=104(dba) groups=105(fms)
$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Jan 10 10:17:18 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production

SQL> select name from v$database;

NAME
---------
AVEA93_C

SQL>
SQL> alter user dbsnmp account unlock;

User altered.

SQL> alter user dbsnmp identified by ****;

User altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

$ ./emctl config agent listtargets
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
[fmsserp1:3872, oracle_emd]
[fmsserp1, host]
[LISTENER_ARCHIVE, oracle_listener]
[AVEA93_C, oracle_database]
$ ./emctl config agent credentials AVEA93_C:oracle_database
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Name = AVEA93_C, Type = oracle_database
Want to change for "UserName" (y/n):n
Want to change for "password" (y/n):y
Enter the new value for "password" :*******************
Re-enter the new value for "password" :*******************
EMD reload completed successfully
$ ./emctl reload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD reload completed successfully
$ ./emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

When the works done successfully everything should be fine. Now is the time to wait a little while for Grid Server (OMS) to discovery newly installed target.

Mining Archived Logs with DBMS_LOGMNR_D

2008-01-11T22:35:00.002+02:00

Oracle Database Logminer utility helps you to read your archived logs for what is running in your database at a specific time period.

-- following script comes with a fresh installation of oracle database. You can find it under your ORACLE_HOME/rdbms/admin directory. Script simply creates DBMS_LOGMNR_D package under SYS schema.
--@?/rdbms/admin/dbmslogmnrd.sql;

-- Contents of the mined archived logs can be examined via the following view.
SELECT * FROM V$LOGMNR_CONTENTS;

-- To determine which redo logs are being analyzed in the current Logminer session
SELECT * FROM V$LOGMNR_LOGS;

-- Shows information about the Logminer DICTIONARY FILE, provided the DICTIONARY was created USING the STORE_IN_FLAT_FILE OPTION.
-- The information shown includes the DATABASE name AND status information.
SELECT * FROM V$LOGMNR_DICTIONARY;

SELECT * FROM V$LOGMNR_PARAMETERS

/*
The TYPE OF CHANGE made TO the DATABASE (INSERT, UPDATE, DELETE, OR DDL).
The SCN AT which a CHANGE was made (SCN COLUMN).
The SCN AT which a CHANGE was committed (COMMIT_SCN COLUMN).
The TRANSACTION TO which a CHANGE belongs (XIDUSN, XIDSLT, AND XIDSQN COLUMNS).
The TABLE AND SCHEMA name OF the modified OBJECT (SEG_NAME AND SEG_OWNER COLUMNS).
The name OF the USER who issued the DDL OR DML STATEMENT TO make the CHANGE (USERNAME COLUMN).

Reconstructed SQL statements showing SQL that IS equivalent (but NOT necessarily identical) TO the SQL used TO generate the redo records (SQL_REDO COLUMN). IF a PASSWORD IS part OF the STATEMENT IN a SQL_REDO COLUMN, the PASSWORD IS encrypted.

Reconstructed SQL statements showing the SQL statements needed TO undo the CHANGE (SQL_UNDO COLUMN). SQL_UNDO COLUMNS that
correspond TO DDL statements are always NULL. Similarly, the SQL_UNDO COLUMN may be NULL FOR SOME datatypes AND FOR rolled back operations

You will need to add supplemental logging to mine your logs. By this way oracle database stores updated data by updated row data instead of ROWID basis. Logical update information is more meaningful rather than obtaining ROWID basis update statements. If a primary key or a unique index exists on the table, mined data will have these values as keys. In absence of these keys values will have all the row to identifiy updated data.

Another key factor is; if you mine your archived logs in a different database instead of mining in the production database ROWID information will worth nothing. In this kind of situation one needs to know the logical statements to mine meaningfully.
*/

----------------------------------------
-- adding supplemental logging and types
----------------------------------------
-- minimal supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Identification key logging enables database-wide before-image logging of primary keys or unique indexes (in the absence of primary keys)
-- for all updates. With this type of logging, an application can identify updated rows logically rather than resorting to ROWIDs
-- i.e. logical standby
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

-- table supplemental logging
-- These columns will be logged every time an UPDATE statement
-- is executed on scott.emp, regardless of whether or not the update affected them
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP emp_parttime (empno, ename, deptno) ALWAYS;

-- before images of the columns will be logged only if at least one of the columns is updated.
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP emp_parttime (empno, ename, deptno);
ALTER TABLE scott.emp DROP SUPPLEMENTAL LOG GROUP emp_parttime;

-- to create all logminer objects in to the specified tablespace
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');

------------------------
-- to extract dictionary
------------------------
-- extracting to a flat file
-- You could also specify a filename and location without specifying the STORE_IN_FLAT_FILE option. The result would be the same.
-- alter system set UTL_FILE_DIR=; should be executed first
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

-- to extract dictionary to online redologs
-- database must be in archive mode and archiving should be enabled
EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

-- dont need to extract dictionary
-- with this command logminer uses online dictionary which is in the schema sys
-- The online catalog option is not valid with the DDL_DICT_TRACKING option.
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

-- tracking ddl statements
-- applies ddl commands in logminer internal dictionary
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING);

-- With this option set, LogMiner applies any DDL statements seen in the redo logs to its internal dictionary.
-- For example, to see all the DDLs executed by user SYS, you could issue the following query
SELECT USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE USERNAME = 'SYS' AND OEPRATION = 'DDL';

-----------------------------
-- filtering redo_log actions
-----------------------------
-- If long-running transactions are present in the redo logs being analyzed,
-- use of this option may cause an "Out of Memory" error.
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.SKIP_CORRUPTION);
EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME => '/oracle/dictionary.ora',
STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));

EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME => '/oracle/dictionary.ora',
STARTSCN => 100,
ENDSCN => 150);

-----------------------------
-- querying v$logmnr_contents
-----------------------------
SELECT OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SCOTT' AND SEG_NAME = 'ORDERS' AND
OPERATION = 'DELETE' AND USERNAME = 'RON';

-- MINE_VALUE example
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
WHERE
SEG_NAME = 'emp' AND
SEG_OWNER = 'SCOTT' AND
OPERATION = 'UPDATE' AND
DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'SCOTT.EMP.SAL') >
2*DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'SCOTT.EMP.SAL');

-- COLUMN_PRESENT example
SELECT
(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
(DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'SCOTT.EMP.SAL') -
DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'SCOTT.EMP.SAL')) AS INCR_SAL
FROM V$LOGMNR_CONTENTS
WHERE
DBMS_LOGMNR.COLUMN_PRESENT(REDO_VALUE, 'SCOTT.EMP.SAL') = 1 AND
DBMS_LOGMNR.COLUMN_PRESENT(UNDO_VALUE, 'SCOTT.EMP.SAL') = 1 AND
OPERATION = 'UPDATE';

--------------------
-- a session example
--------------------
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log1orc1.ora', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log2orc1.ora', OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'/oracle/database/dictionary.ora');
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => 'orcldict.ora',
STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));

/*
--START_LOGMNR options
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
-- If you specify DICT_FROM_REDO_LOGS, LogMiner expects to find a dictionary in the redo logs
-- that you specified with the DBMS_LOGMNR.ADD_LOGFILE procedure before starting logmnr
DBMS_LOGMNR.DICT_FROM_REDO_LOGS
DBMS_LOGMNR.COMMITTED_DATA_ONLY
DBMS_LOGMNR.SKIP_CORRUPTION
DBMS_LOGMNR.DDL_DICT_TRACKING
-- DBMS_LOGMNR.ADD_LOGFILE options
DBMS_LOGMNR.NEW, DBMS_LOGMNR.ADDFILE, AND DBMS_LOGMNR.REMOVEFILE -- See Specify Redo Logs for Analysis
DBMS_LOGMNR.NO_SQL_DELIMITER
DBMS_LOGMNR.PRINT_PRETTY_SQL
DBMS_LOGMNR.CONTINUOUS_MINE
*/

SELECT * FROM v$logmnr_contents;

-- table hit statistics
SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits FROM
V$LOGMNR_CONTENTS WHERE SEG_NAME NOT LIKE '%$' GROUP BY
SEG_OWNER, SEG_NAME;

EXECUTE DBMS_LOGMNR.END_LOGMNR;

Endless Status Pending in OMS Console

2008-01-05T21:17:00.000+02:00

Problem: When I logged in the OMS console, i realized that one of the cluster instances has a problem with one of its nodes. Node_1 seems to be up but when i examine Node_2 the status seems like "Status Pending" in the OMS console of the Node_2.

When i check the agent on the target host, there was not any problem and the agent uploads metrics successfully.

[oracle@be02 bin]$ ./emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.3.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.2.0
Agent Home : /oracle/product/10.2.0/agent10g/ACTV_ACTV2
Agent binaries : /oracle/product/10.2.0/agent10g
Agent Process ID : 28158
Parent Process ID : 28049
Agent URL : https://be02:3872/emd/main
Repository URL : https://oragrid:1159/em/upload
Started at : 2008-01-04 10:47:17
Started by user : oracle
Last Reload : 2008-01-04 10:47:17
Last successful upload : 2008-01-04 10:49:05
Total Megabytes of XML files uploaded so far : 22.89
Number of XML files pending upload : 741
Size of XML files pending upload(MB) : 35.85
Available disk space on upload filesystem : 70.56%
Collection Status : Disabled by Upload Manager
Last successful heartbeat to OMS : 2008-01-04 10:48:30
---------------------------------------------------------------
Agent is Running and Ready

While i was scratching the OMS console to find a clue about the problem i realized that the target host has been restarted for a week ago and the last upload made by target agent was further then the current date . OMS console shows the last upload date four days future. How could it be? I decided to check the date and TZ environment variable for both target and OMS hosts.

-- target op sys date
[oracle@be02 bin]$ date
Fri Jan 4 14:53:38 EET 2008

-- OMS op sys date
[oracle@oragrid ~]$ date
Fri Jan 4 14:55:40 EET 2008

-- TZ Parameter of target
[oracle@be02 bin]$ echo $TZ

[oracle@be02 bin]$

It seems there is not any problem about the date settings of the host operationg systems. Let's have a look at the agent and OMS timezone regions.

-- Agents Timezone value
[oracle@be02 bin]$ ./emctl config agent getTZ
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Turkey

--emd.properties Timezone parameter value
[oracle@be02 config]$ tail -10 emd.properties
# at startup and reload. Currently this applies only to "Critical" marked dynamic
# properties. The following two values are applicable per target and not per
# dynamic property
#
# dynamicPropReComputeInterval --> time difference between a failed dynamic property
# computation and the next try to compute the property in seconds. The default value is 120 seconds.
#
# dynamicPropReComputeMaxTries --> maximum number of reties for calculating failed
# dynamic properties. The default value is 4 retires.
agentTZRegion=Turkey

Timezone values are also the same. There should not be any problem. Then what is the "Status Pending" state and the last upload date of the target in OMS. I decided to reset timezone settings of the agent and the oms.

-- Changing agents timezone
[oracle@be02 bin]$ ./emctl resetTZ agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Agent is running. Stop the agent and rerun the command.

[oracle@be02 bin]$ ./emctl stop agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

[oracle@be02 bin]$ ./emctl resetTZ agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Updating /oracle/product/agent10g/agent10g/sysman/config/emd.properties...
Successfully updated /oracle/product/agent10g/agent10g/sysman/config/emd.properties.

Login as the em repository user and run the script:
exec mgmt_target.set_agent_tzrgn('be02:3872','Turkey')
This can be done for example by logging into sqlplus and doing
SQL> exec mgmt_target.set_agent_tzrgn('be02:3872','Turkey')

[oracle@be02 bin]$ ./emctl start agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ..... started.

[oracle@be02 bin]$ ./emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

-- Executing OMS Script
[oracle@oragrid ~]$ sqlplus sysman/

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jan 4 14:19:19 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exec mgmt_target.set_agent_tzrgn('be02:3872','Turkey');

PL/SQL procedure successfully completed.

Lets check if everything is OK. When i check the OMS console for the status of the Node_2 of my cluster instance it seems to be recovered from the "Status Pending". Node_2 seems to be up in the cluster instances home page also. Everything went fine but one thing to notice. When i click to Node_2 home page in the OMS console weird thing i noticed. Latest Data Collected changed further than before. It now shows 11 days future for the last upload date. Everything goes fine but i still could not find an answer for this strange issue. Maybe somehow target hosts operating system date changed temporarily by unix administrators or by developers for accidentally or for test purposes and then reset to the current date.

You can check the following Oracle document for details of the commands used here:
Oracle® Enterprise Manager Advanced Configuration
10g Release 2 (10.2)
Part Number B16242-02
Chapter 10 - Reconfiguring the Management Agent and Management Service

Metric collection Error in targets status page at OMS Console

2007-12-29T23:33:00.000+02:00

Problem: Metric Collection Error in Oracle EM Grid Control Server (OMS) console

Possible problem in dbsnmp password on target host.

Solution: Oracle Enterprise Manager Agent uses dbsnmp account to gather database wide information. If the user locked or its password other than something non default, you should unlock the user and configure agent with the new password. Check out the following steps.

SQL> alter user dbnsmp account unlock;
SQL> alter user dbnsmp identified by ;

[oracle@raquality00 bin]$ ./emctl config agent listtargets
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
[raquality00:3872, oracle_emd]
[raquality00, host]
[raqcrs, cluster]
[RADB_RADB1, oracle_database]
[RADB, rac_database]
[LISTENER_RAQUALITY00_raquality00, oracle_listener]

$PROMPT> ./emctl config agent credentials RADB_RADB1:oracle_database
Oracle Enterprise Manager 10g Release 10.1.0.2.0
Copyright (c) 2002, 2003 Oracle Corporation. All rights reserved.
Name = emrep10.us.oracle.com, Type = oracle_database

Want to change for "UserName" (y/n):n
Want to change for "password" (y/n):y
Enter the value for "password" :*******

EMD reload completed successfully

Oracle EM Grid Control – Notification Rules

2007-12-29T22:25:00.001+02:00

Notification Rules are the way of Enterprise Manager warns you. You may want to be warned about “tablespace space usage percentage”, “data block corruption alert log entries” or anything else that you think it is critical for you. You can create a rule from scratch or you can also use Public Rules. Public Rules are also customizable for your needs. Here, I will create a rule from scratch for an example. Extenting and customizing a Public Rule has no differences in logical manner.

figure_1

- In “Preferences” Page, click to “My Rules” under “Notification” Tab. “My Notification Rules” page will appear.
- Click “Create” button to create a rule (figure_1)

figure_2

- There are six tabs on the screen. First one is “General”, here you should enter the “Name” of the rule, and the “Target Type” you want to apply the rule (in this example the Target Type is Database Instance).

- You can also identify which targets you want to apply the rule you created. To do this you should select one of the two choices. You can apply the rule to all of the database instances or you can apply the rule to only one instance (figure_2)

figure_3

- If you select to apply the rule to a specific database instance click the second radio button. A new list appears. This is the list of the database instances that your rule will apply.

- Click “Add” Button to add database instances (figure_3)

figure_4

- Target selector appears when you click to the “Add” button. Here you can select which target(s) you want to apply your rule. Select “Target Type” and Targets you want to apply the rule and click “Select” button to make your choice permanent.

- After you complete with this tab click to the next tab “Availability” (figure_4)

figure_5

- You can set the availability of the agent states and you can be notified by the state changes. You can set “agent unreachable” , “metric error” and “blackout” states and their cleared problems to notify you (figure_5)

figure_6

- “Metrics” tab is probably the most handy tab here. By this tab you can add more than a hundred metric to watch an instance. We will add “Generic Alert Log Error” metric in this example. By using this metric, one would never need to write a script to search alert.log for errors and send email instead this metric does the same thing four you.
- Click “Add” button to add a metric (figure_6)

figure_7

- You can find the metrics by clicking “next” link for every 10 records until you find the “Generic Alert Log Error”
- After selecting the specified metric go to the bottom of the page (figure_7)

figure_8

- You will find “Severity States” section here. Select any state you want to be within this metric. In this example I will select both “Critical” and “Warning” levels.
- You may want to be informed when the problem is cleared. Look “Corrective Action States” section to select which corrective actions should inform you.
- After your selections are complete Click “Continue” Button (figure_8)

figure_9

- You will see the added Metric in the “Metrics” Tab.
- Click “OK” button to complete the rule creation.

figure_10

- If the rule is created successfully you should see a rule in the “My Rules” Tab.
- If you want to be informed with this rule select the “Subscribe (Send Email)” column and click “Apply” button.

Oracle EM Grid Control – Changing User Password

2007-12-29T20:39:00.003+02:00

How to change your own user password and preferences in Oracle Enterprise Manager Grid Control (OMS) Console.

Figure_1

- To change your password, one should use “Preferences” Link above the tabs as shown in the figure_1.

Figure_2

- To change your password, you should first enter your “current password” to the related field. And then you can enter your “New Password” twice to the related fields. After you finished filling the boxes simply click to “Apply” button (figure_2)

- You can also change your contact information by using “E-Mail Addresses” section in the page. You can add new email address by clicking “Add Another Row” button. New row will appear instantly under your existing email address. You should write your new email address and click apply button as shown in the figure (figure_2)

- To remove an email address, select the address you want to delete and simply click “Remove” button. Selected email address will disappear immediately. You must click “Apply” button to make the changes permanent (figure_2)

- You can also test your contact email address by selecting your email address and click on the “Test” button. A test email will be sent to your email address (figure_2)

figure_3

- “Information Page” after your password successfully changes (figure_3)

figure_4

- “Information Page” after you test your email address (figure_4)