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 :

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 *

or

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

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


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

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

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

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


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

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

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
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


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
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


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

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

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.

Auto Scroll Stop Scroll