The luck arches the closing ozone

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.

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.

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 :)

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 !!!!!!

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.

Generate DDL scripts using "DBMS_METADATA" package

To Generate all Tablespace DDL's:

set long 2000;

select dbms_metadata.get_ddl('TABLESPACE',tb.name) from v$tablespace tb ;

for 1 tablespace:

select dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual;

To Generate DDL for all PROFILE's :

set long 5000;

select dbms_metadata.get_ddl('PROFILE',a.profile) from dba_profiles a;

For 1 profile:

 select dbms_metadata.get_ddl('PROFILE','PROFILE_NAME') from dual;

To Generate DDL for any TABLE :

set long 5000;

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','OWNER') from dual;

To Generate DDL for any INDEX :

select dbms_metadata.get_ddl('INDEX',’INDEX_NAME’,'OWNER') from dual;

To Generate all INDEX DDL’s on a TABLE:

select dbms_metadata.get_dependent_ddl('INDEX',’TABLE_NAME','OWNER') from dual;

The above command will generate all index ddl’s which are on table.