Friday, November 2, 2012

How to create Enterprise Manager Configuration Assistant (EMCA) in RAC Database Environment

[oracle@lv-rac-test1 scripts]$ emca -config dbcontrol db -repos create

STARTED EMCA at Nov 2, 2012 4:58:46 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TESTR1
Listener port number: 1521
Listener ORACLE_HOME [ /opt/app/oracle/112/product/11.2.0/dbhome_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): oracle@firstdata.lv
Outgoing Mail (SMTP) server for notifications (optional): lv-mail
ASM ORACLE_HOME [ /opt/app/oracle/112/product/11.2.0/dbhome_1 ]: /opt/app/11.2.0/grid
ASM SID [ +ASM ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]:
ASM user password:
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /opt/app/oracle/112/product/11.2.0/dbhome_1

Local hostname ................ lv-rac-test1.ne.1dc.com
Listener ORACLE_HOME ................ /opt/app/oracle/112/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ TESTR1
Email address for notifications ............... oracle@firstdata.lv
Outgoing Mail (SMTP) server for notifications ............... lv-mail
ASM ORACLE_HOME ................ /opt/app/11.2.0/grid
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 2, 2012 4:59:46 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/app/oracle/112/cfgtoollogs/emca/TESTR/emca_2012_11_02_16_58_46.log.
Nov 2, 2012 4:59:47 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Nov 2, 2012 5:05:30 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Nov 2, 2012 5:05:32 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Nov 2, 2012 5:06:17 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Nov 2, 2012 5:06:19 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Nov 2, 2012 5:06:25 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Nov 2, 2012 5:06:25 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Nov 2, 2012 5:06:39 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Nov 2, 2012 5:06:39 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://lv-rac-test1.ne.1dc.com:5501/em <<<<<<<<<<<
Nov 2, 2012 5:06:41 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /opt/app/oracle/112/product/11.2.0/dbhome_1/lv-rac-test1_TESTR/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 2, 2012 5:06:41 PM
[oracle@lv-rac-test1 scripts]$
[oracle@lv-rac-test1 scripts]$
[oracle@lv-rac-test1 scripts]$ /opt/app/oracle/112/product/11.2.0/dbhome_1/bin/emca -config dbcontrol db -cluster -ASM_USER_ROLE SYSDBA -ASM_USER_NAME ASMSNMP -CLUSTER_NAME lv-rac -LOG_FILE /opt/app/oracle/112/admin/TESTR/scripts/emConfig.log -SID TESTR -ASM_SID +ASM1 -DB_UNIQUE_NAME TESTR -EM_HOME /opt/app/oracle/112/product/11.2.0/dbhome_1 -SERVICE_NAME TESTR -ASM_PORT 1521 -PORT 1521 -LISTENER_OH /opt/app/11.2.0/grid -LISTENER LISTENER -ORACLE_HOME /opt/app/oracle/112/product/11.2.0/dbhome_1 -HOST lv-rac-test1 -ASM_OH /opt/app/11.2.0/grid;

STARTED EMCA at Nov 2, 2012 5:07:22 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Password for SYS user:
Database Control is already configured for the database TESTR
You have chosen to configure Database Control for managing the database TESTR
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): oracle@firstdata.lv
Outgoing Mail (SMTP) server for notifications (optional): lv-mail
ASM user password:
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /opt/app/oracle/112/product/11.2.0/dbhome_1

Database instance hostname ................ Listener ORACLE_HOME ................ /opt/app/11.2.0/grid
Listener port number ................ 1521
Cluster name ................ lv-rac
Database unique name ................ TESTR
Email address for notifications ............... oracle@firstdata.lv
Outgoing Mail (SMTP) server for notifications ............... lv-mail
ASM ORACLE_HOME ................ /opt/app/11.2.0/grid
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 2, 2012 5:08:03 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/app/oracle/112/admin/TESTR/scripts/emConfig.log.
Nov 2, 2012 5:08:05 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Nov 2, 2012 5:08:30 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Nov 2, 2012 5:09:24 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Nov 2, 2012 5:09:24 PM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /opt/app/oracle/112/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_lv-rac-test1_TESTR to remote nodes ...
Nov 2, 2012 5:09:26 PM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /opt/app/oracle/112/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_lv-rac-test2_TESTR to remote nodes ...
Nov 2, 2012 5:09:34 PM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /opt/app/oracle/112/product/11.2.0/dbhome_1/lv-rac-test1_TESTR to remote nodes ...
Nov 2, 2012 5:09:37 PM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /opt/app/oracle/112/product/11.2.0/dbhome_1/lv-rac-test2_TESTR to remote nodes ...
Nov 2, 2012 5:09:40 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Nov 2, 2012 5:10:06 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Nov 2, 2012 5:10:30 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Nov 2, 2012 5:10:30 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://lv-rac-test1.ne.1dc.com:5501/em <<<<<<<<<<<
Nov 2, 2012 5:10:32 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
****************  Current Configuration  ****************
 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST
----------        ----------        ---------------------

TESTR             lv-rac-test1             lv-rac-test1.ne.1dc.com
TESTR             lv-rac-test2             lv-rac-test1.ne.1dc.com


Nov 2, 2012 5:10:32 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /opt/app/oracle/112/product/11.2.0/dbhome_1/lv-rac-test1_TESTR/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 2, 2012 5:10:32 PM

Wednesday, October 31, 2012

ASM Плюсы и Минусы



ASM как таковой мне нравится, но в администрирование занимает больше ресурсов. В простых инстанциях его ат-инсталлировал.

Плюсы:
1. достаточно большая пропускная способность, по сравнению с ЕХТ3, ЕХТ4 + АSYNC I/O. С АSYNC на 11.2.0.2 г версии есть проблемы.
2. ДБА трудно удалить, сделать ошибку и удалить дата файлы.
3. ребаланс данных возможен, по всем дискам.
4. возможно использовать без RAID.
5. RAC и только в нем лучше использовать ASM, в Single instance насоветую, если только не хотите экономить на дисках и покупать дорогостоящий массив.
6. В системах где редко делают патчинг может и можно использовать.


Минусы:
1.Трудно администрировать, делать UPGRADE, PATCHING итд. Каждый раз при смене KERNEL надо менять ASM libraries. Может если LINUX от ORACLE, может не так и плохо, там всё включено, но у меня RHEL. У нас патчинг каждый квартал. Соответственно надо тест, Preproduction,production upgrade. People ресурсов не напасёшься на ASM.

Tuesday, October 23, 2012

How to Disable Oracle Label Security



-bash-3.2$ chopt disable lbac

Writing to /opt/oracle/112/product/11.2.0/dbhome_1/install/disable_lbac.log...

/usr/bin/make -f /opt/oracle/112/product/11.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk lbac_off ORACLE_HOME=/opt/oracle/112/product/11.2.0/dbhome_1

/usr/bin/make -f /opt/oracle/112/product/11.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/opt/oracle/112/product/11.2.0/dbhome_1


restart oracle dbs ( otherwise you will get Warning)

WARNING: Oracle executable binary mismatch detected.
 Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages

deinstall LBACSYS with sqlplus

sysdba

select object_type,count(*) from dba_objects where owner='LBACSYS'  group by object_type;



@?/rdbms/admin/catnools.sql


done

 -------------------- SCRIPT

select object_type,count(*) from dba_objects where owner='LBACSYS'  group by object_type;


prompt disable
prompt !host chopt disable lbac
prompt alter system set "_disable_image_check" = true scope=memory;;
prompt @?/rdbms/admin/catnools.sql

prompt enable
prompt !host chopt enable lbac
prompt @?/rdbms/admin/catols.sql

prompt INVALID YES NO?

select object_type,object_name from dba_objects where owner='LBACSYS' and status='INVALID';


prompt @?/rdbms/admin/utlrp

Wednesday, June 20, 2012

SQL Developer import from Excel or CSV

Goal: how to import EXCEL,CSV data to Oracle or Mysql DB.


  • install SQL DEVELOPER on Windows or Linux. In this example SQL DEV 3.1.07 (optional).
  • installation on windows download sqldeveloper-3.1.07.42.zip unzip use it
  • installation on linux RHEL 5, download JDK and SQL DEVELOPER
with root:



and


execute with oracle or root:
[oracle@lv-test ~]$ sqldeveloper
Oracle SQL Developer
 Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.




  • Check what NLS is on DB(optional).
  • Check NLS settings in SQL DEV tools->preferences-NLS (optional).

  • Create a connection to the database in SQL Developer with proper NLS settings
  • Create a table as a destination for the xls data
Example:
create table TEST1 (column1 VARCHAR(100),column2 VARCHAR(100));  

  • right click on the table select 'import data' 



  • use xls or csv data format
  • select file
  • use the wizzard 

 done

Tuesday, April 17, 2012

OUG Harmony 2012

Harmony is on 1st of June in Riga, Latvia Check the Agenda

Latvian Oracle User Group (LVOUG) and Independent User Group, will hold its Annual Conference in Riga.

Attendees are expected from Latvia, Estonia, Finland and Russia. Being a paid event, most attendees will be from End Users, Oracle & System Integrators.


Tom Kyte from Oracle will communicate the future roadmap in his keynote address.