jueves, 28 de noviembre de 2013

How to enable Oracle Label Security

This example is for Oracle Database 12c already installed, but you can execute on previous database version (from 9i and above)

Open a DBCA console and select on Configure Database Options:



















Select your database and click on next



















In that page you can install OLS selecting it and clicking on next and later on finish (if you can't click on it is  like in the photo is because you have installed it when you installed database)




















Let finish the installation and open again DBCA and this time click on "Manage connection database":



















Configure Connection Database:



















Select your database:



















Select your Conecction Database:



















Enable "Configure Label Security"



















Next and Finish and you got it installed.


Now, you must unlock LBACSYS user and grant "select any dictionary" on It, so connect to sqlplus as SYS user and execute (connect to your database, NOT to connection database):

SQL> ALTER USER LBACSYS identified by LBCASYS;
SQL> ALTER USER LBACSYS account UNLOCK;
SQL> GRANT SELECT ANY DICTIONARY to LBACSYS CONTAINER=all;

and run catols.sql:
SQL>@$ORACLE_HOME/rdbms/admin/catols.sql


Verify OLS installation (LBACSYS must have objects and all must be valid):



















Now you have OLS installed and enabled. Now you can create OLS policy.



jueves, 7 de noviembre de 2013

ASM block corruption with ORA-600

The last week i got some problems with the database because the server was shutdown by an outage.

I tried to startup manually when server was operating again.

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
ORA-00205: error in identifying control file, check alert log for more info

so, let's check alertFran.log:

ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+FRA/fran/controlfile/control01.ctl'
ORA-27048: skgfifi: file header information is invalid
ORA-205 signalled during: ALTER DATABASE   MOUNT...

Checker run found 1 new persistent data failures
ORA-205 signalled during: ALTER DATABASE   MOUNT...

controlfile01.ctl is invalid so let's repair them. Two ways:

1) you have multiplixed controlfile:

$export ORACLE_SID=+ASM
$export ORACLE_HOME=/u01/app/grid
$sqlplus / as sysasm
SQL> alter database nomount;
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
SQL> exit
$asmcmd
ASMCMD> rm -rf '<path_invalid_controlfile>;
ASMCMD> cp '<path_valid_controlfile>' '<path_invalid_controlfile>;

2) You haven't multiplexed controlfile, create manually one:
SQL> CREATE CONTROLFILE
     SET DATABASE Fran
     LOGFILE GROUP 1 ('+FRA/fran/onlinelog/group_1.A',
                      '+FRAN_DATA/fran/onlinelog/group_1.B'),
             GROUP 2 ('+FRA/fran/onlinelog/group_2.A',
                      '+FRAN_DATA/fran/onlinelog/group_2.B'),
             GROUP 3 ('+FRA/fran/onlinelog/group_3.A',
                      '+FRAN_DATA/fran/onlinelog/group_3.B'),
             GROUP 4 ('+FRA/fran/onlinelog/group_4.A'),
                      '+FRAN_DATA/fran/onlinelog/group_4.B')
     RESETLOGS                
     DATAFILE '+FRAN_DATA/fran/datafiles/system01.dbf' SIZE 150M,
              '+FRAN_DATA/fran/datafiles/rbs01.dbs' SIZE 512M,
              '+FRAN_DATA/fran/datafiles/temp01.dbs' SIZE 2G,
              '+FRAN_DATA/fran/datafiles/sysaux01.dbf' SIZE 30M,
              '+FRAN_DATA/fran/datafiles/user01.dbf' SIZE 100M,
              '+FRAN_DATA/fran/datafiles/test01.dbf' SIZE 512,
              '+FRAN_DATA/fran/datafiles/fran01.dbf' SIZE 1G;

**if you modified or create controlfile remember that you must modify the new path at pfile. if you create pfile from spfile, open pfile and you see something like this:
**control_files = ('+FRAN_DATA/fran/controlfile/control01.ctl','+FRA/fran/controlfile/control02.ctl');
**modify manually with vi or textpad (depends OS) changing its paths
**control_files = ('new_path/control01.ctl','new_path/control02.ctl');
**SQL> STARTUP PFILE='path_pfile';
**SQL> create spfile from pfile;

we can now try to open database.

$sqlplus / as sysdba
SQL> ALTER DATABASE OPEN;


**It should open without problem, but like my issue is different i got a new error

ORA-00338: log 3 of thread 2 is more recent than control file
ORA-00312: online log 3 thread 2: '+FRAN_DATA/Fran/onlinelog/group_3.A'
ORA-00338: log 3 of thread 2 is more recent than control file
ORA-00312: online log 3 thread 2: '+FRA/Fran/onlinelog/group_3.B'

the solution was restore/recover database from last backup until 10 minutes before the outage:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RUN{
  ALLOCATE CHANNEL c1 FORMAT '<path_of_backup>';
  set until time "to_date('2013-11-01:11:27:00', 'yyyy-mm-dd:hh24:mi:ss')";
  restore database;
  recover database;
    release channel c1;
 }

after restore recover finished succesfully i got a new error when we opened database (ALTER DATABASE OPEN RESETLOGS)

ORA-600 : internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], []

this error are serious, we searched by this arguments at ORA-600 aplication of MOS (My Oracle Support) but no luck....

searching at alert.log from asm instance we found the error:

ORA-600 : internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], []
ORA-15032: not all alterations performed
ORA-15130: diskgroup "FRA" is being dismounted
ORA-15066: offlining disk "FRA_0004" in group
"FRA" may result in a data loss
ORA-15196: invalid ASM block header [kfc.c:26076] [hard_kfbh] [11223]
[2147483648] [34 != 130]
ORA-15196: invalid ASM block header [kfc.c:26076] [hard_kfbh] [11223]
[2147483648] [34 != 130]

It is an ASM corruption block!! Well, breathe, everything has a solution in that life...


SQL> SELECT count(*) from v$database_block_corruption;

 COUNT(*)
---------
     217    <-- confirmed, 217 blocks corrupted.

solution--> move everything of this asm diskgroup to another asm diskgroup or copy them at OS level. If you have enterprise edition licence you can easily repair with "RMAN> blockrecover corruption list;" (you must need a valid backup, of course), like we have standard edition we haven't any other option....

At this ASM diskgroup we have logfiles, controlfile, backups and archivelogs. We aren't worry about backups and archivelogs because they are copying in other server every 1 hour. So we just moved logfiles and controlfile:

$sqlplus / as sysasm
*REMEMBER: I had database on mount state. So we can do it without any problem.

SQL> ALTER SYSTEM SET control_files = ('+FRAN_DATA/fran/controlfile/control01.ctl','/backup/controlfile/control02.ctl') scope=spfile;

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+FRA/fran/onlinelog/group_1.A
+FRA/fran/onlinelog/group_2.A
+FRAN_DATA/fran/onlinelog/group_2.B
+FRA/fran/onlinelog/group_3.A
+FRAN_DATA/fran/onlinelog/group_3.B
+FRA/fran/onlinelog/group_4.A
+FRAN_DATA/fran/onlinelog/group_4.B
+FRAN_DATA/fran/onlinelog/group_1.B

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_1.A', '+FRAN_DATA/fran/onlinelog/group_1.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_1NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_1NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_2.A', '+FRAN_DATA/fran/onlinelog/group_2.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_2NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_2NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_3.A', '+FRAN_DATA/fran/onlinelog/group_3.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_3NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_3NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_4.A', '+FRAN_DATA/fran/onlinelog/group_4.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_4NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_4NEW_B';

Database altered.


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+FRAN_DATA/asm/onlinelog/group_1new_A
+FRAN_DATA/asm/onlinelog/group_2new_A
+FRAN_DATA/asm/onlinelog/group_2new_B
+FRAN_DATA/asm/onlinelog/group_3new_A
+FRAN_DATA/asm/onlinelog/group_3new_B
+FRAN_DATA/asm/onlinelog/group_4new_A
+FRAN_DATA/asm/onlinelog/group_4new_B
+FRAN_DATA/asm/onlinelog/group_1new_B

8 rows selected.

SQL> alter database open resetlogs
2 ;

Database altered.

WOW, opened!! good work!!

Now, i recommend drop and recreate diskgroup +FRA, move again logfiles and controlfile to +FRA and forgot all about corruption blocks.... for the moment....

lunes, 7 de octubre de 2013

Complete Deinstall/Remove Oracle 11gR2

Imagine that you have an Oracle database 11gR2 installed. That database is working but you don't need it anymore and need that space.

That manual is made on windows 7 64 bits but the procedure is simililar on Unix so you can use this guide.

Oracle provides an useful tool called "deinstall tool" for standalone, RAC, ASM Oracle installations. You can find Deinstall Tool under ORACLE_HOME\deinstall\If you don't have it you can go to this link and download it:
ORACLE DOWNLOADS --> Under Oracle Database 11gR2, click "See all" and look for "Oracle De-Install Utility".

1) Deinstall tool will stop Oracle software, and removes Oracle software and configuration files on the operationg system for a specifil Oracle Home. If you are running an Oracle grid infraestructure for a standalon server, then the deinstaller prompts you to run roothas.pl script to deconfigure Oracle Restart.

2)Go to your Oracle_home --> deinstall
or Oracle_home --> grid --> deinstall if you are under grid infraestructure and execute deinstall.bat.

deinstall tool use the following sintax (if you want execute manually):

deinstall.bat -home complete path of Oracle home [-silent] [-checkonly] [-local]
[-cleanupOBase] [-paramfile complete path of input parameter property file] [-params name1=value name2=value . . .]
[-o complete path of directory for saving files] [-help]

Just answer the questions and wait until finish.













miércoles, 25 de septiembre de 2013

Estimate Time To Complete RMAN Execution (backup, recover, restore)

To estimate how many time any rman operation will take, you can check it with this script:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK;

For example if we are doing a full backup of database we can predict how many time we will take (approximately) :













Same will happen with restore/recover.

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

martes, 9 de julio de 2013

How to resolve ORA-00392 ORA-00312

I did a restore/recover from my original RAC to another RAC in another server. When i created the ASM diskgroups, I change one of their original names, exactly +FRAN_DATA for +DATA.
The restore/recover was ok but when i tried to open database with "resetlogs" option i got ORA-00392 and ORA-00312.
The solution is easy, you should clear the busy onlinelog and later change one by one all the redologs name until you can open database with resetlogs option. Something ike this:


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR en línea 1:
ORA-00392: el log 27 del thread 2 se está limpiando, operación no permitida
ORA-00312: log online 27 thread 2: '+DATA/fran/onlinelog/group_27'



SQL> alter database clear logfile group 27;

Base de datos modificada.


SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_20'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_20' to '+DATA/fran/onlinelog/group_20';


Base de datos modificada.


SQL>  alter database open resetlogs;

 alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_21'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_21' to '+DATA/fran/onlinelog/group_21';


Base de datos modificada.


SQL> alter database open resetlogs

  2  ;
alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_22'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL>  alter database rename file '+FRAN_DATA/fran/onlinelog/group_22' to '+DATA/fran/onlinelog/group_22';


Base de datos modificada.


SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_23'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_23' to '+DATA/fran/onlinelog/group_23';


Base de datos modificada.


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_24' to '+DATA/fran/onlinelog/group_24';


Base de datos modificada.


SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_25'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_25' to '+DATA/fran/onlinelog/group_25';


Base de datos modificada.


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_26' to '+DATA/fran/onlinelog/group_26';


Base de datos modificada.


SQL> alter database open resetlogs;


Base de datos modificada.


:)

viernes, 21 de junio de 2013

Remove RAC (11.2.0.3) + ASM under RHEL 5

In that example, i am working with  RAC (2 nodes, Oracle database 11.2.0.3) + ASM under RHEL 5.3


1) Search in /etc/oratab the instances (in all nodes):














2) Start DBCA, select Oracle Real Application Clusters Database, select Delete a database, and select the database that you want to delete. Repeat this step to delete all databases.

3) Run asmca to drop diskgroups





Select the diskgroup, and right click on it, later “drop”



Accept:



*do the same with all diskgroups.

======================================================================

PROBLEMS DROPPING ASM DISKS

======================================================================

Depends the configuration of the asm disks, probably you can't drop all disks. :

OPTION 1: 
You have to create a pfile from spfile and startup with pfile:











Connect as “sys / as sysasm” and create pfile from current spfile (do it in both nodes):










Shutdown database and restart with the pfile created, and try to drop the diskgroup again:

OPTION 2:

SQL> alter diskgroup XXX force dismount;

*in all nodes except in one, where you are going to run "drop diskgroup" have to be mounted.

SQL> drop diskgroup XXX force including contents;

OPTION 3:

*only if It is a OCFS diskgroup
In asmca window, go to “ASM Cluster File Systems” and remember the path of “Active Mount Point”



















As root:






Connect “as sysasm” again:




















Only in one node the diskgroup must be “mounted” (where you execute “drop diskgroup"), in the others should be “dismounted”:

In node 1:








In node 2:



If you have OCR disks (VOTING DISKS) in ASM, you must drop it like follow as “ROOT”:



















If you haven’t OCR (VOTING DISK) under ASM, do the following as “ROOT”:







*change <path> for your path, in that example i can’t because my Voting disk are under ASM.

**to remove ASM entry for OCR you should stop asm and remove it:

$srvctl remove asm

4) Go to /etc/init.d and run “oracleasm listdisks” to list all your asm disks and later use “oracleasm deletedisk XXX” on all.


























5) deconfigure asm configuration, starting with listener and clusterware resources as “ORACLE” user:










6) To uninstall grid infrastructure, run "deinstall" in all nodes (it is under your GRID_HOME), you can do it in parallel:






*In all questions, you have to answer with default option or yes. Almost at the end, you also have to execute some scripts with root user to continue.

7) To uninstall oracle database infrastructure, run "deinstall" in all nodes (it’s under your ORACLE_HOME), you can do it in parallel:




miércoles, 29 de mayo de 2013

Differences between PSU , CPU and SPU and how to install

======================================================================
WHAT'S THE MEANING OF ...
======================================================================

Critical Patch Update (CPU) --> Release of security fixes each quarter instead the cumulative database security patch for the quarter.

Patch Set Updates (PSU) --> Cumulative patches that include both the security fixes and priority fixes.  They are "version upgrades" modifying the las number of version (11.2.0.1.1, 11.2.0.1.2,...).

Security Patch Update (SPU) --> It is the new terminology of CPU (From October 2012) . SPU's can't be applied if any PSU's have been applied, unless your database is upgraded with a new base version.

======================================================================
How To Install CPU?

======================================================================



In that example we are going to install Patch 12419278 (CPU) and Patch 12419378 (PSU), Updates released on July 19, 2011. To download them we have to go to MOS (My Oracle Support) and "search" them, select your OS version (In that case, Linux x86) and download.

1) Unzip both Patches



2) We are goint to Install first CPU (12419278), so first for all open a document called "README.html", it will open a browser with the information, bugs fixed, known issues,steps to install it, ... for this CPU. I recommend read it before do something.
3) Check Opatch version, it would be recommend the latest, or at least 11.2 to continue. You can download the last version at Last Version Opatch.

*For install the last version you have to download the last version --> copy or move the contents of your old $ORACLE_HOME/Opatch directory to another directory --> delete the contents of $ORACLE_HOME/Opatch (don't delete the directory) --> unzip last version Opatch into $ORACLE_HOME/Opatch

- To check Opatch version, Open a termianl go to $ORACLE_HOME/Opatch and execute "opatch lsinventory"



4) Shutdown all instances and listeners associated with Oracle_home that we are updating.



5) Be sure that executables "make", "ar", "ld" and "nm" are under $PATH. Usually this executables are under "/usr/ccs/bin". If you haven't it just run "export PATH=$PATH:/usr/ccs/bin

6) Go to the unziped Path directory and execute Opath utility:






7) When finish, open a terminal and connect to sqlplus as sysdba to startup database and execute catbundle.sql script:



















8) The last step is check for any error at .log generated by catbundle script. At the end of the script it says where to find that log, It should be at $ORACLE_HOME/cfgtoollogs/catbundle. If exists any error, you should read "section 4: known issues" from README document to resolve it.

======================================================================
How To Install PSU?
======================================================================


1) Like we just do the pre-install steps, we can run directly the command to execute the PSU. If you didn't install CPU you have to do it from the beggining, execute steps 1,2,3,4,5 from "How To Install CPU"

*Execute the next command to verify that you haven't any conflicting patch installed:







After verify it, apply the PSU, go to the unzipped directory and execute "$ORACLE_HOME/Opatch/opatch apply"






2) Continue with step 7 and 8 of "How To Istalling CPU"

martes, 30 de abril de 2013

Install ASM on existing Oracle database 11gR2


======================================================================
DOWNLOADS


======================================================================
- ASMLIB (Step 4- Prerequisites)
- Grid --> Grid_download

======================================================================
PRE-REQUISITES

======================================================================

1) To see our partitions we have to open a terminal, connect as root user and write "cat /proc/partitions"



















I have 3 partitions, two of 2Gb and one of 3Gb called hdb,hdc and hdd (hda is the primary master, don't do anything!!)



2) To configure the partitions, we have to use fdisk utility. Like partitions are under /dev/sdX, we have to run fdisk /dev/sdX, one time for each partition. In that step, we will write "n","p" "1" (number 1) and "w" (in that order):





3) If we list partitions again we will show the result:

















4) We have to download ASM libraries and drivers. The easy way is using yum utility. Open a terminal with root user and execute:





5) Now we have to configure the kernel module  for ASM executing "/etc/init.d/oracleasm configure" with root. The answers this time will be "oracle", "oinstall", "y" and  "y"















6) Create disks for ASM
/etc/init.d/oracleasm createdisk ASM1 /dev/sdb1
/etc/init.d/oracleasm createdisk ASM2 /dev/sdc1
/etc/init.d/oracleasm createdisk ASM3 /dev/sdd1










7) run scandisk and listdisk to be sure that we have all asmdisk available.
/etc/init.d/oracleasm scandisks 
/etc/init.d/oracleasm listdisks 








======================================================================
INSTALLATION

======================================================================

1) with user oracle, "Unzip" grid software:




2) execute /grid/runInstaller (grid directory is created when we unzipped grid software) to start grid configuration window











3) choose the second option and "Next"




















4) choose your language and "Next"




















5) In that window we only select 2 asmdisk for DATA (the other will be for Flash_recovery_area, we will do it later), it's important choose Extern Redundancy
(you can select the path of your DISKs in "Change discovery path", by default "/dev/oracleasm/disks"):




















6) Time to choose our passwords, different password for accounts or the same password for all the accounts, like you want




















7) Select all groups as dba and "Next" (will appear a warning, but ignore it)





































8) Select the path where grid will be installed




















9) In that step, the installation check that all will be ok, if not you have to check the description of the error and solve it, when all will be ok, "next"




















10) Verify the information showed and "Finish"




















11) wait untill installation finish




















12) Almost at the end of the installation will appear a new window, we must run root.sh with root user, when finish click on accept, and the installation will continue.





















Set /usr/local/bin like teh full pathname of the local bin directory.


















*like we had installed an oracle database before, you must overwrite dbhome, oraenv and coraenv


13) when the installation finish, close the window.





















14) We have already installed grid infraestructure, now we are going to add FLASH_RECOVERY_AREA to the other free asmdisk (step 5). We need to run asmca, to do it we must set the correct ORACLE_HOME and ORACLE_SID. In this case ORACLE_SID will be +ASM and ORACLE_HOME is the grid path. To go to the easy way, open a terminal and write "oraenv", you will be question for the new ORACLE_SID, write "+ASM" and the ORACLE_HOME automatically change too:



















15) A new window appears, choose create:

















16) Now choose the name, select "extern" and select the disk. "Next"



















17) We have now both asmdisks: