Oracle 10g dbconsole setup

How to see the status of dbconsole and the URL :

su - oracle
emctl status dbconsole

TZ set to Europe/Paris
Oracle Enterprise Manager 10g Database Control Release 10.2.0.3.0
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
http://ora-stb:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db64/ora-stb_URCL2/sysman/log

How to start the dbconsole :

su - oracle
emctl start dbconsole

How to stop the dbconsole :

su - oracle
emctl stop dbconsole

Recreate DbConsole online

The database will be placed in quiecse mode if you are using emca to drop and recreate. For example, the following commands will quiesce database:

emca -deconfig dbcontrol db -repos drop emca -config dbcontrol db -repos recreate

But "emca -config dbcontrol db -repos create" will not place the database in quiecse state.
So being said this, I would suggest you to manually drop the dbcontrol and then create it using emca which does not require downtime.
So you can do the following steps to recreate the database control which does not place the database in quiesce state:
Make sure that the database and the listener is up and running.
Logon SQLPLUS as user SYS and drop the sysman account and mangement objects:

drop user sysman cascade;
drop role MGMT_USER;
drop user MGMT_VIEW cascade;
drop public synonym MGMT_TARGET_BLACKOUTS;
drop public synonym SETEMVIEWUSERCONTEXT;

Manually remove the following folders:

ORACLE_HOME/host_sid/ ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_host_sid

For example if ORACLE_HOME is /u01/app/oracle and hostname is test.oracle.com and sid is db1, then the above mentioned folders would be:

/u01/app/oracle/oracle/test.oracle.com_db1 /u01/app/oracle/oc4j/j2ee/OC4J_DBConsole_test.oracle.com_db1

Set the ORACLE_HOME and ORACLE_SID
From $ORACLE_HOME/bin issue the following command:

emca -deconfig dbcontrol db -repos drop

Attention ! Database in quiesce mode

emca -config dbcontrol db -repos create

Reconfigure the Port

emca -reconfig ports -DBCONTROL_HTTP_PORT 5500

For more information see Metalink Doc: https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=278100.1&blackframe=1

Install Oracle Grid Control 11g

To install Oracle Grid Control, you need to have a few components installed beforehand.
* Oracle database 11g
* Oracle Weblogic server

Once this is done, you can install it. The steps bellow will guide you from the beginning.


Step 1 : Install the DB
It will be detailed in another post. Link to come soon.

Step 2 : Install the weblogic server
Be sure you have the java 1.6 SDK installed:

[oracle@ora-gridr1 oracle]$ java -version

java version "1.6.0_21"
Java(TM) SE Runtime Environment (build 1.6.0_21-b06)
Java HotSpot(TM) 64-Bit Server VM (build 17.0-b16, mixed mode)

Then, download the version of the installer you need, then run it:

java -jar wls1032_generic.jar

When it is asked, choose the typical install

After that, next, next, next and finish.

Step 3 : Install Enterprise manager
Official installation doc: http://docs.oracle.com/cd/E11857_01/install.111/e15838/toc.htm

* stop the dbconsole created via dbca

SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

* Manually remove the following folders:
ORACLE_HOME/host_sid/ ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_host_sid

For example if ORACLE_HOME is /u01/app/oracle and hostname is test.oracle.com and sid is db1, then the above mentioned folders would be:
/u01/app/oracle/oracle/test.oracle.com_db1 /u01/app/oracle/oc4j/j2ee/OC4J_DBConsole_test.oracle.com_db1


* set some Oracle parameters:

SQL> alter system set processes = 500 scope = spfile;

System altered.

SQL>  alter system set  session_cached_cursors = 200 scope = spfile;

System altered.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3423965184 bytes
Fixed Size      2218152 bytes
Variable Size   1879050072 bytes
Database Buffers  1526726656 bytes
Redo Buffers     15970304 bytes
Database mounted.
Database opened.

* add a datafile for the undo tablespace to be more than 200m:

alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/GRID/undotbs02.dbf' size 200m autoextend on next 1m maxsize 500m;

* the install will fail trying to configure the repository. You still need to do it.
After that, you will have to clean the mess and start again, then it will work.

*Drop AQ related objects in the SYSMAN schema

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force =>TRUE);

* Drop the DB Control Repository Objects
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Now, click on retry.
The install and configuration process will keep going, you can go for a coffee.

Your grid control is now operational, have fun

Restore datafile with RMAN

Process to restore datafile with rman

Situation
Typical ORACLE error

ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/BDDPROD/PROD/users01.dbf'

Check what happened to the datafile
Example:

ls -l /BDDPROD/PROD/users01.dbf

HOW TO
Switch the online logfiles to archive logs

rman target / catalog rman/rman@rman_cat
sql 'alter system switch logfile';

Restore the missing datafile

If you get an error at the end of the recovery process :

restore datafile 4;

You need to put all the others datafiles of the tablespace offline This command will show which datafiles need to become offline :

recover tablespace users;

After that, proceed to set offline datafiles :

sql 'alter datafile 7 offline';

Next you can recover your missing datafile and set your tablespace online :

recover datafile 4;
sql 'alter tablespace users online';

Migrate from MySQL to PostgreSQL

Enterprise DB provide a tool (Migration Toolkit) that allow you to migrates database from MySQL to PostgreSQL


Before running this script, you must have a config file filled with information regarding the source and destination DB:

/opt/PostgresPlus/9.0AS/etc/toolkit.properties

SRC_DB_URL=jdbc:mysql://orig-server.com:3306/orig_db
SRC_DB_USER=root
SRC_DB_PASSWORD=*****

TARGET_DB_URL=jdbc:postgresql://dest-server.com:5432/dest_db
TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=********

Then, in the binary folder from your enterpriseDB install (/opt/PostgresPlus/9.0AS/bin), run the runMTK.sh script with the following parameter:

[root@test bin]# ./runMTK.sh -sourcedbtype mysql -targetdbtype postgres orig_db

Some information will be displayed :

Source database connectivity info...
conn =jdbc:mysql://orig-server.com:3306/orig_db
user =root
password=******
Target database connectivity info...
conn =jdbc:postgresql://dest-server.com:5432/dest_db
user =postgres
password=******
Connecting with source MySQL database server...
Connecting with target Postgres database server...
Importing mysql schema orig_db...
Creating Schema...orig_db
Creating Tables...
Creating Table: table1
Creating Table: table2
Creating Table: table3
Created 3 tables.
Loading Table Data in 8 MB batches...
Loading Table: table1 ...
[table1] Migrated 12 rows.
[table1] Table Data Load Summary: Total Time(s): 0.115 Total Rows: 12 

Loading Table: table2 ...
[table2] Migrated 4 rows.
[table2] Table Data Load Summary: Total Time(s): 0.075 Total Rows: 4 

Loading Table: table3 ...
[table3] Migrated 19 rows.
[table3] Table Data Load Summary: Total Time(s): 0.049 Total Rows: 19 

Total Size(MB): 9.765625E-4 

Data Load Summary: Total Time (sec): 0.414 Total Rows: 35 Total Size(MB): 0.0010 Creating Constraint: PRIMARY Creating Constraint: PRIMARY Creating Constraint: PRIMARY

Schema orig_db imported successfully.


Migration process completed successfully.

Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs

******************** Migration Summary ********************
Tables: 3 out of 3
Constraints: 3 out of 3

Total objects: 6
Successful count: 6
Failure count: 0

*************************************************************

DBMS_CRYPTO

synopsis:

select sys.DBMS_CRYPTO.HASH( utl_raw.cast_to_raw(v_string), v_type) from dual;

example:

select sys.DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW ('tata toto titi','AL32UTF8'), 3) from dual;
select sys.DBMS_CRYPTO.HASH( utl_raw.cast_to_raw('tata toto titi'), 3) from dual;

variables:
- v_type can be 1 (MD4), 2 (MD5), 3 (SH1)
- v_string has to be converted using utl_raw.cast_to_raw or UTL_I18N.STRING_TO_RAW.

references:
http://psoug.org/reference/dbms_crypto.html 

Encrypt in AES 128: The code bellow will encrypt tha string "tata titi toto":

DECLARE
enc_val   RAW(2000);
l_key     RAW(2000);
l_key_len NUMBER := 128/8; -- convert bits to bytes
l_mod     NUMBER := dbms_crypto.ENCRYPT_AES128
+ dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_ZERO;

BEGIN
  l_key := dbms_crypto.randombytes(l_key_len);

  enc_val := dbms_crypto.encrypt(
  utl_i18n.string_to_raw('tata titi toto', 'AL32UTF8'),
  l_mod, l_key);

  dbms_output.put_line(enc_val);
END;
/

Or just with a select:

select dbms_crypto.encrypt(
         utl_i18n.string_to_raw('tata titi toto', 'AL32UTF8'),
         6 + 256 + 4096,
         dbms_crypto.randombytes(128/8)
       )
from dual

The 6 + 256 + 4096 represents dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_ZERO

PHP with oci8 support on centos

Hi,

many people have trouble install and configuring php with oci8 support... below is a quick, easy and working way of doing it.Php and apache must be installed already.

All actions has to be done as root user.

First, install rpm for instantclient basic and SDK (previously downloaded from oracle website):

[root@test ~]# rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient11.########################################### [100%]
[root@test ~]# rpm -ivh oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient11.########################################### [100%]

next, just run pecl to install oci8 and configure php with it support:

[root@test ~]# pecl install oci8

It will ask you for the librairy folder:

Please provide the path to the ORACLE_HOME directory. Use 'instantclient,/path/to/instant/client/lib' if you're compiling with Oracle Instant Client [autodetect] : 

on a 64bits system, it should be 

instantclient,/usr/lib/oracle/11.2/client64/lib

on a 32bits system, it should be 

instantclient,/usr/lib/oracle/11.2/client/lib

if you have the following error, verify that you have the sdk installed:

checking Oracle Instant Client SDK header directory... configure: error: Oracle Instant Client SDK header files not found
ERROR: `/var/tmp/oci8/configure --with-oci8=instantclient,/usr/lib/oracle/11.2/client' failed

Then, locate your php.ini file 

[root@test ~]# find / -type f -name php.ini
/etc/php.ini

And add this line:

extension=oci8.so

The last operation is the apache service to restart.

check its status:

[root@test ~]# service httpd status
httpd (pid  3173) is running...

stop it

[root@test ~]# service httpd stop
Stopping httpd:                                            [  OK  ]

restart it:

[root@test ~]# service httpd start
Starting httpd:                                            [  OK  ]

You're done, time for you to enjoy a coffee :-)