Dev's Weblog

Archive for the ‘11G’ Category.

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

11GR2 Install Fails on Fedora 13

September 1, 2010, 1:36 pm

Problem:

======

During relinking operation OUI will fail and in $ORACLE_HOME/install/make.log you will find following error.

/u01/app/dev/Middleware/agent11g/lib/libnnz11.so: could not read symbols: Invalid operation
collect2: ld returned 1 exit status
make[1]: *** [/u01/app/dev/Middleware/agent11g/sysman/lib/emdctl] Error 1
make[1]: Leaving directory `/u01/app/dev/Middleware/agent11g/sysman/lib'

Solution:

======

When you start to install with ./runInstaller, run in another terminal window (as root)

ls $ORACLE_HOME/sysman/lib/ins_emagent.mk

At first this will produce an error, as the installer wont have created this file yet.
Once the file exists, do:

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk

Search for the line

$(MK_EMAGENT_NMECTL)

Change it to:

$(MK_EMAGENT_NMECTL) -lnnz11

Category: 11G | 15 Comments

Create database silently in oracle

July 22, 2010, 9:55 am

[oracle@db1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db

[oracle@db1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@db1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName orcl.sysdbaonline.com -sysPassword oracle -systemPassword oracle -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS

Tags: dbca silent
Category: 11G | Comment

DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1

September 11, 2009, 12:23 pm

DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. To see it in action, create a new table and insert a row, but don’t commit the insert.

CREATE TABLE lock_tab (
  id  NUMBER
);

INSERT INTO lock_tab VALUES (1);

Leave this session alone and in a new session, set the DDL_LOCK_TIMEOUT at session level to a non-zero value and attempt to add a column to the table.

ALTER SESSION SET ddl_lock_timeout=30;

ALTER TABLE lock_tab ADD (
  description  VARCHAR2(50)
);

The session will wait for 30 seconds before failing.

ALTER TABLE lock_tab ADD (
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

If we repeat the ALTER TABLE command and commit the insert in the first session within 30 seconds, the ALTER TABLE will return a successful message.

ALTER TABLE lock_tab ADD (
  description  VARCHAR2(50)
);

Table altered.

SQL>

Category: 11G | Comment

REMAP_TABLE & REUSE_DUMPFILES Feature of 11G DataPump

September 8, 2009, 12:09 pm

Often DBAs are needed to import the data of one table of first database to another table of another database.

With 10G DBAs had to export the table and import it to destination DB then rename the table.

But with 11G you can simply REMAP the table to new table name of destination Database.

As for Example,

Source DB : CHICAGO

$sqlplus dev/oracle@CHICAGO

SQL>create table dev as select rownum num from dual connect by rownum <= 1000000;

Check the size of table after its creating using following SQL.

col segment_name format a20
select segment_name,bytes “SIZE_BYTES”,ceil(bytes / 1024 / 1024) “SIZE_MB” from dba_segments where segment_name = ‘DEV’;

SEGMENT_NAME         ?SIZE_BYTES? ?SIZE_MB?
——————–         ————         ———-
DEV                           13631488         13

Export DEV table from DEV schema.

expdp userid=dev/oracle dumpfile=DATA_PUMP_DIR:exp.dmp tables=dev reuse_dumpfiles=y

Reuse_dumpfiles will re-create the dumpfile if the same file exists.This is mostly helpful in case of ASM where you need remove the dumpfile from SQLPLUS if you don’t have access to ASMCMD.

Export: Release 11.2.0.1.0 – Production on Tue Sep 8 12:01:23 2009

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DEV"."SYS_EXPORT_TABLE_01": userid=dev/******** dumpfile=DATA_PUMP_DIR:exp.dmp tables=dev reuse_dumpfiles=y
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEV"."DEV" 8.568 MB 1000000 rows
Master table "DEV"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEV.SYS_EXPORT_TABLE_01 is:
/u01/app/dev/admin/CHICAGO/dpdump/exp.dmp
Job "DEV"."SYS_EXPORT_TABLE_01" successfully completed at 12:01:33

Destination DB : DUP

Import the Dev table to Het table.Please note that Het table must not exists before you import it.

impdp userid=dev/oracle dumpfile=DATA_PUMP_DIR:exp.dmp remap_table=dev.dev:het

Import: Release 11.2.0.1.0 – Production on Tue Sep 8 12:07:33 2009

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEV"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEV"."SYS_IMPORT_FULL_01": userid=dev/******** dumpfile=DATA_PUMP_DIR:exp.dmp remap_table=dev.dev:het
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEV"."HET" 8.568 MB 1000000 rows
Job "DEV"."SYS_IMPORT_FULL_01" successfully completed at 12:07:38

Notice that the REMAP_TABLE option is designated as

[schema.]old_tablename[.partition]:new_tablename.

Check Het table created or not.

dev@shetal:~$ sqlplus dev/oracle@DUP

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 8 12:08:10 2009

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc het
Name Null? Type
—————————————– ——– —————————-
NUM NUMBER

SQL> select count(*) from het;

COUNT(*)
———-
1000000

Enjoy Madi…

Tags: REMAP_TABLE
Category: 11G | Comment

11G – Create PFILE / SPFILE from Memory

September 22, 2008, 3:38 pm

In 11G , you can create pfile or spfile from memory of running instance.

SQL> create pfile from memory;
File created.

You can also use the memory clause to create an spfile, as shown here:

SQL> create spfile from memory;
File created.

You need to have instance up and running to be able to create pfile or spfile from memory.

Category: 11G | Comment

11G – Read Only Table

September 18, 2008, 4:03 pm

Prior to 11G you can have read only tablespace but not tables but from 11G you can read only tables as well.

SQL>create table test as select * from dba_registry;

SQL>alter table test read only;

The new column of DBA_TABLES would tell you that whether table is read only or not.

You can make table read write with following command.

SQL>alter table test read write;

Once you put a table in a read-only mode, you can’t issue any DML statements such as update, insert, or delete. You also can’t issue a select for update statement involving a readonly table. You can issue DDL statements such as drop table and alter table on a read-only table, however. You can use the read-only feature to prevent changes to a table’s data during maintenance operations. You can perform maintenance operations on any of the indexes that you have defined on the read-only table, prior to changing their status to read-only. You can, of course, use this feature for security reasons, where you want to grant users the ability to read but not modify table data.

Category: 11G | Comment

11G – Invisible Indexes

September 18, 2008, 10:27 am

Suppose you want to create huge index on some table but don’t want optimizer to change its plan based on new index then you need to use 11G’s new feature called invisible index.

11G allows you to create index which would be invisible to optimizer.

How to create invisible index

SQL> create index idx on tbl1(name) invisible;
Index created.

You can also specify a tablespace while creating the invisible index.

SQL> create index idx2 on tbl2(name) invisible tablespace users;
Index created.

The index idx2 is stored to users tablespace as just a normal index.

Making index invisible

SQL> alter index idx1 invisible;
Index altered.

Making index visible

SQL> alter index idx1 visible;
Index altered.

You cann’t create two index on same column even if one being invisible.

If you want optimizer to use invisible index while deciding query plan then you need to set following parameter enabled.

SQL> alter session set optimized_use_invisible_indexes=true;

SQL> alter system set optimized_use_invisible_indexes=true;

Category: 11G | Comment

11G – Explicit Locking of Tables

September 17, 2008, 2:03 pm

In 10G if you would like to add a column to any table and if oracle is not able to acquire the exclusive lock on that table because of frquent updates on that table then DDL command would fail immediately.

But in 11G you can ask Oracle to wait before it fails in case of its inability to acquire the necessary DML lock on the table.

The new command LOCK TABLE lets you specify the maximum time a statement should wait to obtain a DML lock on a table.

The syntax for new command is ,

LOCK TABLE … IN lockmode MODE [NOWAIT | WAIT integer]

nowait and wait means :

Nowait – Immediate error.
Wait – Will wait number of seconds before it fails to acquire DML lock.You can set the value of this parameter to any integer value you want—there’s no limit.
If you don’t specify either wait or nowait, the database will wait until the locked table is available and then lock it before returning control to you.

Category: 11G | Comment

Dev's Weblog

Oracle 11G – Active Duplicate Database

Oracle – How to improve delete performance

11GR2 Install Fails on Fedora 13

Create database silently in oracle

DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1

REMAP_TABLE & REUSE_DUMPFILES Feature of 11G DataPump

11G – Create PFILE / SPFILE from Memory

11G – Read Only Table

11G – Invisible Indexes

11G – Explicit Locking of Tables

Categories

Recent Posts