Oracle: 2013

Friday, November 1, 2013

ORA-38500: Unsupported operation: Oracle XML DB not present

when i'm trying to do schema import with content=data_only option then i got this error below

ORA-31693: Table data object "SCOTT"."TEST" failed to load/unload and is being skipped due to error:
ORA-38500: Unsupported operation: Oracle XML DB not present

Then i checked the XDB status and it is installed and VALID status but still getting this error during the import

SQL> select substr(comp_id,1,15) comp_id, substr(comp_name,1,35) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

COMP_ID COMP_NAME VERSION STATUS
--------------- ----------------------------------- ---------- -----------
EM Oracle Enterprise Manager 11.2.0.3.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.3.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.3.0 VALID
OWM Oracle Workspace Manager 11.2.0.3.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.3.0 VALID
XDB Oracle XML Database 11.2.0.3.0 VALID
CONTEXT Oracle Text 11.2.0.3.0 VALID
EXF Oracle Expression Filter 11.2.0.3.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0 VALID
XML Oracle XDK 11.2.0.3.0 VALID
RUL Oracle Rules Manager 11.2.0.3.0 VALID

COMP_ID COMP_NAME VERSION STATUS
--------------- ----------------------------------- ---------- -----------
ORDIM Oracle Multimedia 11.2.0.3.0 VALID
SDO Spatial 11.2.0.3.0 VALID
AMD OLAP Catalog 11.2.0.3.0 VALID
APS OLAP Analytic Workspace 11.2.0.3.0 VALID
XOQ Oracle OLAP API 11.2.0.3.0 VALID
OWB OWB 11.2.0.2.0 VALID
APEX Oracle Application Express 3.2.1.00.1 VALID

18 rows selected.

After researching, according to Doc ID 1375713.1, The DBMS_METADATA_DIFF package was not completely compiled when it was created.
then performed the below steps

SQL> alter package dbms_metadata_int compile plsql_ccflags = 'ku$xml_enabled:true';
Package altered.

SQL> alter package dbms_metadata_util compile plsql_ccflags = 'ku$xml_enabled:true';
Package altered.

and started import again then it WORKED !!

More on XDB :

Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)

Wednesday, October 2, 2013

Uninstall Oracle GOLDENGATE on LINUX

1. Log on to the database server (as oracle) where the GoldenGate software is installed.

cd /home/oracle/ggs

2. Start GGSCI:

./ggsci

3. Stop all GoldenGate processes:

GGSCI (dbserver1) 1> stop EXTRACT *

GGSCI (dbserver1) 1> stop REPLICAT *

Then:

GGSCI (dbserver1) 2> stop MGR

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER …

Request processed.

Manager stopped.

GGSCI (dbserver1) 3> exit

4. Change directory to the installation directory:

cd /home/oracle

5. Remove the GoldenGate files:

rm -rf ggs

6. Logon to the Oracle database as SYSDBA and drop the GoldenGate Admin user. Include the CASCADE keyword:

oracle@host.com:/opt/oracle INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 1 19:19:23 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user gguser cascade;

drop user gguser cascade

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2

ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot

DROP object used in Oracle GoldenGate replication while trigger is enabled.

Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical

Support if you wish to do so., error stack: ORA-06512: at line 231

ORA-06512: at line 1030

SQL> select * from dba_triggers db where db.owner='GGUSER';

no rows selected

SQL> SELECT a.obj#, a.sys_evts, b.nameFROM trigger$ a,obj$ b

WHERE a.sys_evts> 0AND a.obj#=b.obj#AND baseobject = 0;

OBJ# SYS_EVTS NAME

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

204316 8256 EXPFIL_ALTEREXPTAB_MAINT

204314 128 EXPFIL_DROPUSR_MAINT

204464 4096 RLMGR_TRUNCATE_MAINT

357922 64 CDC_ALTER_CTABLE_BEFORE

357923 32 CDC_CREATE_CTABLE_AFTER

357924 32 CDC_CREATE_CTABLE_BEFORE

357925 128 CDC_DROP_CTABLE_BEFORE

10520 8416 NO_VM_DDL

10521 128 NO_VM_DROP_A

204315 96 EXPFIL_RESTRICT_TYPEEVOLVE

204313 128 EXPFIL_DROPOBJ_MAINT

OBJ# SYS_EVTS NAME

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

805933 524256 GGS_DDL_TRIGGER_BEFORE

203646 4224 XDB_PI_TRIG

7743 128 AW_DROP_TRG

341038 4096 AW_TRUNC_TRG

341040 8192 AW_REN_TRG

16 rows selected.

SQL> drop trigger ggs_ddl_trigger_before;

Trigger dropped.

SQL> drop user gguser cascade;

User dropped.

Sunday, September 8, 2013

Install oracle 11.2.0.3 on Linux 64 bit

Download binaries from Oracle and SCP .zip files to server

Unzip both zip files and you will see “database” folder created.

oracle@t01odsdev201:/opt/oracle/11.2.0.3 INT$ cd database

oracle@t01odsdev201:/opt/oracle/11.2.0.3/database INT$ ls

doc install readme.html response rpm runInstaller sshsetup stage welcome.html

oracle@t01odsdev201:/opt/oracle/11.2.0.3/database INT$ export DISPLAY=10.109.38.156:0

oracle@t01odsdev201:/opt/oracle/11.2.0.3/database INT$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 3893 MB Passed

Checking swap space: must be greater than 150 MB. Actual 9565 MB Passed

Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-09-05_03-55-01PM. Please wait ...oracle@t01odsdev201

Click on “fix& Check again” then it will prompt you to run script, then open a new terminal and run the script and check “ignore All” button and continue.

After finished running, it will prompt you to run root.sh script. Open a new terminal and run the script as root user.

Install oracle 12.1.0.1 database on Linux 64 bit

Download oracle 12c software and SCP .zip files to server

Unzip both zip files and you see “database” folder created.

oracle@t01odsdev201:/opt/oracle/12c INT$ unzip linuxamd64_12c_database_1of2.zip

oracle@t01odsdev201:/opt/oracle/12c INT$ unzip linuxamd64_12c_database_2of2.zip

oracle@t01odsdev201:/opt/oracle/12c INT$ ls

database linuxamd64_12c_database_1of2.zip linuxamd64_12c_database_2of2.zip

oracle@t01odsdev201:/opt/oracle/12c INT$ cd database

oracle@t01odsdev201:/opt/oracle/12c/database INT$ ls

install response rpm runInstaller sshsetup stage welcome.html

Invoke above “runInstaller” and then GUI will be seen

oracle@t01odsdev201:/opt/oracle/12c/database INT$ export DISPLAY=10.119.38.156:0

oracle@t01odsdev201:/opt/oracle/12c/database INT$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 3889 MB Passed

Checking swap space: must be greater than 150 MB. Actual 9781 MB Passed

Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-09-05_09-28-14PM. Please wait ...

uncheck the box and continue ................

specify your Oracle Home & Software location

Click on “fix& Check again” then it will prompt you to run script, then open a new terminal and run the script and check “ignore All” button and continue.

After finished running, it will prompt you to run root.sh script. Open a new terminal and run the script as root user.

Friday, August 23, 2013

create SOURCEDEF file using Goldengate DEFGEN utility

Data definitions are needed when the source and target tables have different definitions or the databases are of different types.

Perform below steps on the SOURCE database from which you want to obtain metadata definitions.

From the Oracle GoldenGate directory, run GGSCI.

1. In GGSCI, issue the following command to create a DEFGEN parameter file.

ggsci > Edit params defgen

defsfile ./dirsql/SOURCE_DEFGEN.def

userid gguser password ******

TABLE schema.*;

2. Enter the parameters listed above in the order shown and invoke DEFGEN from GG_HOME location

$ ./defgen paramfile ./dirprm/defgen.prm

3. Use FTP or SFTP to transfer this SOURCE_DEFGEN.def file to the TARGET server

4. Specify this copied SOURCE_DEFGEN.def file location in REPLICAT param file

ex: SOURCEDEFS ./dirsql/SOURCE_DEFGEN.def

For every DDL change happen to the source tables (only tables which are captured for replication) generate a NEW sourcedef file and copied to the target server

NOTE: Do not create a data-definitions file for Oracle sequences. It is not needed and DEFGEN does not support it.

Friday, June 21, 2013

ORA-39168: Object path STATISTICS was not found during datapump impdp

Today while i'm trying to do schema import and i got an error below:

ORA-39168: Object path STATISTICS was not found

CAUSE:

Expdp directory=DIR_NAME dumpfile=DUMPFILE_NAME.dmp logfile=LOGFILE_NAME.log schemas=SCHEMA_NAME exclude=statistics

My export Done successful without any errors

Now, I’m trying the import the same into my target database

Impdp directory=DIR_NAME dumpfile=DUMPFILE_NAME.dmp logfile=LOGFILE_NAME.log exclude=statistics

Import: Release 11.2.0.2.0 - Production on Fri Jun 21 16:15:14 2013

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39168: Object path STATISTICS was not found.

The error is because i already used EXCLUDE=STATISTICS parameter during expdp job and again trying to exclude statistics again during the impdp operation, and resulting in the error.

But we can use EXCLUDE=STATISTICS parameter in the impdp(import) operation when we didn’t include this parameter in the export job.

Solution: Remove EXCLUDE=STATISTICS parameter from the import job and run

Friday, June 14, 2013

Install Oracle Goldengate on LINUX 11gr2

Here I’m going to install oracle Goldengate 11.2.1.0.3 on my oracle 11.2.0.2 database, Linux 64 bit

If it is a RAC, then install in a common location

Download Goldengate Software and move the ZIP file to Linux server

Copy software(ZIP file) to some directory in the database and unzip the file

$ Mkdir ggate

$ cd ggate

$ unzip V34339-01.zip

Archive: V34339-01.zip

inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar

inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc

inflating: Oracle GoldenGate_11.2.1.0.3_README.txt

inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf

$ tar -xvof fbo_ggs_Linux_x64_ora11g_64bit.tar

$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Create Sub directories using "CREATE SUBDIRS”, this will create all required sub directories for oracle goldengate.

GGSCI (oracledev.domain.com) 1> CREATE SUBDIRS

Creating subdirectories under current directory /opt/oracle/GG

Parameter files /opt/oracle/GG/dirprm: already exists

Report files /opt/oracle/GG/dirrpt: created

Checkpoint files /opt/oracle/GG/dirchk: created

Process status files /opt/oracle/GG/dirpcs: created

SQL script files /opt/oracle/GG/dirsql: created

Database definitions files /opt/oracle/GG/dirdef: created

Extract data files /opt/oracle/GG/dirdat: created

Temporary files /opt/oracle/GG/dirtmp: created

Stdout files /opt/oracle/GG/dirout: created

GGSCI (oracledev.domain.com) 2>

Then we need to create a database user and tablespace on both Source and TARGET servers which will be used by the GoldenGate Manager, Extract and Replicat processes.

$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 14 20:59:57 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace ggs_data datafile '/opt/oracle/ggs_data01.dbf' size 100m autoextend on next 10m maxsize unlimited;

Tablespace created.

SQL> create user gguser identified by gguser default tablespace ggs_data temporary tablespace temp;

User created.

SOURCE grants:

SQL> grant create session to gguser;

grant connect,resource to gguser;

GRANT ALTER ANY TABLE TO GGUSER;

GRANT CREATE ANY TABLE TO GGUSER;

GRANT CREATE TABLE TO GGUSER;

GRANT DELETE ANY TABLE TO GGUSER;

GRANT DROP ANY TABLE TO GGUSER;

GRANT FLASHBACK ANY TABLE TO GGUSER;

GRANT INSERT ANY TABLE TO GGUSER;

GRANT SELECT ANY DICTIONARY TO GGUSER;

GRANT SELECT ANY TABLE TO GGUSER;

GRANT QUOTA UNLIMITED ON GGS_DATA TO GGUSER;

GRANT UPDATE ANY TABLE TO GGUSER;

TARGET grants:

GRANT CREATE SESSION to gguser;

GRANT ALTER SESSION to gguser;

GRANT ALTER SYSTEM to gguser;

GRANT RESOURCE to gguser;

GRANT CONNECT to gguser;

GRANT SELECT ANY DICTIONARY to gguser;

GRANT SELECT ANY TABLE to gguser;

GRANT INSERT,UPDATE, DELETE ON TARGET_SCHEMA.* to gguser;

GRANT CREATE TABLE to gguser;

NOTE: Please refer below Oracle Goldengate installation Doc for the GGUSER grants on Source and Target side
http://docs.oracle.com/cd/E35209_01/doc.1121/e35957.pdf

Saturday, April 20, 2013

Upgrade OPatch version from 11.2.0.1.1 to 11.2.0.3.3

I want to update my OPatch version from 11.2.0.1.1 to 11.2.0.3.3 on My LINUX server, to do so we need to apply patch 6880880

$ cd $ORACLE_HOME/OPatch

$ ls

crs docs emdpatch.pl fmw jlib ocm opatch opatch.ini opatch.pl opatchprereqs

$ ./opatch lsinventory

Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1

Oracle Home : /opt/oracle/app/11.2.0.2

Central Inventory : /opt/oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.1

OUI version : 11.2.0.2.0

OUI location : /opt/oracle/app/11.2.0.2/oui

Log file location : /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/opatch2013-03-19_15-45-56PM.log

Patch history file: /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/lsinv/lsinventory2013-03-19_15-45-56PM.txt

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

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.2.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

Download patch 6880880 from Oracle support and Copy the p6880880_112000_Linux-x86-64.zip file to $ORACLE_HOME location.

Before unzip the file backup the existing OPatch folder

$mv OPatch OPatch.bak

Now, unzip the file then you’ll see new OPatch folder.

$ unzip p6880880_112000_Linux-x86-64.zip

New OPatch version is created.

$ cd /opt/oracle/app/11.2.0.2/OPatch

$ ./opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.3

Oracle Home : /opt/oracle/app/11.2.0.2

Central Inventory : /opt/oracle/oraInventory

from : /opt/oracle/app/11.2.0.2/oraInst.loc

OPatch version : 11.2.0.3.3

OUI version : 11.2.0.2.0

Log file location : /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/opatch2013-03-19_16-05-48PM_1.log

Lsinventory Output file location : /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/lsinv/lsinventory2013-03-19_16-05-48PM.txt

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

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.2.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

Saturday, April 6, 2013

ORA-39213: Metadata processing is not available

select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

when i'm trying to take full database export using DATAPUMP i got an error below:

$ expdp directory=EXPORT dumpfile=FULL_devdb.dmp logfile=exp_FULL_devdb.log full=y

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 06 April, 2013 16:09:04

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39006: internal error

ORA-39213: Metadata processing is not available

Then i checked,

SQL > SELECT comp_id, version, status FROM dba_registry;

COMP_ID VERSION STATUS

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

OWM 10.2.0.5.0 VALID

CATALOG 10.2.0.5.0 VALID

CATPROC 10.2.0.5.0 VALID

JAVAVM 10.2.0.5.0 VALID

According to Metalink ID: 402242.1,

DataPump export uses DBMS_METADATA_UTIL package but this package does not successfully ran. Executing sys.dbms_metadata_util.load_stylesheets procedure failed with following error:

ORA-31609: error loading file "kualter.xsl" from file system directory "/rdbms/xml/xsl"

ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 1807

ORA-06512: at line 1

Then I ran

connect / as sysdba

SQL > execute sys.dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

Then my export went successful.

Monday, April 1, 2013

100K HITS

Wow its time to celebrate, i hit 100,000 blog hits !! very happy to see such a huge number, i really thank each and everyone who is visiting my blog and make this happen, This is the best motivation for me to post more and new kind of issues/posts. Thanks all again :)

Tuesday, March 26, 2013

Add database to 11g OEM Grid control

Here I have a 11g Grid control running on Linux x86_64.

To add a database in the Grid control, Open OEM grid control page --> targets --> databases

Click on ADD button, then the below screenshot will appear

Select the hostname from the list shown and click continue.

NOTE : The hostnames are displayed when that server is configured with the GRID server, if not configure.

It will take some time to find and display the targets and the listeners found on that host(server).

Select the target you want to add and the listener associated with it. Then click on configure button for the selected target and you’ll get below screen.

Give ‘dbsnmp’ password and fill all the fields correctly then click next and ok.

Done, now you’ll see your target(database) in the OEM grid database list. Njoy !!!!!!

Thursday, March 21, 2013

Move datafile to NEW Location

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

SQL> select name from v$datafile;

NAME

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

/opt/oracle/data/DEV3/system01.dbf

/opt/oracle/data/DEV3/sysaux01.dbf

/opt/oracle/data/TEST01.dbf

Here my TEST tablespace datafile is not in DEV3 location, so I wanted to move into /DEV location.

Here I’m testing in my DEV database but make sure no one using this tablespace if this is doing in prod database J

1) Make the corresponding Tablespace OFFLINE

SQL> ALTER tablespace TEST offline;

Tablespace altered.

2) Update the data dictionary pointing to new location using below command:

SQL> ALTER DATABASE RENAME FILE '/opt/oracle/data/TEST01.dbf' to '/opt/oracle/data/DEV3/TEST01.dbf';

Database altered.

3) Now, recover the datafile from new location:

SQL> RECOVER DATAFILE '/opt/oracle/data/DEV3/TEST01.dbf';

Media recovery complete.

4) Bring back the TEST tablespace ONLINE

SQL> ALTER tablespace TEST online;

Tablespace altered.

SQL> select name from v$datafile;

NAME

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

/opt/oracle/data/DEV3/system01.dbf

/opt/oracle/data/DEV3/sysaux01.dbf

/opt/oracle/data/DEV3/TEST01.dbf

Now my TEST datafile moved to the new location.