UNIX tricks and treats

Purge automatique des logs sous adrci:

L'âge des logs est exprimée en heures
Par défaut, les logs sont purgés tous les mois pour les logs à vie courte (720 heures) et tous les ans pour les logs à vie longue (8760 heures).
Nous allons baisser ces valeurs à 7 et 30 jours (168 et 720 heures).

adrci> show home
ADR Homes:
diag/tnslsnr/srv_1/listener
diag/tnslsnr/srv_1/listener_scan1
diag/asm/+asm/+ASM1

adrci> set home diag/tnslsnr/srv_1/listener
adrci> show control

ADR Home = /u01/app/grid/diag/tnslsnr/srv_1/listener:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
3936991633           720                  8760                 2010-01-18 18:23:33.022104 +01:00                                                 2010-09-21 11:59:19.401387 +02:00        1                    2                    76                   1                    2010-01-18 18:23:33.022104 +01:00
1 rows fetched


adrci> set control (SHORTP_POLICY=168)
adrci> set control (LONGP_POLICY=720)
adrci> show control

ADR Home = /u01/app/grid/diag/tnslsnr/srv_1/listener:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
3936991633           168                  720                  2010-09-21 12:05:06.248617 +02:00                                                 2010-09-21 11:59:19.401387 +02:00        1                    2                    76                   1                    2010-01-18 18:23:33.022104 +01:00
1 rows fetched

Réaliser la même modification pour les autres homes

adrci> set home diag/tnslsnr/srv_1/listener_scan1
adrci> set control (SHORTP_POLICY=168)
adrci> set control (LONGP_POLICY=720)

adrci> set home diag/asm/+asm/+ASM1
adrci> set control (SHORTP_POLICY=168)
adrci> set control (LONGP_POLICY=720)

Purge manuelle des logs sous adrci:

Cette fois-ci, l'âge des logs est exprimée en minutes

adrci> show home
ADR Homes:
diag/tnslsnr/srv_1/listener
diag/tnslsnr/srv_1/listener_scan1
diag/asm/+asm/+ASM1

adrci> set home diag/tnslsnr/srv_1/listener
adrci> purge -age 5400

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

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

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.

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; /

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.

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:

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;

/