Dev's Weblog

Archive for the ‘Oracle DB’ Category.

Oracle – Restrict User login from particular IP Address

January 31, 2012, 3:19 pm

Problem :

I was asked to load new data into the database and I didn’t want users/applications to connect when I am loading the data.

Solution :

I had the following solutions in my mind.

Solution 1:Only allow login from the particular host.

Open the SQLNET.ORA from $ORACLE_HOME/network/admin and add following parameters.

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost, 10.103.119.182)

Reload the listener.

$cd $ORACLE_HOME/bin

$./lsnrctl reload

Now Oracle will only allow the connections from the machine with 10.103.119.182 IP Address.It will not even allow the sys or system user so make sure you have the SSH access to the database machine because if something goes wrong than you can SSH into the box and change the sqlnet.ora accordingly.

Advantages:

No database configuration needs to be changed.
Easy to toggle the setting.

Disadvantages :

It applies to all the users including SYS and SYSTEM.
SQLNET.ORA changes the system wide setting so it will be applied to all the database which are registered with the listener.

Solution 2 : Allow particular host using the Database triggers.

Write a Logon Database trigger which will allow the users/applications from the particular hosts.

CREATE OR REPLACE TRIGGER restricted_logon
AFTER LOGON ON DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in ('10.103.119.183', '10.103.119.184') THEN
RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
END IF;
END;
/

The user will get the following error if his host is not allowed.

ORA-20003: You are not allowed to connect to the database

Please disable the trigger once the data loading is done.

alter trigger restricted_logon disable;

Advantages :

The trigger works at the database level so it wont affect the other databases.
Easy to disable/enable trigger.

Disadvantages :

The database trigger will have to created once and it may not be supported or allowed.
The triggers won’t be fired for the uses who has the DBA privileges/role.

Tags: Blocking Orace user, Blocking through SQLNET.ORA, Blocking through triggers, IP Address Blocking
Category: Oracle, Oracle DB | Comment

Oracle 11G – Active Duplicate Database

December 14, 2011, 11:08 am

Problem :

I was asked to duplicate the production database (11.2.0.2) on the same machine which will be used as a test instance.

Solution:

With 11G there are many ways to duplicate the database.

Manual Duplicate – Where you need to take the backup of production system and move the backup pieces to the machine where want to duplicate the database.
Duplicate database from location – with 11GR2 you can use this option to duplicate the production database from its backup.The main advantage of this method is that while duplicating the database you don’t need to connect to production system at all.
Active Duplicate – With this method you need to have session with production and to be cloned database before you can perform the duplication.The advantage of this method is that you do not need to have production database’s backup beforehand.The duplication process with take the backup and will get rid of it once the duplication is done.

I opted for the Active duplication.Here are the steps which I performed to duplicate the database.

My environment.

Production DB	Clone DB
ORCL11202	PRIMDB

Step 1 : Create TNS Aliases

Create tns alias entries into $ORACLE_HOME/network/admin/tnsnames.ora for both Production and clone database.

ORCL11202 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = meteor)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl11202)
    )
  )

PRIMDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = meteor)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIMDB)
    )
  )

Step 2 : Create Static Registration for both databases in the listner.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = meteor)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
          (GLOBAL_DBNAME = ORCL11202)
          (ORACLE_HOME = /export/home/oracle/product/11.2.0.2/dbhome_1)
          (SID_NAME = ORCL11202)
    )
    (SID_DESC =
             (GLOBAL_DBNAME = PRIMDB)
             (ORACLE_HOME = /export/home/oracle/product/11.2.0.2/dbhome_1)
             (SID_NAME = PRIMDB)
   )
  )

Step 3 : Create password file and pfile for clone database.

Create password for the clone database.

$orapwd file=$ORACLE_HOME/dbs/orapwPRIMDB password=password entries=10

Create a pfile $ORACLE_HOME/dbs/initPRIMDB.ora with following parameters.

*.db_name='PRIMDB'
*.diagnostic_dest=/export/home/oracle

Step 4 : Start Clone database in the nomount mode.

$export ORACLE_SID=PRIMDB

$sqlplus / as sysdba

SQL>startup nomount;
ORACLE instance started.

Total System Global Area  217219072 bytes
Fixed Size                  2156896 bytes
Variable Size             159389344 bytes
Database Buffers           50331648 bytes
Redo Buffers                5341184 bytes

Step 5 : Create necessary directories for the clone database.

$mkdir /u02/database/PRIMDB

$mkdir /u02/fra/PRIMDB

Step 6 : Connect to Production instance and auxiliary instance using RMAN.

$rman target sys/password@orcl11202 auxiliary sys/password@PRIMDB

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Dec 14 10:31:25 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL1120 (DBID=3456964407)
connected to auxiliary database: PRIMDB (not mounted)

Step 7 : Start the Duplication process.

run {
allocate channel C1 type disk;
allocate auxiliary channel DUP type disk;
duplicate target database
to PRIMDB
from active database
spfile
SET MEMORY_TARGET="270M"
set db_unique_name='PRIMDB'
set db_file_name_convert='/u02/database/orcl11202','/u02/database/PRIMDB'
set log_file_name_convert='/u02/database/orcl11202','/u02/database/PRIMDB'
set control_files='/u02/database/PRIMDB/control01.ctl','/u02/database/PRIMDB/control02.ctl'
set log_archive_max_processes='5'
set log_archive_dest_1='LOCATION=/u02/fra VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB'
;
}

Step 8 : Monitor the duplication process.

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=143 device type=DISK

allocated channel: DUP
channel DUP: SID=96 device type=DISK

Starting Duplicate Db at 14-DEC-11

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/export/home/oracle/product/11.2.0.2/dbhome_1/dbs/spfileorcl11202.ora' auxiliary format
 '/export/home/oracle/product/11.2.0.2/dbhome_1/dbs/spfilePRIMDB.ora'   ;
   sql clone "alter system set spfile= ''/export/home/oracle/product/11.2.0.2/dbhome_1/dbs/spfilePRIMDB.ora''";
}
executing Memory Script

Starting backup at 14-DEC-11
Finished backup at 14-DEC-11

sql statement: alter system set spfile= ''/export/home/oracle/product/11.2.0.2/dbhome_1/dbs/spfilePRIMDB.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRIMDB'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PRIMDB'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  MEMORY_TARGET =
 270M comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PRIMDB'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u02/database/orcl11202'', ''/u02/database/PRIMDB'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u02/database/orcl11202'', ''/u02/database/PRIMDB'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u02/database/PRIMDB/control01.ctl'', ''/u02/database/PRIMDB/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 5 comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''LOCATION=/u02/fra VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIMDB'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''PRIMDB'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  MEMORY_TARGET =  270M comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''PRIMDB'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u02/database/orcl11202'', ''/u02/database/PRIMDB'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u02/database/orcl11202'', ''/u02/database/PRIMDB'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u02/database/PRIMDB/control01.ctl'', ''/u02/database/PRIMDB/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=/u02/fra VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     284008448 bytes

Fixed Size                     2157624 bytes
Variable Size                218108872 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5021696 bytes
allocated channel: DUP
channel DUP: SID=135 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL1120'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PRIMDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u02/database/PRIMDB/control01.ctl';
   restore clone controlfile to  '/u02/database/PRIMDB/control02.ctl' from
 '/u02/database/PRIMDB/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORCL1120'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''PRIMDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     284008448 bytes

Fixed Size                     2157624 bytes
Variable Size                222303176 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5021696 bytes
allocated channel: DUP
channel DUP: SID=134 device type=DISK

Starting backup at 14-DEC-11
channel C1: starting datafile copy
copying current control file
output file name=/u02/database/orcl11202/snapcf_orcl11202.f tag=TAG20111214T103545 RECID=15 STAMP=769862155
channel C1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-DEC-11

Starting restore at 14-DEC-11

channel DUP: copied control file copy
Finished restore at 14-DEC-11

database mounted

contents of Memory Script:
{
   sql clone 'alter database flashback off';
   set newname for datafile  1 to
 "/u02/database/PRIMDB/system01.dbf";
   set newname for datafile  2 to
 "/u02/database/PRIMDB/sysaux01.dbf";
   set newname for datafile  3 to
 "/u02/database/PRIMDB/undotbs01.dbf";
   set newname for datafile  4 to
 "/u02/database/PRIMDB/users01.dbf";
   set newname for datafile  5 to
 "/u02/database/PRIMDB/example01.dbf";
   set newname for datafile  6 to
 "/u02/database/PRIMDB/ccdata.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u02/database/PRIMDB/system01.dbf"   datafile
 2 auxiliary format
 "/u02/database/PRIMDB/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u02/database/PRIMDB/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u02/database/PRIMDB/users01.dbf"   datafile
 5 auxiliary format
 "/u02/database/PRIMDB/example01.dbf"   datafile
 6 auxiliary format
 "/u02/database/PRIMDB/ccdata.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

sql statement: alter database flashback off

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-DEC-11
channel C1: starting datafile copy
input datafile file number=00004 name=/u02/database/orcl11202/users01.dbf
output file name=/u02/database/PRIMDB/users01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:03:35
channel C1: starting datafile copy
input datafile file number=00003 name=/u02/database/orcl11202/undotbs01.dbf
output file name=/u02/database/PRIMDB/undotbs01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:01:45
channel C1: starting datafile copy
input datafile file number=00006 name=/u02/database/orcl11202/ccdata.dbf
output file name=/u02/database/PRIMDB/ccdata.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:01:15
channel C1: starting datafile copy
input datafile file number=00001 name=/u02/database/orcl11202/system01.dbf
output file name=/u02/database/PRIMDB/system01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:00:35
channel C1: starting datafile copy
input datafile file number=00002 name=/u02/database/orcl11202/sysaux01.dbf
output file name=/u02/database/PRIMDB/sysaux01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:00:25
channel C1: starting datafile copy
input datafile file number=00005 name=/u02/database/orcl11202/example01.dbf
output file name=/u02/database/PRIMDB/example01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-DEC-11

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u02/fra/ORCL11202/archivelog/2011_12_14/o1_mf_1_429_7gjzhnxp_.arc" auxiliary format
 "/u02/fra/1_429_769281339.arc"   ;
   catalog clone archivelog  "/u02/fra/1_429_769281339.arc";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 14-DEC-11
channel C1: starting archived log copy
input archived log thread=1 sequence=429 RECID=427 STAMP=769862645
output file name=/u02/fra/1_429_769281339.arc RECID=0 STAMP=0
channel C1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-DEC-11

cataloged archived log
archived log file name=/u02/fra/1_429_769281339.arc RECID=427 STAMP=769862647

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=769862648 file name=/u02/database/PRIMDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=769862648 file name=/u02/database/PRIMDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=769862648 file name=/u02/database/PRIMDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=769862648 file name=/u02/database/PRIMDB/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=769862648 file name=/u02/database/PRIMDB/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=769862648 file name=/u02/database/PRIMDB/ccdata.dbf

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

executing command: SET until clause

Starting recover at 14-DEC-11

starting media recovery

archived log for thread 1 with sequence 429 is already on disk as file /u02/fra/1_429_769281339.arc
archived log file name=/u02/fra/1_429_769281339.arc thread=1 sequence=429
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-DEC-11
Oracle instance started

Total System Global Area     284008448 bytes

Fixed Size                     2157624 bytes
Variable Size                243274696 bytes
Database Buffers              33554432 bytes
Redo Buffers                   5021696 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRIMDB'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIMDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     284008448 bytes

Fixed Size                     2157624 bytes
Variable Size                243274696 bytes
Database Buffers              33554432 bytes
Redo Buffers                   5021696 bytes
allocated channel: DUP
channel DUP: SID=133 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PRIMDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY     1597
 LOGFILE
  GROUP  1 ( '/u02/database/PRIMDB/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u02/database/PRIMDB/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u02/database/PRIMDB/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u02/database/PRIMDB/system01.dbf'
 CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u02/database/PRIMDB/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u02/database/PRIMDB/sysaux01.dbf",
 "/u02/database/PRIMDB/undotbs01.dbf",
 "/u02/database/PRIMDB/users01.dbf",
 "/u02/database/PRIMDB/example01.dbf",
 "/u02/database/PRIMDB/ccdata.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/database/PRIMDB/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/sysaux01.dbf RECID=1 STAMP=769862682
cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/undotbs01.dbf RECID=2 STAMP=769862682
cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/users01.dbf RECID=3 STAMP=769862682
cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/example01.dbf RECID=4 STAMP=769862682
cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/ccdata.dbf RECID=5 STAMP=769862682

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=769862682 file name=/u02/database/PRIMDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=769862682 file name=/u02/database/PRIMDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=769862682 file name=/u02/database/PRIMDB/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=769862682 file name=/u02/database/PRIMDB/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=769862682 file name=/u02/database/PRIMDB/ccdata.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database enable block change tracking using file '/u02/database/PRIMDB/bct.dbf'

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

database opened
Executing: alter database flashback on
Finished Duplicate Db at 14-DEC-11
released channel: C1
released channel: DUP

Category: 11G, Oracle, Oracle DB | Comment

Oracle – How to improve delete performance

December 13, 2011, 11:44 am

Problem :

I was asked to delete the 1 Million rows from the table and I wanted make sure that it take bare minimum resources and time.

Solution :

I tried all possible solutions on my test system and here are findings.

I had created a test table with 100000 rows with one index on top of it.

SQL> select count(*) from custaccounts;

  COUNT(*)
----------
   1000000

SQL> select index_name from user_indexes where table_name ='CUSTACCOUNTS';

INDEX_NAME
------------------------------
CA_U1
CA_PK

SQL> select num_rows from user_indexes where table_name ='CUSTACCOUNTS';

  NUM_ROWS
----------
   1017005
   1013011

Elapsed: 00:00:00.13

DELETE NORMALLY:

SQL> delete from CUSTACCOUNTS;

1000000 rows deleted.

Elapsed: 00:35:08.93

This operation had generated 17 Archive logs.

DELETE WITH NOLOGGING:

Most of the people thinks that if they perform delete with nologging , it will have good performance improvement because nologging will not create any redo.Its not true guys.let me prove my point with demo.

SQL> delete from custaccounts nologging;

1000000 rows deleted.

Elapsed: 00:34:09.29

This operation had generated 16 Archive logs.I even tried the rollback operation as well and it went through fine.

DELETE PARALLEL:

Next I thought of doing delete in parallel.

SQL>alter session enable parallel dml;

session altered.

SQL> delete from custaccounts;

1000000 rows deleted.

Elapsed: 00:34:05.29

This operation had generated 16 archive logs.

DELETE INDEX and DELETE ROWS:

Now I tried with deleting the indexes of the table first and than tried the delete operation.I could see good performance improvement with this method.

SQL> drop index CA_U1;

Index dropped.

Elapsed: 00:00:00.75

SQL> select index_name from user_indexes where table_name ='CUSTACCOUNTS';

INDEX_NAME
------------------------------
CA_PK

Elapsed: 00:00:00.17

SQL> delete from custaccounts;

1000000 rows deleted.

Elapsed: 00:23:30.71

This operation had generated 12 archive logs.

CONCLUSION:

After performing delete operation with different option, I came to following conclusion.

Operation	Duration	Archive logs	Recommended
DELETE	00:35:08.93	17	No
DELETE NOLOGGING	00:34:09.29	16	No
DELETE PARALLEL	00:34:08.00	16	No
DELETE WITH NO INDEX	00:23:30.71	12	Yes

Operation Duration Archive logs Recommended

DELETE 00:35:08.93 17 No

DELETE NOLOGGING 00:34:09.29 16 No

DELETE PARALLEL 00:34:08.00 16 No

DELETE WITH NO INDEX 00:23:30.71 12 Yes

Category: 11G, Oracle, Oracle DB | 1 Comment

Trace DBCA

November 16, 2010, 12:10 pm

Sometimes its needed to trace dbca.Please follow the following action plan to trace dbca.

1. Copy dbca to dbca.orig

cp dbca dbca.orig

2. Edit dbca file, fine the last line and add “-DDEBUG” before the “-classpath” argument. The edited line must looks like:

# Run DBCA
$JRE_DIR/bin/java -Dsun.java2d.font.DisableAlgorithmicStyles=true -DORACLE_HOME=$OH
-DDISPLAY=$DISPLAY -DJDBC_PROTOCOL=thin -mx128m -DDEBUG
-classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

-bash-3.00$ dbca
[main] [ 2010-11-16 12:21:42.186 GMT ] [InventoryUtil.getOUIInvSession:347]  setting OUI READ level to ACCESSLEVEL_READ_LOCKLESS
[main] [ 2010-11-16 12:21:42.191 GMT ] [HAUtils.<init>:190]  oui location /rabi/oracle/product/10.2.0.1/db_1/oraInventory/ContentsXML
[main] [ 2010-11-16 12:21:42.314 GMT ] [Version.isPre:274]  version to be checked 11.2.0.1.0 major version to check against10
[main] [ 2010-11-16 12:21:42.316 GMT ] [Version.isPre:285]  isPre.java: Returning FALSE
[main] [ 2010-11-16 12:21:42.333 GMT ] [OCR.loadLibrary:308]
Inside constructor of OCR
[main] [ 2010-11-16 12:21:42.446 GMT ] [OCR.loadLibrary:316]  Going to load the ocr library
[main] [ 2010-11-16 12:21:42.452 GMT ] [sPlatform.isHybrid:69]  osName=SunOS osArch=sparcv9 JVM=64 rc=false
[main] [ 2010-11-16 12:21:42.455 GMT ] [sPlatform.isHybrid:69]  osName=SunOS osArch=sparcv9 JVM=64 rc=false
[main] [ 2010-11-16 12:21:42.457 GMT ] [Library.load:254]  Loading  library /rabi/oracle/product/11.2.0.1/db_1/lib/libsrvmocr11.so
[main] [ 2010-11-16 12:21:42.477 GMT ] [OCR.loadLibrary:318]  loaded ocr libraries
[main] [ 2010-11-16 12:21:42.479 GMT ] [OCR.isCluster:939]  Calling OCRNative for isCluster()
[main] [ 2010-11-16 12:21:42.489 GMT ] [OCR.isCluster:943]  OCR Result status = true
[main] [ 2010-11-16 12:21:42.498 GMT ] [OCR.isCluster:955]  Bolean result = false
[main] [ 2010-11-16 12:21:42.500 GMT ] [HAUtils.getHASHome:585]  Oracle home from system properties: /rabi/oracle/product/11.2.0.1/db_1
-bash-3.00$ dbca [main] [ 2010-11-16 12:21:42.186 GMT ] [InventoryUtil.getOUIInvSession:347]  setting OUI READ level to ACCESSLEVEL_READ_LOCKLESS[main] [ 2010-11-16 12:21:42.191 GMT ] [HAUtils.<init>:190]  oui location /rabi/oracle/product/10.2.0.1/db_1/oraInventory/ContentsXML[main] [ 2010-11-16 12:21:42.314 GMT ] [Version.isPre:274]  version to be checked 11.2.0.1.0 major version to check against10[main] [ 2010-11-16 12:21:42.316 GMT ] [Version.isPre:285]  isPre.java: Returning FALSE[main] [ 2010-11-16 12:21:42.333 GMT ] [OCR.loadLibrary:308]   Inside constructor of OCR[main] [ 2010-11-16 12:21:42.446 GMT ] [OCR.loadLibrary:316]  Going to load the ocr library[main] [ 2010-11-16 12:21:42.452 GMT ] [sPlatform.isHybrid:69]  osName=SunOS osArch=sparcv9 JVM=64 rc=false[main] [ 2010-11-16 12:21:42.455 GMT ] [sPlatform.isHybrid:69]  osName=SunOS osArch=sparcv9 JVM=64 rc=false[main] [ 2010-11-16 12:21:42.457 GMT ] [Library.load:254]  Loading  library /rabi/oracle/product/11.2.0.1/db_1/lib/libsrvmocr11.so[main] [ 2010-11-16 12:21:42.477 GMT ] [OCR.loadLibrary:318]  loaded ocr libraries[main] [ 2010-11-16 12:21:42.479 GMT ] [OCR.isCluster:939]  Calling OCRNative for isCluster()[main] [ 2010-11-16 12:21:42.489 GMT ] [OCR.isCluster:943]  OCR Result status = true[main] [ 2010-11-16 12:21:42.498 GMT ] [OCR.isCluster:955]  Bolean result = false[main] [ 2010-11-16 12:21:42.500 GMT ] [HAUtils.getHASHome:585]  Oracle home from system properties: /rabi/oracle/product/11.2.0.1/db_1

Category: Oracle DB | Comment

Trigger SQL_trace automatically

July 12, 2010, 1:12 pm

Sometimes it is necessary to automatically trigger SQL trace. Automatically here means that code must be added somewhere.

The simplest approach is to create a logon trigger at the database level. To avoid enabling SQL trace for all users, I usually suggest creating a role (named sqltrace in the following example) and temporarily granting it only to the user utilized for the test.


CREATE ROLE sqltrace;

CREATE OR REPLACE TRIGGER enable_sqltrace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQLTRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/

So whenever user with sqltrace role logs in sql tracing will be started by above trigger.

Tags: sql_trace trigger
Category: Oracle DB | Comment

Find trace file using session id in oracle

July 8, 2010, 1:15 pm

Sometimes it becomes very tough to find the relevant oracle trace file using session id in oracle.You can alter your session before setting an event to identify trace file quite easily but following method doesn’t require alteration of session.

SELECT s.sid,
s.server,
lower(
CASE
WHEN s.server IN (‘DEDICATED’,'SHARED’) THEN
i.instance_name || ‘_’ ||
nvl(pp.server_name, nvl(ss.name, ‘ora’)) || ‘_’ ||
p.spid || ‘.trc’
ELSE NULL
END
) AS trace_file_name
FROM v$instance i,
v$session s,
v$process p,
v$px_process pp,
v$shared_server ss
WHERE s.paddr = p.addr
AND s.sid = pp.sid (+)
AND s.paddr = ss.paddr(+)
AND s.type = ‘USER’
ORDER BY s.sid;

Sample Output:

       SID SERVER    TRACE_FILE_NAME
———- ——— ————————————————–
     8    DEDICATED orcl_ora_17842.trc
    10   DEDICATED orcl_ora_17846.trc
    16   DEDICATED orcl_ora_17856.trc
    199 DEDICATED orcl_ora_17848.trc
    394 DEDICATED orcl_ora_17844.trc
    400 DEDICATED orcl_ora_17850.trc
    580 DEDICATED orcl_ora_17852.trc
    585 DEDICATED orcl_ora_17840.trc

8 rows selected.

Category: Oracle DB | Comment

Howto find when table was last updated in Oracle.

June 24, 2010, 10:06 am

SQL> select scn_to_timestamp(max(ora_rowscn)) from WRH$_FILESTATXS;

SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))

—————————————————————————

24-JUN-10 11.00.10.000000000 AM

Category: Oracle DB | 1 Comment

Howto Change ias_admin password

June 16, 2010, 1:51 pm

— ias_admin is user required to use Enterprise Manager Web (Application Server Console – iASConsole) Site.

— ias_admin password is set during Installation of Oracle Application Server (902, 904, 10.1.2.X) or Oracle Identity Management (Infra Tier 10.1.4.X)—ias_admin account is NOT stored in OID (Oracle Internet Directory), It is stored in XML file (JAZN-XML – Java AuthoriZatioN)

How to Reset / Change ias_admin Password

You can reset/change ias_admin password in following ways

1. Using Enterprise Manager (Application Server Control) Web Site
–Login to Instance Home Page
–Click on Preferences on top right
–In new screen, click on “Change Password” on left menu
–Enter current password and New Password
2. Using Command line tool
emctl set password oldpassword new password

emctl set password password1 password2
If you don’t know current ias_admin password then change it in configuration file

3. Change ias_admin password directly in configuration file
–Backup $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
–Search for xml entry like below
< user >
< name > ias_admin < / >
< credentials >{903}8QkQ/crno3lX0f3+67djasdasdvdawe < / credentials >
< /user >

and Update new password (password2 like )

< user >< name > ias_admin < / >
< credentials > !password2 / credentials >
< /user >
Note ! (Exclamation Mark in front of password. This signifies that password is stored in clear text)

Category: Oracle DB | Comment

DB Link Cloning Script

May 21, 2010, 3:58 pm

SELECT

‘create ‘||DECODE(U.NAME,’PUBLIC’,'public ‘)||’database link ‘||CHR(10)

||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.')|| L.NAME||chr(10)

||’connect to ‘ || L.USERID || ‘ identified by ”’

||L.PASSWORD||”’ using ”’ || L.host || ””

||chr(10)||’;’ TEXT

FROM sys.link$ L,

sys.user$ U

WHERE L.OWNER# = U.USER# ;

SELECT’create ‘||DECODE(U.NAME,’PUBLIC’,'public ‘)||’database link ‘||CHR(10)||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.')|| L.NAME||chr(10)||’connect to ‘ || L.USERID || ‘ identified by ”’||L.PASSWORD||”’ using ”’ || L.host || ””||chr(10)||’;’ TEXTFROM sys.link$ L,sys.user$ UWHERE L.OWNER# = U.USER# ;

Category: Oracle DB | Comment

Clone Database Structure

May 14, 2010, 3:59 pm

exp system/oracle file=structure.dmp full=y rows=n
Open the structure.dmp and you will have definitions for all the objects of the database.

Category: Oracle DB | Comment

Oracle – Restrict User login from particular IP Address

Oracle 11G – Active Duplicate Database

Oracle – How to improve delete performance

Trace DBCA

Trigger SQL_trace automatically

Find trace file using session id in oracle

Howto find when table was last updated in Oracle.

Howto Change ias_admin password

DB Link Cloning Script

Clone Database Structure

Categories

Recent Posts