UNIX tricks and treats

Aller au contenu | Aller au menu | Aller à la recherche

jeudi 27 décembre 2012

Querying Oracle instance alertlog via SQL

As of Oracle 11g, the contents of Oracle alert log can be queried directly via a fixed table X$DBGALERTEXT.

desc X$DBGALERTEXT
 ADDR                                               RAW(8)
INDX NUMBER
INST_ID NUMBER
ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(64)
COMPONENT_ID VARCHAR2(64)
HOST_ID VARCHAR2(64)
HOST_ADDRESS VARCHAR2(46)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(64)
MESSAGE_GROUP VARCHAR2(64)
CLIENT_ID VARCHAR2(64)
MODULE_ID VARCHAR2(64)
PROCESS_ID VARCHAR2(32)
THREAD_ID VARCHAR2(64)
USER_ID VARCHAR2(64)
INSTANCE_ID VARCHAR2(64)
DETAILED_LOCATION VARCHAR2(160)
PROBLEM_KEY VARCHAR2(64)
UPSTREAM_COMP_ID VARCHAR2(100)
DOWNSTREAM_COMP_ID VARCHAR2(100)
EXECUTION_CONTEXT_ID VARCHAR2(100)
EXECUTION_CONTEXT_SEQUENCE NUMBER
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
VERSION NUMBER
MESSAGE_TEXT VARCHAR2(2048)
MESSAGE_ARGUMENTS VARCHAR2(128)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128)
SUPPLEMENTAL_DETAILS VARCHAR2(128)
PARTITION NUMBER
RECORD_ID NUMBER


The PROBLEM_KEY column gives the ORA- error code.

However, being a fixed table, it means that the table is only accessible by SYS, and not viewable in SYS's catalog.

A quick and dirty fix can be to create a V$ public synonym for the view, and grant SELECT to the view to whoever has to access it.

CREATE VIEW SYS.V_$ALERT_LOG
AS SELECT * FROM x$dbgalertext;

CREATE OR REPLACE PUBLIC SYNONYM V$ALERT_LOG FOR SYS.V_$ALERT_LOG;

GRANT SELECT ON SYS.V_$ALERT_LOG TO MYDBUSER;


You can then query the view for interesting details.

For example:

SELECT * FROM V$ALERT_LOG WHERE MESSAGE_LEVEL <> 16 ORDER BY ORIGINATING_TIMESTAMP DESC;


Happy computing

Drop me a line and hang out on the sidebar links if this note has been useful to you.

Nixman

vendredi 31 août 2012

Oracle Transparent Data Encryption

But:

Oracle Transparent Data Encryption permet de chiffrer et déchiffrer à la volée les données en base. Les données sont chiffrées au niveau des datafiles. Seuls les utilisateurs authentifiés en base ont accès aux données en clair. Principe:

Le chiffrage repose sur l'utilisation d'un portefeuille (wallet) de mot(s) de passe. Contrairement à l'utilisation de packages comme DBMS_CRYPTO, cette solution de cryptage est transparente pour l'utilisateur authentifié, et ne nécessite donc pas de modification du code client, ce qui constitue un avantage financier non négligeable. Il est possible de chiffrer des colonnes spécifiques d'une table, ou bien des tablespaces entiers.

Mise en place:

Création du wallet:

Le wallet est positionné par défaut dans le répertoire $ORA_ADM/$SID/wallet.

Il suffit alors de créer le répertoire:

mkdir $ORA_ADM/$SID/wallet

Il est également possible de spécifier le lieu de stockage du wallet dans le fichier sqlnet.ora, de la façon suivante:

ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/MON_REPERTOIRE_A_WALLETS)))

Il faut ensuite créer le mot de passe de chiffrage:

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "M0n#m0T$dE_Pa55E";

Puis ouvrir le wallet sur chacune des instances de la base:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "M0n#m0T$dE_Pa55E";

/! L'ouverture du wallet doit se faire à chaque redémarrage de la base, ainsi que sur l'ensemble des instances d'un RAC.

Cas particulier du wallet sur un RAC:

Le wallet doit être préférentiellement stocké sur un filesystem partagé comme ACFS (ce qui est notre cas). De plus, il faut initialiser les paramètres système et BDD ORACLE_UNQNAME.

ex:

export ORACLE_UNQNAME=MONINST
srvctl setenv database -d MONINST -T "ORACLE_UNQNAME=MONINST"

De même, il faut indiquer le lieu de stockage du wallet en utilisant la variable $ORACLE_UNQNAME dans sqlnet.ora du user oracle, puis redémarrer le listener (avec le user grid).

ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/acfsmounts/orabase1/admin/$ORACLE_UNQNAME/wallet/)))

Sinon, le wallet ne peut être ouvert que sur une seule instance à la fois.

Vérification:

select * from gv$encryption_wallet;
INST_ID WRL_TYPE             WRL_PARAMETER                                                          STATUS
---------- -------------------- ---------------------------------------------------------------------- ------------------
1 file                 /u01/app/oracle/acfsmounts/orabase1/admin/$ORACLE_UNQNAME/wallet/      OPEN
2 file                 /u01/app/oracle/acfsmounts/orabase1/admin/$ORACLE_UNQNAME/wallet/      OPEN

Les deux wallets doivent être en état "OPEN"

Chiffrage d'un tablespace:

Le chiffrage d'un tablespace se fait à la création de celui-ci. Il n'est (pour l'instant?) pas possible de chiffrer un tablespace existant.

create tablespace ts_encrypted datafile size 10M autoextend on next 10M encryption using 'AES256' default storage(ENCRYPT);

Il suffit alors de déplacer les données vers ce nouveau tablespace chiffré. L'avantage d'un tablespace chiffré par rapoort au chiffrage de colonnes d'une table est essentiellement dans l'absence de limitations sur l'utilisation des eindexes dans le cas de l'opérateur LIKE, et dans le chiffrage simple des blobs. Le désavantage essentiel est l'impossibilité de l'effectuer en ligne.

Chiffrage d'une colonne d'une table:

On peut chiffrer les colonnes d'une table à l'aide d'une simple commande ALTER TABLE.

ex:

alter table SCOTT.EMPLOYEE modify (ID RAW(32) ENCRYPT);

Dans le cas où la colonne est indexée, il faut désactiver l'option SALT:

ex:

alter table SCOTT.EMPLOYEE modify (DATE_EMBAUCHE DATE ENCRYPT NO SALT);

L'encodage par défaut est AES192 sous Oracle 11gR2.

A noter que les tables ne contenant aucune colonne cryptée seront toujours accessibles, même si le wallet n'est pas ouvert.
Une table contenant une seule colonne chiffrée sera totalement inaccessible si le wallet n'est pas ouvert.

Retour arrière:

Le retour arrière se fait toujours à l'aide d'une simple commande ALTER TABLE.

ex:

alter table SCOTT.EMPLOYEE modify (DATE_EMBAUCHE DECRYPT);

Chiffrage des colonnes LOB d'une table:

Il faut chiffrer les lobsegments en utilisant l'option de stockage SECUREFILE lors de la création du LOB.

Il est possible de chiffrer une colonne LOB en ligne en utilisant le package DBMS_REDIFINITION.

Chiffrage des backups Datapump:

Il est possible d'exporter les données sous forme non chiffrée, avec les options standard de DataPump L'alerte suivante est alors levée dans les logs de l'export:

ORA-39173: Des données cryptées ont été stockées décryptées dans un ensemble de fichiers de vidage.

Dans ce cas, il est toujours possible de lire des données en clair dans le fichier de dump, à l'aide de l'utilitaire strings par exemple.

Afin d'exporter des données chiffrées, il faut ajouter l'option encryption_password à la commande DataPump

ex:

expdp TOTO/TATA directory=EXP_DIR schemas=TITI encryption_password=M0n#m0T$dE_Pa55E dumpfile=dump_titi.dmp logfile=dump_titi.log

De même, il faut spécifier le mot de passe de chiffrage lors de l'import.

ex:

impdp TOTO/TATA directory=EXP_DIR encryption_password=M0n#m0T$dE_Pa55E dumpfile=dump_titi.dmp logfile=impdp_titi.log

Références:

http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_securing_data.htm#CHDCGGBH
http://www.oracle.com/technetwork/database/focus-areas/security/twp-transparent-data-encryption-bes-130696.pdf
http://docs.oracle.com/cd/B28359_01/network.111/b28530/asotrans.htm
http://docs.oracle.com/cd/B19306_01/network.102/b14268/asotrans.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_smart.htm#BABDIEGE
http://oracleflash.com/26/Oracle-10g-Transparent-Data-Encryption-examples.html

jeudi 8 avril 2010

Multiplexer les redo logs Oracle sous ASM

SQL> select group#, member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 +FRA/MYDB/onlinelog/group_1.257.715620719
         2 +FRA/MYDB/onlinelog/group_2.258.715620719
         3 +FRA/MYDB/onlinelog/group_3.259.715620719

SQL>  select group#, status, bytes/1024  from v$log;

    GROUP# STATUS           BYTES/1024
---------- ---------------- ----------
         1 ACTIVE                51200
         2 CURRENT               51200
         3 ACTIVE                51200


SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;


SQL> alter system checkpoint global;


SQL>  select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE


Prendre un des groupes en état INACTIVE et le supprimer, puis le recréer avec deux membres.

Dans cet exemple, on suppose que l'instance est en mode OMF.

SQL> alter database drop logfile group 3;
(si le groupe 3 est en état INACTIVE)

SQL> select group#, status, bytes/1024  from v$log;

    GROUP# STATUS           BYTES/1024
---------- ---------------- ----------
         1 CURRENT               51200
         2 INACTIVE              51200

SQL> alter database add logfile group 3 ('+DATA','+FRA') size 51200K;

SQL> alter system switch logfile;

On vérifie que le groupe 3 possède désormais deux membres

SQL> select group#, status, members, bytes/1024  from v$log;

    GROUP# STATUS              MEMBERS BYTES/1024
---------- ---------------- ---------- ----------
         1 ACTIVE                    1      51200
         2 INACTIVE                  1      51200
         3 CURRENT                   2      51200


SQL> select group#, member from v$logfile order by 1

    GROUP# MEMBER
---------- --------------------------------------------------
         1 +FRA/MYDB/onlinelog/group_1.257.715710709
         1 +DATA/MYDB/onlinelog/group_1.285.715710709
         2 +FRA/MYDB/onlinelog/group_2.258.715710073
         2 +DATA/MYDB/onlinelog/group_2.284.715710073
         3 +FRA/MYDB/onlinelog/group_3.259.715709881
         3 +DATA/MYDB/onlinelog/group_3.282.715709881

Et faire ainsi de suite pour les trois groupes... Ici, on peut dropper et recréer le groupe 2 car il est inactif

Attention à ne pas dropper les trois groupes de logs en même temps ou de dropper un logfile group actif!


N'hésitez pas à un message si cet article vous a été utile.

Nixman

mercredi 7 avril 2010

Multiplexing an Oracle controlfile in ASM


1) Check current controlfile's name and restart the database NOMOUNT

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------
control_files                        string      +FRA/MYDB/controlfile/current.256.715620719


SQL> shutdown immediate

SQL> startup nomount


2) Copy/restore current controlfile through RMAN

$ rman target /

RMAN> restore controlfile to '+DATA' from '+FRA/MYDB/controlfile/current.256.715620719';

Starting restore at 07-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 07-APR-10

3) Find out the name of the new controlfile through asmcmd

ASMCMD> ls -lsa +DATA/MYDB/controlfile

Type         Redund  Striped  Time             Sys  Block_Size  Blocks    Bytes     Space  Name
CONTROLFILE  UNPROT  FINE     APR 07 14:00:00  Y         16384     595  9748480  16777216  none => current.283.715704921


4) Modify your spfile to take into account the new controlfile

SQL> alter system set control_files='+DATA/MYDB/controlfiles/current.283.715704921','+FRA/MYDB/controlfile/current.256.715620719' scope=spfile;


5) Restart the database and check

SQL> shutdown immediate

SQL> startup

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/MYDB/controlfiles/cu
                                                 rrent.283.715704921, +FRA/mmtw
                                                 ebdv/controlfile/current.256.7
                                                 15620719



Leave me a line if this note has been useful to you.

Happy computing


Nixman

Multiplexer le fichier de contrôle Oracle sous ASM


1) Vérifier le nom du fichier de contrôle actuel:

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------
control_files                        string      +FRA/MYDB/controlfile/current.256.715620719


SQL> shutdown immediate

SQL> startup nomount


2) Effetuer la copie du fichier de contrôle via rman

$ rman target /

RMAN> restore controlfile to '+DATA' from '+FRA/MYDB/controlfile/current.256.715620719';

Starting restore at 07-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 07-APR-10


3) Retrouver le nom du nouveau fichier de contrôle via asmcmd:

ASMCMD> ls -lsa +DATA/MYDB/controlfile

Type         Redund  Striped  Time             Sys  Block_Size  Blocks    Bytes     Space  Name
CONTROLFILE  UNPROT  FINE     APR 07 14:00:00  Y         16384     595  9748480  16777216  none => current.283.715704921


4) Modifier les paramètres d'init pour prendre en compte le nouveau fichier de contrôle:

SQL> alter system set control_files='+DATA/MYDB/controlfiles/current.283.715704921','+FRA/MYDB/controlfile/current.256.715620719' scope=spfile;


5) Redémarrer la base et vérifier:

SQL> shutdown immediate

SQL> startup

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/MYDB/controlfiles/cu
                                                 rrent.283.715704921, +FRA/mmtw
                                                 ebdv/controlfile/current.256.7
                                                 15620719



Laissez-moi une note si cet article vous a été utile.

Bonne journée.


Nixman

lundi 22 mars 2010

Purge backups OCR non fonctionnel sous Cluster Oracle 11gR2

Dans un cluster Oracle 11gR2, la purge des backups automatiques de l'OCR ne fonctionne pas (depuis version 10.2.4) si les  fichiers *.ocr situés sous $ORA_CRS_HOME/cdata/<cluster_name> (ex:/u01/app/11.2.0/grid/cdata/inbdor0809-rac/) ont les mauvaises permissions. De ce fait, les fichiers *.ocr avec des noms aléatoires ne sont pas renoimmés en backupXX.ocr et ne sont pas purgés, saturant la partition.

ocrconfig -showbackup montre bien la bonne date de sauvegarde, mais les fichiers backupXX.ocr ont une date bien antérieure.

Un grand nombre de fichiers XXXXXXXXX.ocr existe sous $ORA_CRS_HOME/cdata/<cluster_name>.


# ls -rtl $ORA_CRS_HOME/cdata/<cluster_name>

-rw------- 1 grid oinstall 7081984 jan 18 22:24 day.ocr
-rw------- 1 grid oinstall 7081984 jan 19 02:24 day_.ocr
-rw------- 1 grid oinstall 7081984 jan 19 06:24 backup02.ocr
-rw------- 1 grid oinstall 7081984 jan 19 10:24 backup01.ocr
-rw------- 1 grid oinstall 7081984 jan 19 14:24 backup00.ocr

...

-rw------- 1 root root 7413760 mar 19 22:06 40778839.ocr
-rw------- 1 root root 7413760 mar 20 02:06 26378630.ocr
-rw------- 1 root root 7413760 mar 20 06:06 11332652.ocr
-rw------- 1 root root 7413760 mar 20 10:06 35215677.ocr
-rw------- 1 root root 7413760 mar 20 14:06 41977816.ocr
-rw------- 1 root root 7413760 mar 20 18:06 18335174.ocr
-rw------- 1 root root 7413760 mar 20 22:06 90743999.ocr
-rw------- 1 root root 7413760 mar 21 02:06 20182690.ocr
-rw------- 1 root root 7413760 mar 21 06:06 28125568.ocr
-rw------- 1 root root 7413760 mar 21 10:06 20121708.ocr
-rw------- 1 root root 7413760 mar 21 14:06 34916120.ocr
-rw------- 1 root root 7413760 mar 21 18:06 24068304.ocr



Solution:

chown root:root $ORA_CRS_HOME/cdata/<cluster_name>/*.ocr

Les fichiers XXXXXXXX.ocr peuvent être ensuite supprimés.

Référence: Note Metalink 741271.1

mardi 17 novembre 2009

STREAMS alter table move bug


A very annoying STREAMS bug, which should be corrected in the 11GR2 release, is the failure of STREAMS to keep up with an alter table move tablespace command.

If your table contains a LOB, and want to do some reorg through a move, then you will very likely hit the bug and receive the following error message in your alert.log:

ORA-26744: STREAMS capture process "STREAMS_CAPTURE" does not support "OWNER"."TABLE_NAME" because of the following reason:
ORA-26773: Invalid data type for column "malformed redo"


No workarounds exist, except excluding your table from your STREAMS propagation.

Reimporting the table with a new flashback SCN won't work. You have to reimplement the whole STREAMS process to get back on your feet.

Metalink réference:Bug 5623403.

vendredi 9 octobre 2009

Configuration particulière de STREAMS pour RAC

Dans le cadre d'un RAC, si l'on a configuré la propagation via un dblink, il faut indiquer un des noeuds comme propriétaire par défaut de la queue capture et apply de STREAMS. Sinon, on peut se retrouver confroté à une erreur ORA-25315 de façon aléatoire.

Le mieux, sur des configurations 10.2 ou supérieurs, étant de positionner le paramètre queue_to_queue à TRUE lors de la mise en place de la propagation avec DBMS_PROPAGATION_ADM.CREATE_PROPAGATION. On ne passera alors plus par le dblink. Il est impossible de modifier le paramètre en cours de route.

Réf: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_prop_a.htm

Retrouver les noms des queues STREAMS

set lines 150

SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, t.OWNER_INSTANCE

  • FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
  • WHERE t.OBJECT_TYPE = 'SYS.ANYDATA'
  • AND q.QUEUE_TABLE = t.QUEUE_TABLE
  • AND q.OWNER = t.OWNER;

Indiquer un propriétaire par défaut et un propriétaire secondaire

DBMS_AQADM.ALTER_QUEUE_TABLE (

  • queue_table IN VARCHAR2,
  • comment IN VARCHAR2 DEFAULT NULL,
  • primary_instance IN BINARY_INTEGER DEFAULT NULL,
  • secondary_instance IN BINARY_INTEGER DEFAULT NULL);

ex:

BEGIN

  • DBMS_AQADM.ALTER_QUEUE_TABLE(
    • queue_table => 'MON_QUEUE_TABLE_APPLY',

    • primary_instance => 1,

    • secondary_instance => 2);

END; /

BEGIN

  • DBMS_AQADM.ALTER_QUEUE_TABLE(
    • queue_table => 'MON_QUEUE_TABLE_CAPTURE',

    • primary_instance => 1,

    • secondary_instance => 2);

END; /

BEGIN

  • DBMS_AQADM.ALTER_QUEUE_TABLE(
    • queue_table => 'SCHEDULER$_JOBQTAB',

    • primary_instance => 1,

    • secondary_instance => 2);

END; /

lundi 21 septembre 2009

Installing ocfs2 filesystem on RHEL 5.3


Until Oracle finally releases its much awaited-for Universal FileSystem, the only way to install grid infrastructure on shared storage is still ocfs2, which you may find useful as a regular cluster filesystem, too.

Download the rpms for Red Hat from
http://oss.oracle.com/projects/ocfs2/

For a 64-bit platform, you will need these ones:

( Do a uname -r to check which is your platform)

ocfs2-2.6.18-128.el5-1.4.2-1.el5.x86_64.rpm
ocfs2-tools-1.4.2-1.el5.x86_64.rpm
ocfs2console-1.4.2-1.el5.x86_64.rpm

# rpm -Uvh ocfs2-tools-1.4.2-1.el5.x86_64.rpm ocfs2-2.6.18-128.el5-1.4.2-1.el5.x86_64.rpm ocfs2console-1.4.2-1.el5.x86_64.rpm

You might have to install pygtk and vte first
# yum install vte.x86_64
# yum install pygtk2.x86_64

Contrarily to what the install doc states, you will first have to edit the /etc/ocfs2/cluster.conf by hand before being able to do anything.

cluster:
       node_count =1
       name=ocfs2
node:
        ip_port = 7777
        ip_address = my_cluster_node_1_interconnect_ip_adress
        number = 1
        name = my_cluster_node_1_hostname
        cluster = ocfs2


Once you've edited the file on one of the nodes, you're not done yet. Do a:

# service o2cb configure
Configuring the O2CB driver.

This will configure the on-boot properties of the O2CB driver.
The following questions will determine whether the driver is loaded on
boot.  The current values will be shown in brackets ('[]').  Hitting
<ENTER> without typing an answer will keep that current value.  Ctrl-C
will abort.

Load O2CB driver on boot (y/n) [y]: y
Cluster stack backing O2CB [o2cb]:
Cluster to start on boot (Enter "none" to clear) [ocfs2: ocfs2
Specify heartbeat dead threshold (>=7) [31]:
Specify network idle timeout in ms (>=5000) [30000]:
Specify network keepalive delay in ms (>=1000) [2000]:
Specify network reconnect delay in ms (>=2000) [2000]:
Writing O2CB configuration: OK
Starting O2CB cluster ocfs2: OK

Then only you may start the graphic ocfs2 console:

# ocfs2console

In the GUI, go to Edit-> Add node, and add your second node, with its interconnect ip address. Validate.

Go to Edit -> Propagate Configuration.

By now, you should see the following configuration on your two nodes.

node:
        ip_port = 7777
        ip_address = my_cluster_node_1_interconnect_ip_adress
        number = 1
        name = my_cluster_node_1_hostname
        cluster = ocfs2

node:
        ip_port = 7777
        ip_address = my_cluster_node_2_interconnect_ip_adress
        number = 2
        name = my_cluster_node_2_hostname
        cluster = ocfs2

cluster:
       node_count =3
       name=ocfs2



Do a:
# service o2cb configure
on the second node

Check if the service is finally up and running running on both nodes:

# ps -ef | grep o2
root     24816   153  0 17:27 ?        00:00:00 [o2net]
root     24891 18206  0 17:27 pts/0    00:00:00 grep o2

Then, you may go on formatting the volume you've prepared on your shared storage.

Here, the volume is configured under Linux with Device-Mapper multipath, and is seen under /dev/mapper as VOL1.

# mkfs.ocfs2 -c 4K -C 4K -L "ocfs2volume1" /dev/mapper/VOL1


Then, you may  just create a mount point on which to mount the volume on both nodes, /u01/app/ocfs2mounts/grid for example, if you're planning on installing Oracle grid infrastructure.

Mount the filesystem on both nodes

# mount /dev/mapper/VOL1 /u01/app/ocfs2mounts/grid

Drop me a line, or have a look at the links, if this post has been useful to you.

Happy computing

Nixman.






samedi 19 septembre 2009

Oracle 11gr2 RAC on Red Hat Linux 5.3 install guide part1


Oracle 11gr2 RAC on Red Hat Linux 5.3 install guide part1: Installing the grid infrastructure:

Oracle 11gR2 has been released for Linux, and the installation has somewhat changed from precedent versions, including 11gR1. In this step-by-step guide, we will lead you through a real-life Oracle Real Application Cluster (RAC) installation, its novelties, incompatibilities, and the caveats of Oracle install documentation.

The installation process is divided into two parts: The grid infrastructure installation, which now includes the clusterware, but also ASM installation, which has been moved there from the regular database installation. This stems from the fact that ASM now supports voting disks, and OCR files, and you are no longer required (actually, its now discouraged) to place the voting disks and OCR files on raw devices.

Grid infrastructure also installs for you the ACFS cluster file system, which allows you to share the ORACLE_HOME of your database installation between all the nodes of your RAC cluster. However, it doesn't allow you to share the grid infrastructure ORACLE_HOME between the nodes. For that, you would need to install the grid infrastructure binaries on an ocfs2 filesystem. However, that's not supported by Oracle, nor does it work. Last year, Oracle had promised an Oracle Universal File System (UFS), and it is a bit disappointing to see that ACFS is not what we expected yet.

Download the necessary files:

You will need the grid infrastructure disk, as well as the two database disks from Oracle's site. Download as well the deinstall disk, as Oracle Universal Installer doesn't support the deinstallation of the binaries anymore, and everything has moved to this 300Mb plus disk.

You will also need the three asmlib files from OTN, that are downloadable here.

Do a uname -rm on your platform in order to find out which ones are the right ones for you.

Oracle validate rpm will also be useful in order to ensure you have all the necessary rpm's installed on your server.

Setting the system parameters and prerequisites:

Nothing much new here. Simply follow the install guide's  instructions. The installer will check anyhow if you have set the parameters right, and even generate a fixup script for most of the prerequisites.

# cat >> /etc/sysctl.conf <<EOF
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmax = 4294967296
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF

# sysctl -p

An extremely intriguing fact is the necessity to set up ntpd with the -x option which disallows any brutal adjustment of the system clock under a drift of 600s (instead of 128ms by default). This is a workaround for a RAC bug that was supposed to have been corrected in release 10.2.0.3... Well, actually, it may not be a bug, but a feature for any cluster that needs synchronization. The downside of the -x option is, that if your hardware is down for a month, and the system clock goes off half an hour, it will take days for it to adjust slowly to network time.

Be sure to do a chkconfig oracleasm on after setting up oracleasm on a RHEL 5.3. Else, you will corrupt your voting disks and OCR upon the first reboot. The install guide has simply forgotten to mention that oracleasm enable/disable have been deprecated on this platform.

Don't bother setting up VIP's or passwordless ssh connectivity, contrarily to what the install guide instructs you to do: the installer won't appreciate your initiative, and you will have to set them up the way Oracle wants it. Simply give the same password to your grid and oracle users on both nodes.

Creating the UNIX groups, users, and directories:

Create two separate users (grid and oracle for example), one for grid infrastructure installation, and one for the database installation, with separate ORACLE_BASE and ORACLE_HOME directories.

A new set of three groups have been created to manage asm. Grid user should be member of them.

A change to OFA is that the grid user's ORACLE_HOME cannot be under its ORACLE_BASE directory, but on a separate path.

Here, we will point oracle user's home to a shared ACFS mount. We'll mount that filesystem later, after grid infrastructure's installation  when we will have ACFS installed. Indeed, ACFS is built on top of ASM, which in turn is installed as part of grid infrastructure. Hence the separation of grid infrastructure and database installation.

(As a footnote: you may change u01 to get27, for example, and still be OFA-compliant)

# /usr/sbin/groupadd oinstall
# /usr/sbin/groupadd dba
# /usr/sbin/groupadd oper
# /usr/sbin/groupadd asmadmin
# /usr/sbin/groupadd asmdba
# /usr/sbin/groupadd asmoper
# /usr/sbin/groupadd orauser

# /usr/sbin/usermod -g oinstall -G dba,asmdba oracle
# /usr/sbin/usermod -g oinstall -G dba,asmdba,oper,oinstall,asmadmin grid

# mkdir /u01/app/11.2.0/grid
# mkdir /u01/app/grid
# mkdir /u01/app/acfsmounts/oracle
# chown -R grid:oinstall /u01/app
# chmod -R 775 /u01/app
# chown -R oracle:oinstall /u01/app/acfsmounts
# chmod -R 775 /u01/app/acfsmounts

# cat >> /etc/security/limits.conf <<EOF
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF

To be continued ... stay tuned.

vendredi 18 septembre 2009

Discrepancies and catchas in Oracle 11gR2 grid infrastructure install guide for Linux


Discrepancies in Oracle 11gR2 grid infrastructure install guide for Linux:

- Oracle instructs you to create VIP's on both nodes as a preinstall task.
However, if you do so, Oracle grid infrastructure installer will tell you the VIP adresses are already in use.

- Even though you have set up passwordless ssh connectivity between two RAC nodes, the installer keeps telling you this is not the case. I guess it has something to do with Oracle using rsa1. I gave up and gave both my oracle users the same password, and clicked on "setup", and let the installer do it for me. Everything went fine afterwards.

- /usr/sbin/oracleasm enable and disable have been deprecated on RHEL 5.3.
You have to use chckconfig oracleasm on.
If you fail to do so, upon reboot, asmlib is not loaded, and your voting disks and OCR are corrupted.

- If you use ACFS have to use different ORACLE_BASE directories for the Oracle grid infrastructure user (ex:grid: /u01/app/grid/), and the Oracle database user (ex: oracle: /u01/app/oracle/).
In the install doc, this is not so clear, as only ORACLE_HOME directories (ex:/u01/app/11.2.0/grid/ for grid and /u01/app/oracle/acfsmounts/orahome1/ for oracle) have to be different, the ORACLE_BASE seeming to be a unique one.

- Even though you can set up a shared ORACLE_HOME through ACFS for the database binaries, you still have to rely on ocfs2 if you want to have the Oracle grid infrastructure binaries on a shared filesystem.

- You absolutely have to be patient ant wait for the root.sh script to finish on your first node (can last half an hour), before you may execute it on your other nodes. Else, your  installation will miserably fail.

A complete RAC installation guide for Oracle 11gR2 on RHEL 5.3 with multipath will follow soon.

mercredi 16 septembre 2009

Enabling server-side failover, TAF and load-balancing on Oracle 1OgR2 RAC


Sometimes, you don't  have the possiblity enable Transparent application failover on the client side (in the tnsnames.ora file for example).

That's where this new feature in Oracle 10gR2 RAC comes handy:

You can enable both failover and load-balancing on the server side, by executing a simple dbms_service procedure.

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'MY_SERVICE_NAME'
, aq_ha_notifications => TRUE
, failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC
, failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT
, failover_retries => 60
, failover_delay => 10
, clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);


To disable the feature, it's as simple:

begin
    dbms_service.modify_service(
      service_name=>'MY_SERVICE_NAME',
      failover_type=>DBMS_SERVICE.FAILOVER_TYPE_NONE,
      failover_method=>DBMS_SERVICE.FAILOVER_METHOD_NONE
    );
  end;
/


For complete documentation, you can check:

http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/hafeats.htm#BABIAICG



Happy computing,

Nixman

Purger les jobs Datapump terminés ou orphelins

Réf: Metalink Doc ID: 336014.1

SQL > SET lines 200

COL owner_name FORMAT a10

COL job_name FORMAT a20

COL state FORMAT a11

COL operation LIKE state

COL job_mode LIKE state

SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' order by 1,2;

EXPIMP SYS_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0
EXPIMP SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

Ne sélectionner que ceux qui sont en "NOT RUNNING". Voir où se trouvent leurs Master tables:

SQL> SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

VALID 85215 TABLE EXPIMP.SYS_EXPORT_TABLE_02
VALID 85162 TABLE EXPIMP.SYS_EXPORT_FULL_01

Dropper les Master tables concernés:

SQL> DROP TABLE EXPIMP.sys_export_table_02;

SQL> DROP TABLE EXPIMP.sys_export_full_01;

Killer un job datapump non interactif proprement

Réf: Metalink Doc ID: 336014.1

SQL> SET lines 200

COL owner_name FORMAT a10

COL job_name FORMAT a20

COL state FORMAT a11

COL operation LIKE state

COL job_mode LIKE state

SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%';

EXPIMP SYS_EXPORT_FULL_05 EXPORT FULL RUNNING 0

SQL> connect expimp/expimp

SQL> DECLARE h1 number;

  • BEGIN
  • h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_05','EXPIMP');
  • DBMS_DATAPUMP.STOP_JOB (h1);
  • END;

/

La session passe en "STOP PENDING" pendant un certain temps, puis en "NOT RUNNING"

Correspondance volumes ASM ORACLE , disks et device côté Linux:

/etc/init.d/oracleasm listdisks

VOL1

VOL2

export $ORACLE_SID=+ASM

sqlplus / as sysdba

SQL> show parameter asm_diskstring asm_disk

string string /dev/oracleasm/disks/*

SQL> select path from v$asm_disk;

/dev/oracleasm/disks/VOL1

ls -lsa /dev/oracleasm/disks total 0

  • 0 drwxr-xr-x 1 root root 0 avr 18 12:51 .
  • 0 drwxr-xr-x 1 root root 0 avr 18 12:51 ..
  • 0 brw-rw 1 oracle dba 8, 81 avr 18 12:51 VOL1
  • 0 brw-rw 1 oracle dba 8, 97 avr 18 12:51 VOL2

Voir le major/minor (ex:8,81) et les trouver dans /dev:

ls -lsa /dev |grep " 8,"

  • 0 brw-rw 1 root floppy 8, 80 Jun 24 2004 sdf

    0 brw-rw 1 root disk 8, 81 Jun 24 2004 sdf1 --> VOL1 est monté sur /dev/sdf1

  • 0 brw-rw 1 root disk 8, 90 Jun 24 2004 sdf10
Ou cat /proc/partitions -> voir le major/minor

jeudi 14 août 2008

HR ACCESS user logins and passwords


HR ACCESS stores the user passwords without encryption in the UC10 table. As an Oracle DBA, if you have access to the database instance, all you have to do is issue the following command through SQL*PLUS:

 SQL> select cdutil, cdpass from UC10;

CDUTIL   CDPASS
-------- --------   
USER1     PASWORD1
USER2   PASWORD2
USER3   PASWORD3
USER4   PASWORD4
USER5  PASWORD5

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman

dimanche 18 mai 2008

Installing and configuring Oracle Heterogeneous Services for SQLServer


All databases share a common set of normalized SQL, which, in theory, allows them to interoperate directly using database links.
However, reality is not so simple, as those who've tried to connect DB2 with SQLServer might have realized.

Luckily, with Oracle, there are at least two ways to achieve direct SQL*NET connectivity to foreign databases: Oracle Heterogeneous Services ODBC (HSODBC) and Oracle Transparent Gateways.

Here, we will achieve a simple database link between an Oracle database on a UNIX server and an SQLServer database residing on a Windows Server 2003 machine through the simplest of the two methods: Oracle Heterogeneous services. Bluntly, it consists in installing an Oracle pseudo-listener on the target non-Oracle database server.

As Microsoft doesn't provide any sort of UNIX client for SQL Server, all this interoperability is achieved thanks to work done by Oracle coders. Kudos to them, and  the opposite to the other guys.
 
X = Windows Server 2003 with SQLServer 2005 + Oracle 8iR3 with Oracle HS
Y = Solaris 8 server with Oracle 8iR3 + Heterogeneous Services installed.
 
 
On X:

Step 0) On X: Install Oracle Server 8iR3 software or later with Heterogenous Database connectivity (Check that ODBC DRIVERS have really been installed). I won't detail the installation of Oracle on Windows here.
 
Step 1) On X: Configure DSN:
Go to: Settings -> Control Panel. Double-click on ODBC icon.
Then click on the System DSN tab and Add button. Add SQL Server, as local server. Name it, for example, MSQL (we will be using "MSQL" in our example configuration files from now on).
Test it. The default database is the on we're targetting.
 
Step 2) On X :  Copy the file inithsodbc.ora into initMSQL.ora in the  $ORACLE_HOME\hs\admin directory (If you'd named the DSN "ZOZO" in the previous step, you would have named the file initZOZO.ora, of course).
 
Step 3) On X: Modify  the initMSQL.ora file in the following manner (HS_FDS_CONNECT_INFO must have the same name as the DSN):

###########
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSQL
HS_FDS_TRACE_LEVEL = NO
###########
 
Step 4) On X:  Modify the listener.ora file in $ORACLE_HOME\network\admin directory in the following manner (you're modifying the  SID_LIST_LISTENER paragraph. SID_NAME must be the same as the DSN) :
 
###########
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSQL)
         (ORACLE_HOME = c:\Orant)
         (PROGRAM=hsodbc)
       )
      )
 ###########

Another solution would be to add an altogether new listener, that you've called MSQL, like this (here, we've set it to listen on port 1522):

###########
MSQL =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
 
SID_LIST_MSQL=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSQL)
         (ORACLE_HOME = c:\Orant)
         (PROGRAM=hsodbc)
       )
      )
###########

In that case, you would have to start this listener specifically by issuing a lsnrctl start MSQL
 
Step 5) On X: restart the listener by issuing a  lsnrctl reload.
 
 
On Y:
 
Step 1) On Y: set GLOBAL_NAMES=FALSE  in your database's init.ora  file or in the initialization parameters.
 
Step 2) On Y: Add an entry pointing at server X listener by adding the following lines to the tnsnames.ora file:
 
###########
testMSQL  =
  (DESCRIPTION=
    (ADDRESS=
        (PROTOCOL=tcp)
        (HOST=SERVER_X_IP_ADDRESS)
        (PORT=SERVER_X_LISTENER_PORT)
     )
     (CONNECT_DATA=
        (SID=MSQL)
     )
     (HS=OK)
  )
###########
 
Step 3) On Y: Test connectivity by issuing a tnsping testMSQL
 
Step 4) On Y: Create the database link between your Oracle database and testMSQL by issuing the following SQL command:
SQL> create public database link testingMSQL connect to USER identified by PASSWORD using 'testMSQL';
 
Step 5) On Y: Do some selects on Server X's tables:
SQL> select * from TABLE_NAME@testingMSQL;

You're done!

Beware that you're restricted to normalized SQL. between the two databases. Old-timers will find themselves back in Oracle 6 days: You won't be able to use INSERT SELECT statements or other Oracle enhancements, but will have to go through a cursor, etc... However, you will be able to issue simple SELECT, INSERT and UPDATE commands. Which is what you wanted in the first place.

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman

vendredi 9 mai 2008

Selecting tables for an import parfile


This little SQL script is quite handy when constructing a parfile for an import from a dump file of a previous Oracle export

Suppose you want to import all tables beginning with "XY", and there are several hundreds of them.

You would first construct a parfile named my_parfile.par, with the following content:

USERID=USER/PASSWORD
BUFFER=40960
FILE=DUMP_FILE_NAME.dmp
LOG=IMPORT_LOG_FILE_NAME.log
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y

Then, you would create the following sql script (that you've called select_xy_tables_parfile.sql), in order  to construct  the list of tables you want to import (supposing the tables still have the same names as at the moment of the initial export):

set head off 
set pages 0
set trims on
set echo off
set feedback off  
spool my_XY_tables.txt
select decode( rownum, 1, 'TABLES=(', ',' ), table_name
from user_tables
where table_name like 'XY%'
union all
select ')', null
from dual ;
spool off
quit


Then, just run the script against your database:

SQL> @select_xy_tables_parfile.sql


After that, just do a cat my_XY_tables.txt >> my_parfile.par

Finally, after setting the correct ORACLE_SID, do an imp parfile=./my_parfile.par

You're done.

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman

mercredi 30 avril 2008

Trouver le process ID UNIX d'une session Oracle


(If you're looking for the english version, it's here)
(Ceci est la traduction française de l'article précédent.)

Fonctionne sur: Solaris, Linux, AIX

Supposons que vous ayiez un processus Oracle qui ait généré un verrou sur votre base de données et n'arrivez pas à le tuer avec une simple commande ALTER SYSTEM KILL SESSION 'sid, serial#'

Il vous suffit alors d'effecuer la jointure suivante entre les tables V$SESSION et V$PROCESS d'Oracle, afin de retrouver le process ID système (spid) qu'il faudra tuer:

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;


La colonne spid vous donne la valeur du process ID qu'il faudra tuer avec une commande shell kill -9 spid.

Et voilà!

Rem: Sur un serveur Windows, on utiliserait la commande suivante:
orakill $ORACLE_SID spid

Laissez-moi un commentaire si cet article vous a été utile.

Bien à vous

Nixman

vendredi 14 mars 2008

Finding the UNIX process ID of an Oracle session to kill


Works with: Solaris, Linux, AIX


Let us start with something easy and frequently used on sites with badly written PL/SQL code.

Let's suppose that you have an Oracle process that has gone astray, or even worse, is generating a lock on your database, preventing other users from making updates on a table.

The first step would be to find the Oracle session that's causing the lock (not the ones that are suffering from the lock, which you would find easily by quering the V$SESSION table).

It's as easy as:

SELECT * FROM dba_blockers;

You might have to wait for a while before getting the magic serial# and sid (not to be mistaken with ORACLE_SID of course) needed to run your ALTER SYSTEM KILL SESSION 'sid , serial#' command.

Note: on Oracle 8i and earlier, you would first have to run the catblock.sql script in $ORACLE_HOME/rdbms/admin/ directory in order to create the DBA_BLOCKERS table.

However, sometimes killing the session simply won't work. In that case, you could have to kill the session, or an Oracle proces gone astray through UNIX system utilities.

For that purpose, on UNIX systems, Oracle has the V$PROCESS table, which can be joined with the V$SESSION table to find the UNIX process ID matching your Oracle session ID (the sid field of DBA_BLOCKERS).

The following query will yield you the needed system process ID (spid), among other useful information about your rogue process or session:

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;


All you need to do now is to run a kill -9 spid  on your UNIX machine upon the process number given by the spid column of the above query.

Note: On a windows box, you would use the orakilll.exe utility located in $ORACLE_HOME/bin. Like this:
orakill $ORACLE_SID spid

Happy computing.

Drop me a comment if this post has been useful to you.
Alternatively, you could also visit a few links to keep me in business ;-)


Nixman