Dev's Weblog

Reading time: 1 - 2 minutes

Problem:

======

During relinking operation OUI will fail and in $ORACLE_HOME/install/make.log Ayou 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'

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

Reading time: < 1 minute

[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

Reading time: 2 - 2 minutes

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>

Reading time: 3 - 5 minutes

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

1. 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

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

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

1. 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

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

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

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

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…

Reading time: < 1 minute

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.