lunes, 26 de agosto de 2013

EMKEY problems in Oracle RAC 11gR2

Oracle database 11gR2 (RAC 2 nodes)
O.S.: Redhat 5.3


1) After do a duplicate database in another host  i was unable to start Enterprise Manager Console. I got an error about EMKEY file corruption.

[oracle@fran ~]$ emctl status emkey
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
Please enter repository password:

The Em Key is not configured properly or is corrupted in the file system and does not exist in the Management Repository. To correct the problem:

1) Copy the emkey.ora file from another OMS or backup machine to the OH/sysman/config directory.
2) Configure the emkey.ora file by running "emctl config emkey -emkeyfile <emkey.ora file location>".


2) I went to ORACLE_HOME/<host>_<sid>/sysman/config  directory and look for emkey.ora, It exists but when i tried to run "emkey config emkey -emkeyfile <emkey.ora file location>" it shows:

The Em Key could not be configured. /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora is corrupted or contains an invalid Em Key.

So i replaced this emkey.ora from an older emkey.ora but it shows again the same error:

 The Em Key could not be configured. /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora is corrupted or contains an invalid Em Key.

3) My next try was configure manually but again it shows an error:

[oracle@fran config]$ emctl config emkey -repos -sysman_pwd oracle -emkeyfile /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.

The Em Key could not be configured. The Em Key was not found in Management Repository.


4) If Em Key wasn't found in Management Repository you only have one way to resolve, recreate Repository...

You can check how to recreate repository in this blog: How To Recreate Oracle Repository

How to recreate Oracle 11gR2 Repository

Oracle Database: 11.2.0.3
OS: Redhat 5.3

1) Recreate Oracle repository is an easy job, but sometimes is problematic. Try to resolve your OEM problem without recreate it unless you waste all the others options with no luck.

2) First of all, check that database is up and running, listener must be up and running too and register with database and ORACLE_SID is set.

3) You should drop SYSMAN user, and its roles and views to avoid "CONFIG: ORA-20001: SYSMAN already exists" error. To do it connect as sysdba user and run:

drop user sysman cascade;
drop public synonym setemviewusercontext;
drop role mgmt_user;
drop public synonym mgmt_target_blackouts;
drop user mgmt_view;

3) now we can drop the repository,exit of sqlplus and run:

$ emca -deconfig dbcontrol db -repos drop

*If you are in RAC, you should drop repository with this command in every node or
$ emca -deconfig dbcontrol db -repos drop -cluster (from primary node)
** You can check the logs under $ORACLE_HOME/cfgtoollogs/emca/


Be carefull, you will be question if you want to continue but with a Warning.
———————————————————————————————————————————
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————
————————————————————
Do you wish to continue? [yes(Y)/no(N)]: Y


That Warning means that only sys and syman users can connect and work (only for maintaince purpose)

SQL> select status,active_state from v$instance;
STATUS       ACTIVE_ST
————       ———
———
OPEN           QUIESCED
To avoid this situation, you can unquiesce database with:

SQL> alter system unquiesce; 

If you need to run again in quiescing mode, run again:

SQL> Alter system quiesce restricted;

In Example:
SQL> Alter system quiesce restricted;
Sistema modificado.

SQL> select status,active_state from v$instance;
STATUS       ACTIVE_ST
------------    ---------
OPEN            QUIESCED

SQL> alter system unquiesce;
Sistema modificado.

SQL> select status,active_state from v$instance;
STATUS       ACTIVE_ST
------------    ---------
OPEN            NORMAL

4) After drop repository finish, we can create the new one:

$ emca -config dbcontrol db -repos create

*In RAC will be:

$ emca -config dbcontrol db -repos create -cluster

5) Now you can start OEM executing "emctl start dbconsole"


lunes, 12 de agosto de 2013

duplicate database 11gR2 (step by step) under linux

Sometimes you will need to clone a database, for example when you have to test something with the data of production database but you can't use production database, so the easy way will be clone production database and create a test database.

In this example, I have two databases. My original database called "FRAN" and my target database called "duplicate". I have created duplicate with dbca, it is empty. I want to clone Fran into duplicated to do my tests without modify any important data of FRAN database.

1)First of all I need to do a full backup of FRAN:













2) You should know path of datafiles, logfiles, tempfiles, dbid, and a copy of pfile (for security):

- To know where are datafiles, logfiles and tempfiles execute this query:
SQLPLUS> SELECT FILE# AS "File/Grp#", NAME
FROM   V$DATAFILE
UNION
SELECT GROUP#,MEMBER
FROM   V$LOGFILE
UNION
SELECT FILE#, NAME
FROM V$TEMPFILE;

-To make a copy of FRAN's pfile:

SQLPLUS> create pfile='<path>/PFILE_ORIGINAL' from spfile;

-To know FRAN's DBID
when we run rman, appear--> Connected to target database: <database_name>(DBID XXXXXXX)

3) copy backup database in the same directory of target server, I mean if we have in FRAN's Server the bakcup in '/backup/rman/' we should have the same directory into DUPLICATE server and copy the backup there.
If it is in the same server, don't do anything.
If not you should move between servers with  scp <backup_direcotry> <user>@<SERVER_IP/NAME>:<target_diretctory>  . In example:

$ scp /backup/rman/% oracle@DUPLICATEDB:/backup/rman/

4) To duplicate, we have to put DUPLICATEDB into NOMOUNT state:

















5) Now create duplicate script, we have two choices, with exactly the same name and path of datafiles/logfiles/tempfiles, or with different name or path or both:

IMPORTANT: run the next step from SOURCE server, in that case FRAN's server.
IMPORTANT_2: databases name are stored with 8 digits, so DUPLICATE  will be DUPLICAT. I will use this alias, because if you use real name, you will get error. Also if you execute in sqlplus "select name from v$database" will show DUPLICAT, DUPLICATE doesn't show but it is the database real name. Take care with the name used, the good one is 8 digits.

 5.1) exactly the same datafiles/logfiles/tempfiles name and path


$duplicate_db> rman target system/<passwd>@FRAN auxiliary /
RMAN>
run{
duplicate target database to 'DUPLICAT' nofilenamecheck;
}


5.2) different datafiles/logfiles/tempfiles name ond path or both:

SET NEWNAME FOR DATAFILE '<ORIGINAL_PATH/DATAFILE_NAME>' TO '<NEW_PATH/NEW_DATAFILE_NAME>';

$duplicate_db> rman target system/<passwd>@FRAN auxiliary /
RMAN>
run{
  SET NEWNAME FOR DATAFILE '<ORIGINAL_PATH/ORIGINAL_DATAFILE_NAME_1>' TO '<NEW_PATH/NEW_DATAFILE_NAME_1>';
   DUPLICATE TARGET DATABASE TO ‘DUPLICAT’;
SET NEWNAME FOR DATAFILE <ORIGINAL_PATH/ORIGINAL_DATAFILE_NAME_1> TO '<NEW_PATH/NEW_DATAFILE_NAME_1>';
SET NEWNAME FOR DATAFILE <ORIGINAL_PATH/ORIGINAL_DATAFILE_NAME_1> TO '<NEW_PATH/NEW_DATAFILE_NAME_1>';
*
*
*
SET NEWNAME FOR TEMPFILE<ORIGINAL_PATH/ORIGINAL_TEMPFILE_NAME> TO '<NEW_PATH/NEW_TEMPFILE_NAME>';
SET NEWNAME FOR TEMPFILE<ORIGINAL_PATH/ORIGINAL_TEMPFILE_NAME> TO *
*
*
*
SET NEWNAME FOR LOGFILE<ORIGINAL_PATH/LOGFILE_NAME> TO '<NEW_PATH/NEW_LOGFILE_NAME>';
*
*
*
*
DUPLICATE TARGET DATABASE TO "DUPLICAT";

   }

*If you omit something, oracle will deduce that you want with the same path+name, for example it could be just one logfile or all tempfiles.
** you can add in that script: PFILE, CONTROLFILE too

6) I will choose different name and path for datafiles and tempfiles, so from Fran's server  I execute:
remember --> auxiliary will be DUPLICAT (new database) with nomount state and target is FRAN (original database) with open state.












*when duplicate finish, DUPLICAT database will be called like original database (Fran) with same DBID of Original Fran.

That's all!!