Flowers To India

Flowers To India
Send cakes,flowers to India

Friday, November 23, 2012

12c grid Agent deployment error

If you see the below error while deploying the 12c agentput true in agentpush.properties and make sure you add correct target hostname in /etc/hosts

You can either set the "oracle.sysman.prov.agentpush.enablePty" property to true in the "/u01/app/oracle/oms12cr2/oms/sysman/prov/agentpush/agentpush.properties" file, pass -S argument to the sudo command, or enable the "visiblepw" flag in the sudoers file


Wednesday, November 21, 2012

ex: 0602-101 Out of memory saving lines for undo

# Avoid problems when using vi to edit large files (files with many lines) in AIX
export EXINIT="set ll=20000000"
 
 

Monday, November 19, 2012

How to use DBMS_METADATA.GET_DDL for ddl

DBMS_METADATA.GET_DDL


Oracle introduced the usage of DBMS_METADSATA package's GET_DDL function to retrieve the DDL of a particular object type from 9i onwards. It takes two/three arguments depending on the type of object you want to retrieve the DDL for.

GENERAL USAGE
set long 300000 pages 0 lines 140
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;

The 'OWNER' argument is sometimes optional if you are connected as the same user who owns the object you are trying to retreive the DDL for. Pasted below are the statements to generate the DDL for most of the object types.

select dbms_metadata.get_ddl('CLUSTER','C_MLOG#','SYS') from dual;
select dbms_metadata.get_ddl('CONTEXT','LT_CTX') from dual;
select dbms_metadata.get_ddl('DB_LINK','PROD.WORLD','ADAM') from dual;
select dbms_metadata.get_ddl('DB_LINK','TEST.WORLD','PUBLIC') from dual;
select dbms_metadata.get_ddl('FUNCTION','TO_DATE_FUNC','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','REPCAT$_REPCOLUMN_PK','SYSTEM') from dual;
select dbms_metadata.get_ddl('JAVA_SOURCE','java_util','ADAM') from dual
select dbms_metadata.get_ddl('JAVA_SOURCE','/6c363944_Dumper','SYS') from dual
select dbms_metadata.get_ddl('LIBRARY','UTL_SMT_LIB','SYS') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BS_TAB_MV','SYS') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_BS_TABLE','SYS') from dual;
select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION') from dual;
select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE','XMLPARSER') from dual;
select dbms_metadata.get_ddl('PACKAGE','XMLPARSER','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE_BODY','STATSPACK','PERFSTAT') from dual;
select dbms_metadata.get_ddl('PROCEDURE','ORA$_SYS_REP_AUTH','SYSTEM') from dual;
select dbms_metadata.get_ddl('SEQUENCE','STATS$SNAPSHOT_ID','PERFSTAT') from dual;
select dbms_metadata.get_ddl('SYNONYM','/2fddc42_paintARGB_PRE_ONTO_S5','PUBLIC') from dual;
select dbms_metadata.get_ddl('TABLE','SQLEXPERT_PLAN1','SYSTEM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
select dbms_metadata.get_ddl('TRIGGER','DEF$_PROPAGATOR_TRIG','SYSTEM') from dual;
select dbms_metadata.get_ddl('TYPE','XMLSEQ_IMP_T','SYS') from dual;
select dbms_metadata.get_ddl('TYPE_BODY','ORACLE_LOADER','SYS') from dual;
select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION','SYS') from dual;
select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION') from dual;
select dbms_metadata.get_ddl('PACKAGE','DBMS_METADATA','SYS') from dual;

Sunday, November 18, 2012

How to redirect grid agent to another OMS in 12cGrid Control

 Redirecting Oracle Management Agent to Another OMS

If you want to redirect or repoint your Oracle Management Agent (Management Agent), which is already communicating with an Oracle Management Service (OMS), to communicate and upload data to another OMS, then follow these steps:
  1. Stop the Management Agent:
    $/bin/emctl stop agent
  2. Remove the Management Agent instance home:
    rm -rf
  3. From the Enterprise Manager Cloud Control console, remove the targets monitored by the Management Agent.
  4. Create a new instance home for the Management Agent so that it directs to the new OMS:
    $/core/12.1.0.2.0/sysman/install/agentDeploy.sh AGENT_BASE_DIR= OMS_HOST= EM_UPLOAD_PORT= AGENT_REGISTRATION_PASSWORD= AGENT_INSTANCE_HOME= -configOnly
    Note:
    The specified agent base directory location and the new agent instance home location map to locations on the same host, where the Management Agent was already configured. The OMS host name, of course, maps to the other host where the new OMS is configured, that is, the OMS with which you want the Management Agent to communicate now.

Wednesday, November 14, 2012

How to spool data to excel sheet from Oracle

#sqlplus user/password
SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;
SQL> SPOOL test.xls;
SQL> SELECT * FROM TAB;
SQL> SPOOL OFF;
------------------------------------------------------------------------

For not getting any sql or feed back or lines, for getting proper excel report I have done like below.

export ORACLE_SID=sid
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export ORACLE_HOME=home path
export PATH=$ORACLE_HOME/bin:$PATH
export DD=`date +%d%m%Y`
sqlplus username/password <show user;
alter session set nls_date_format='dd/mm/yyyy';
set pagesize 0 trimspool on feedback off lines 900;
set feedback off;
SET VERIFY    OFF;
SET TRIMSPOOL ON ;
set trimspool on;
set pages 1500;
set lines 1000;
set underline off;
set colsep ','
set echo off
@sqlscript.sql
exit;
EOF


in sqlscript.sql
spool filename_${DD}.csv
select * from emp;
spool off;
spool filename2.csv
select * from dept;
spool off