Oracle 11g & J2EE Samples: 08/01/2011

mercredi 31 août 2011

Oracle 11g : Backup and recovery

Some useful information @ http://wap.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ

Oracle 11g : Using Flashback table and Managing the recycle bin

what is the recycle bin
Oracle says : "The recycle bin is actually a data dictionary table containing information about dropped objects.Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints."

Viewing the content of the recycle bin

SQL>select * from recyclebin;

SQL>desc RECYCLEBIN;
OBJECT_NAME      NOT NULL VARCHAR2(30)
ORIGINAL_NAME    VARCHAR2(32)
OPERATION        VARCHAR2(9)
TYPE             VARCHAR2(25)
TS_NAME          VARCHAR2(30)
CREATETIME       VARCHAR2(19)
DROPTIME         VARCHAR2(19)
DROPSCN          NUMBER
PARTITION_NAME   VARCHAR2(32)
CAN_UNDROP       VARCHAR2(3)
CAN_PURGE        VARCHAR2(3)
RELATED          NOT NULL NUMBER
BASE_OBJECT      NOT NULL NUMBER
PURGE_OBJECT     NOT NULL NUMBER
SPACE            NUMBER

Object naming in the recycle bin
When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names.
Format of objects is : BIN$unique_id$version

where:

unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
version is a version number assigned by the database

Enabling and Disabling the Recycle Bin
To disable the recycle bin, issue one of the following statements:

SQL>ALTER SESSION SET recyclebin = OFF;

SQL>ALTER SYSTEM SET recyclebin = OFF scope=SPFILE;

To enable the recycle bin, issue one of the following statements:

SQL>ALTER SESSION SET recyclebin = ON;

SQL>ALTER SYSTEM SET recyclebin = ON scope=SPFILE;

Remark :For modification at the system level, you need to restart your database.

Viewing the content of the recycle bin
2 views available :

USER_RECYCLEBIN
DBA_RECYCLEBIN

SQL>SELECT object_name, original_name FROM dba_recyclebin
   WHERE owner = 'XXX';

SQL>show recyclebin

Restoring table from recycle bin
Issue the following command :

FLASHBACK TABLE <my_table> TO BEFORE DROP;

If you want to rename in the same time the table :

SQL>FLASHBACK TABLE <my_table> TO BEFORE DROP RENAME TO <new_table_name>;

More reference on flashback database: see @ http://abcdba.com/abcdbabackupdataguardhowtoconfigureaflashbackdb

mardi 30 août 2011

Oracle 11g: Recover loss of datafile in NOARCHIVELOG database

Using SQLplus
First of all, you have to identify the missing or corrupted file using the alert log or the v$recover_file view.

Once the datafile identified, you need to stop the database :

SQL>shutdown immediate;

Copy your backup of the file at the same place of your missing datafile.

Startup your database :

SQL>startup;
ORACLE instance started.

Total System Global Area  728199168 bytes
Fixed Size                  1338924 bytes
Variable Size             213909972 bytes
Database Buffers          503316480 bytes
Redo Buffers                9633792 bytes
Database mounted.
ORA-01113: file 44 needs media recovery
ORA-01110: data file 44: '/database/VOLATI/data/DATA_TEST.dbf'
SQL>

As you can see the file you copy need media recovery.

if you check the status of the database, you can see that the database is not opened but stay MOUNT state:

SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>

Now copy the corrupted file or missing one from your last backup to the previous location of the file.

Here you can ask for media recovery on datafile with file id 44 :

SQL>recover datafile 44;

May be you may need to set the datafile online :

SQL>alter database datafile '/database/VOLATI/data/DATA_TEST.dbf' online;

SQL>alter database datafile 44 online;

If the recovery on a unique file fails, then try to recover the entire database by restoring all the data files and also the control files from the last backup of your database (remember as your database is in noarchive log, may be you lost some data).

Using RMAN

$>rman target /
RMAN>sql 'alter database datafile 44 offline';
RMAN>restore datafile 44;
RMAN>recover datafile 44;
RMAN>sql 'alter database datafile 44 online';

Oracle 11g : How to know which file to recover

Using SQLplus
Connect to the database and use the following sql instruction :

SQL>select * from v$recover_file;
     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
        44 ONLINE  ONLINE  FILE NOT FOUND                                                             0

As you can see the file with id 44 is missing.

To retrieve the name of the datafile you can write :

SQL>select file#, name from v$datafile join v$recovery_file using( file#);

Using RMAN
To validate the structure of the database.

$>rman target /
RMAN>validate database;

If you want to validate also logical block failure :

$>rman target /
RMAN>validate database CHECK LOGICAL;

To list all database failure that need to be restored or recovered:

$>rman target /
RMAN>list failure all;

To ask an advise from the data recovery advisor :

$>rman target /
RMAN>advise failure;

To ask rman to recover all failure that could be repaired automatically :

$>rman target /
RMAN>repair failure;

The 'repair failure' command need to be executed after the 'advise failure' command because 'advise failure' registers all problems in the Automatic Diagnostic Repository.

Oracle 11g: Recover the lost of a redo log

Redo log file is part of a redo log group that have more than one file

Identify the missing or corrupted redo log file from alert log

Drop the log file member from the group:

SQL>alter database drop logfile member '<redo_log_file_name>';

Add a new file redo log file member in the previous group :

SQL>alter database add logfile member '<redo_log_file_name>' to group #;

Redo log file is unique inside a redo log group

May be you can drop the group and recreate a new one (if redo log not ACTIVE or CURRENT) :

SQL>alter database drop logfile group #;
SQL>alter database add logfile group # ('<redo_log_file_name>') SIZE 150M;

May be you can clear the log file of the group that is corrupted or missing.
- If the database is in NOARCHIVELOG or if the redo log has been archived you can write :
```
SQL>alter database clear logfile group #;
```
- If the database is in ARCHIVELOG and the redo log file has not been archived :
```
SQL>alter database clear unarchived logfile group #;
```

If the redo log is ACTIVE then you can try :
```
SQL>alter system checkpoint;
```
If the checkpoint fails then you need to perform a complete recovery from a previous backup.

Remark : A log cannot be dropped or cleared until the thread's checkpoint has advanced out of the log.
If the database is not open, then open it. Crash recovery will advance the checkpoint.
If the database is open force a global checkpoint. If the log is corrupted so that the database cannot be opened, it may be necessary to do incomplete recovery until cancel at this log.

For an incomplete recovery :

SQL>RECOVER DATABASE UNTIL CANCEL;
SQL>ALTER DATABASE OPEN RESETLOGS;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

Remark : If you are unable to recover a datafile with redolog (cause redolog is corrupted) and you accept to loose data from datafile then you can use :

SQL>alter database clear logfile unrecoverable datafile;

Oracle requires that you use the unrecoverable datafile option when the particular redo log group is both unarchived and you cannot get the data from it. It looks like Oracle expects that you had already tried to recover the datafile and could not because of a bad redo log.

Note seen from another site (Don't remember where cause just have a copy past):
Oracle Recovery with _allow_resetlogs_corruption

Recovery of a database using the undocumented parameter _allow_resetlogs_corruption should be regarded as a last-ditch, emergency recovery scenario only, and should not be attempted until all other avenues of recovery have been exhausted.
Note that Oracle will not support a database that has been recovered using this method unless it is subsequently exported and rebuilt.
Essentially, using _allow_resetlogs_corruption forces the opening of the datafiles even if their SCNs do not match up; then, on the next checkpoint, the old SCN values are overwritten. This could leave the database in an unknown state as far as concurrency.
To use this option:

SQL>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SQL>shutdown immediate;
SQL>startup mount;
SQL>recover database until cancel;
CANCEL
SQL>alter database open resetlogs;
SQL>shutdown immediate;
SQL>startup;

lundi 29 août 2011

Oracle 11g: Recover the loss of a control file

Using RMAN

Shutdown the database
Statup the database in NOMOUNT

Use rman to recover the control file :

$>rman target /
rman>startup nomount
rman>restore controlfile from '+DATA/<oracle_sid>/controlfile/current.xxx.yyyy';
rman>alter database mount;
rman>alter database open;

You can also restore the control file from an autobackup :

...
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
...

Using files from file system

Check the alert log file to retrieve the lost controlfile
Shutdown the database
Copy a multiplexe control file (remember control file must be multiplexed and Oracle recommends having at least three copies of the control file) into the missing one.
Startup the database in the NOMOUNT state

Execute the following command under SQLplus:

SQL> RECOVER CONTROLFILE USING BACKUP CONTROLFILE UNTIL CANCEL;

Open the database with RESETLOGS option:

SQL> ALTER DATABASE MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;

Oracle 11g : Data recovery advisor activation

To activate the Data recovery advisor in single instance :

$> rman target /
rman> list failure all;

vendredi 26 août 2011

Oracle 11g: Online user backup

Today I have learned a new thing about backup and recovery on how to keep a database in backup mode.
Even if i know that Oracle strongly recommend the use of RMAN for backup and recovery . I thought, this would be good thing to learn how to make an online backup of the database using the legacy method of backup, i.e. ALTER TABLESPACE | DATABASE BEGIN/END BACKUP.

Database in Archive log
First of all, the database must be in archive log mode.
To do this, shutdown the database and be sure that the database will not be in recovery state (avoid shuting down with abort option).
After shuting down, start the database in mount state (archive log could only be activated in mount state).

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

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1807921152 bytes
Fixed Size                  1337128 bytes
Variable Size            1073744088 bytes
Database Buffers          721420288 bytes
Redo Buffers               11419648 bytes
Database mounted.

Now, the database can be set in archivelog mode.

SQL>alter database archivelog;
Database altered.

SQL>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/11.2.0/dbs/arch
Oldest online log sequence     299
Current log sequence           306

SQL>alter database open;
Database altered.

Backup the entire database
Since Oracle 11g all the files could be set in backup at once :

SQL>alter database begin backup;

Then you can check if all file are in backup mode :

select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                      0
         2 ACTIVE                      0
         3 ACTIVE                      0
         4 ACTIVE                      0
         5 ACTIVE                      0
         6 ACTIVE               33564182 26-AUG-11
         7 ACTIVE                      0

7 rows selected.

Now you can copy all the database files using the system command line.

To remove the database from the backup mode, use:

SQL>alter database end backup;

Backup some parts of the database
Instead of using the alter database begin/end backup, it is possible to set each tablespace in backup mode using the following sql command :

SQL>alter tablespace <tablespace_name> begin backup;

To retrieve all tablespaces and associated datafiles use the DBA_DATA_FILES table.

Here is an example showing the procedure to set the system table space in backup mode :

SQL>alter tablespace system begin backup;
Tablespace altered.
SQL>select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE               33587729 26-AUG-11
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE           33564182 26-AUG-11
         7 NOT ACTIVE                  0

7 rows selected.

SQL>alter tablespace system end backup;
Tablespace altered.

Remark : READ ONLY, INVALID & OFFLINE tablespaces could not be set in backup mode. They could be copied directly because they could not be accessed or modified by any users.

Remark : Don't forget to backup the server parameter file, the control files and current redo log files.
To backup the controle file in a specific location and file name :

SQL>alter database backup controlfile to '&repertoire/control.ctl' REUSE ;

Before to copy the redo log, look at which redo log is currently in used :

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1        306  209715200        512          1 YES INACTIVE              33559556 26/08/2011 12:03:45     33584866 26/08/2011 14:44:25
         2          1        307  209715200        512          1 YES INACTIVE              33584866 26/08/2011 14:44:25     33585365 26/08/2011 14:46:41
         3          1        308  209715200        512          1 YES INACTIVE              33585365 26/08/2011 14:46:41     33585368 26/08/2011 14:46:43
         8          1        305  209715200        512          1 YES INACTIVE              33559526 26/08/2011 12:02:37     33559556 26/08/2011 12:03:45
         5          1        302  209715200        512          1 YES INACTIVE              33302574 22/08/2011 01:18:56     33428871 24/08/2011 14:36:26
         6          1        303  209715200        512          1 YES INACTIVE              33428871 24/08/2011 14:36:26     33512626 25/08/2011 17:04:01
         7          1        304  209715200        512          1 YES INACTIVE              33512626 25/08/2011 17:04:01     33559526 26/08/2011 12:02:37
         4          1        309  209715200        512          1 NO  CURRENT               33585368 26/08/2011 14:46:43   2.8147E+14

8 rows selected.

The current redo log is the redo log member #1 of the group #4.
To retrieve the name of the redo log take a look at the v$logfile table or use the following command :

select l.status, l.group#,l.members, f.member from v$log l inner join v$logfile f on( l.group#=f.group#);

Once the current redo log file identified, perform a swith log file using:

SQL>alter system switch logfile;

Then you can copy the redo log file in your backup or recovery area.

jeudi 25 août 2011

Oracle 11g : Dynamic Performance Statistics

Oracle 11g provides 3 levels of performance statistics :

System statistics
Session statistics
Service statistics

For each level, Oracle provide cumulative statistics and Wait event statistics.

Dynamic Statistics Views
	System level	Session level	Service level
Cumulative view	V$SYSSTAT	V$SESSSTAT	V$SERVICE_STAT
Wait event view	V$SYSTEM_EVENT	V$SESSION_EVENT	V$SERVICE_EVENT

For memory, you can also take a look at :

Memory views

V$SGAINFO

V$SGASTAT

V$PGASTAT

V$BUFFER_POOL_CACHE

V$LIBRARYCACHE

For contention, you can look at :

Contention views

V$LOCK

V$UNDOSTAT

V$WAITSTAT

V$LATCH

Oracle 11g : AMM & ASMM

AMM - Automatic Memory Management
AMM allows the dba to define precisely the global use of memory for the entire database.
This memory allocation is divided between the SGA component and all PGA components. This memory distribution is done automatically by the database memory size advisor.
There is 2 parameters to set in order to activate the Automatic Memory Management :

MEMORY_TARGET : Minumum memory we wish to allow for SGA and all PGAs
MEMORY_MAX_TARGET : Maximum memory that MEMORY_TARGET should not exceed

you enable the automatic memory management by setting only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.

The activation of the AMM implies the activation of ASMM and the activation of the PGA memory advisor.

Remark :

If MEMORY_TARGET is set and MEMORY_MAX_TARGET is omitted then MEMORY_MAX_TARGET is set automatically with the value of MEMORY_TARGET.
If MEMORY_MAX_TARGET is set and MEMORY_TARGET omitted then MEMORY_TARGET is set to 0. MEMORY_TARGET could be updated dynamicaly using ALTER SYSTEM. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
We assume commonly that MEMORY_TARGET = SGA_TARGET + PGA_AGGREGATE_TARGET.

Remark : Any modification on these parameters implies the reboot of the entire database. MEMORY_MAX_TARGET is not a dynamic initialization parameter.

ASMM - Automatic Shared Memory Management
ASMM allows the dba to define the memory used by the SGA only.

There is 2 parameters to set in order to activate the Automatic Shared Memory Management :

SGA_TARGET : Minumum memory we wish to allow for SGA (not the PGAs)
SGA_MAX_SIZE : Maximum memory that SGA_TARGET should not exceed

You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a nonzero value.
Note:
The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL for automatic shared memory management to function.

Remark :

If SGA_TARGET is set then SGA_MAX_SIZE is set automatically with the value of SGA_TARGET.
If SGA_MAX_SIZE is defined then SGA_TARGET is setted by default to 0.
SGA_MAX_SIZE is a static parameter. You need to reboot the instance if updated.

Remark :

If ASMM is activated then the SGA memory size advisor is activated.
If ASMM is activated for the first time then it is not allowed to set a value for the following database parameters in the initialization parameters file:
- SHARED_POOL_SIZE
- DB_CACHE_SIZE
- LARGE_POOL_SIZE
- JAVA_POOL_SIZE
- STREAM_POOL_SIZE
Setting a value for one component of the SGA when ASMM is activated implies that we want to overload the behaviour the advisor. If the value is less than the value calculated by ASMM then the value is defined as a minimal requirement. If the value is more then the value defined by ASMM then the size of the component is increased to fit the dba requirement. The previous parameter could be altered using ALTER SYSTEM (except for the LOG_BUFFER parameter that is static after instance startup).

mardi 23 août 2011

Oracle 11g : Alert's type and associated tables

There is 2 kind of alerts in database :

Stateful alerts : Alerts based on threshold (Physical reads Per sec, User commits Per sec)
Stateless alerts : Alerts that does not depends on any kind of threshold (Snapshot too old, Recovery are low free space)

Stateless alerts
Stateless alerts are logged in the DBA_ALERT_HISTORY table.

Stateful alerts
Stateful alerts are logged in the DBA_OUTSTANDING_ALERTS table.
When the situation is resolved, the alert is cleared from this table and moved automatically to the DBA_ALERT_HISTORY.

Action on alerts
An alert could be cleared from the history. Its status changes to cleared and is keeped in the table.
An alert could be purged from the alert log history. In this way, the alert is removed from the repository and discared.

Oracle 11g: Automatic Workload Repository

General points

The MMON (Managability Monitor) database process generates a snapshot of the database every 60 minutes.
Snapshots are logged over a period of 8 days. (Retention policy by default, could be changed using the enterprise Manager console or through DBMS_WORKLOAD_REPOSITORY package)
The AWR repository is the property of the SYSMAN user and stored in the SYSAUX tablespace.

Baseline AWR
A baseline AWR is a set of snapshots between two snapshot id or two dates.
A baseline is identified using a unique name.
A baseline is keep in database until the baseline is deleted.

To create a baseline use :

Function
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    START_SNAP_ID   IN NUMBER,
    END_SNAP_ID     IN NUMBER,
    BASELINE_NAME   IN VARCHAR2, 
    DBID            IN NUMBER DEFAULT,
    EXPIRATION      IN NUMBER DEFAULT
) RETURNS NUMBER;

Procedure
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    START_TIME      IN DATE,
    END_TIME        IN DATE,
    BASELINE_NAME   IN VARCHAR2,
    DBID            IN NUMBER DEFAULT,
    EXPIRATION      IN NUMBER DEFAULT
);

To drop a baseline use :

Procedure
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
    BASELINE_NAME   IN VARCHAR2,
    CASCADE         IN BOOLEAN  DEFAULT,
    DBID            IN NUMBER   DEFAULT
);

Baseline information can be queried from the DBA_HIST_BASELINE view.

How to generate an extra snapshot
Write the following sql command :

SQL>exec dbms_workload_repository.create_snapshot('ALL');

How to list all snapshot registered in the AWR repository?
Select the DBA_HIST_SNAPSHOT to retrive the snapshot id from all snapshot registered in the AWR repository.
This table contains the following information :

SQL>desc DBA_HIST_SNAPSHOT;

SNAP_ID             NOT NULL NUMBER
DBID                NOT NULL NUMBER
INSTANCE_NUMBER     NOT NULL NUMBER
STARTUP_TIME        NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME   NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED       INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL          NUMBER
ERROR_COUNT         NUMBER
SNAP_FLAG           NUMBER

Statistics level
Adjust the STATISTICS_LEVEL database parameter :

ALL : Generaly not used
TYPICAL : Use default preferences, gives more advise of the database
BASIC : Statistics collections not active

This parameter is dynamic.

Changing the snapshot frequency and retention policy
By default snapshots of the relevant data are taken every hour and retained for 8 days.
The default values for these settings can be altered using the following procedure.

BEGIN
      DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
        retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
        interval  => 30);          -- Minutes. Current value retained if NULL.
    END;
    /

An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

To retirve the retention delay and snapshot interval :

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
2058702964 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

Workload Repository Views
The following workload repository views are available:

V$ACTIVE_SESSION_HISTORY     - Displays the active session history (ASH) sampled every second.
    V$METRIC                     - Displays metric information.
    V$METRICNAME                 - Displays the metrics associated with each metric group.
    V$METRIC_HISTORY             - Displays historical metrics.
    V$METRICGROUP                - Displays all metrics groups.
    DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
    DBA_HIST_BASELINE            - Displays baseline information.
    DBA_HIST_DATABASE_INSTANCE   - Displays database environment information.
    DBA_HIST_SNAPSHOT            - Displays snapshot information.
    DBA_HIST_SQL_PLAN            - Displays SQL execution plans.
    DBA_HIST_WR_CONTROL          - Displays AWR settings.

Workload Repository Reports

Oracle provides two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats.
The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

or for a specific database instance

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

to compare two periods of snapshot :

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

or for a specific database instance

@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

More references
See more @ http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/autostat.htm

Oracle11g: Oracle optimizer and Statistics collections

Statistics collections activation
The STATISTICS_LEVEL is the initial database parameter used to enable/disable advisories statistics collections.
There here 3 levels for the STATISTICS_LEVEL parameter :

BASIC : Desactivates all the advisory statistics collections
TYPICAL : Activates some advisory statistics collections
ALL : All advisory are activated

SQL>alter system set statistics_level=basic;
System altered.
SQL>SELECT statistics_name,
         session_status,
         system_status,
         activation_level,
         session_settable
  FROM   v$statistics_level
order by statistics_name;


STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History         DISABLED        DISABLED        TYPICAL          NO
Adaptive Thresholds Enabled    DISABLED        DISABLED        TYPICAL          NO
Automated Maintenance Tasks    DISABLED        DISABLED        TYPICAL          NO
Bind Data Capture              DISABLED        DISABLED        TYPICAL          NO
Buffer Cache Advice            DISABLED        DISABLED        TYPICAL          NO
Global Cache Statistics        DISABLED        DISABLED        TYPICAL          NO
Longops Statistics             DISABLED        DISABLED        TYPICAL          NO
MTTR Advice                    DISABLED        DISABLED        TYPICAL          NO
Modification Monitoring        DISABLED        DISABLED        TYPICAL          NO
PGA Advice                     DISABLED        DISABLED        TYPICAL          NO
Plan Execution Sampling        DISABLED        DISABLED        TYPICAL          YES

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics      DISABLED        DISABLED        ALL              YES
SQL Monitoring                 DISABLED        DISABLED        TYPICAL          YES
Segment Level Statistics       DISABLED        DISABLED        TYPICAL          NO
Shared Pool Advice             DISABLED        DISABLED        TYPICAL          NO
Streams Pool Advice            DISABLED        DISABLED        TYPICAL          NO
Threshold-based Alerts         DISABLED        DISABLED        TYPICAL          NO
Time Model Events              DISABLED        DISABLED        TYPICAL          YES
Timed OS Statistics            DISABLED        DISABLED        ALL              YES
Timed Statistics               ENABLED         ENABLED         TYPICAL          YES
Ultrafast Latch Statistics     DISABLED        DISABLED        TYPICAL          NO
Undo Advisor, Alerts and Fast  DISABLED        DISABLED        TYPICAL          NO

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics          DISABLED        DISABLED        TYPICAL          NO

23 rows selected.



SQL>alter system set statistics_level=typical;
System altered.
SQL>SELECT statistics_name,
         session_status,
         system_status,
         activation_level,
         session_settable
  FROM   v$statistics_level
order by statistics_name;

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History         DISABLED        DISABLED        TYPICAL          NO
Adaptive Thresholds Enabled    DISABLED        DISABLED        TYPICAL          NO
Automated Maintenance Tasks    DISABLED        DISABLED        TYPICAL          NO
Bind Data Capture              DISABLED        DISABLED        TYPICAL          NO
Buffer Cache Advice            DISABLED        DISABLED        TYPICAL          NO
Global Cache Statistics        DISABLED        DISABLED        TYPICAL          NO
Longops Statistics             DISABLED        DISABLED        TYPICAL          NO
MTTR Advice                    DISABLED        DISABLED        TYPICAL          NO
Modification Monitoring        DISABLED        DISABLED        TYPICAL          NO
PGA Advice                     DISABLED        DISABLED        TYPICAL          NO
Plan Execution Sampling        ENABLED         DISABLED        TYPICAL          YES

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics      DISABLED        DISABLED        ALL              YES
SQL Monitoring                 ENABLED         DISABLED        TYPICAL          YES
Segment Level Statistics       DISABLED        DISABLED        TYPICAL          NO
Shared Pool Advice             DISABLED        DISABLED        TYPICAL          NO
Streams Pool Advice            DISABLED        DISABLED        TYPICAL          NO
Threshold-based Alerts         DISABLED        DISABLED        TYPICAL          NO
Time Model Events              ENABLED         DISABLED        TYPICAL          YES
Timed OS Statistics            DISABLED        DISABLED        ALL              YES
Timed Statistics               ENABLED         ENABLED         TYPICAL          YES
Ultrafast Latch Statistics     DISABLED        DISABLED        TYPICAL          NO
Undo Advisor, Alerts and Fast  DISABLED        DISABLED        TYPICAL          NO

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics          DISABLED        DISABLED        TYPICAL          NO

23 rows selected.



SQL>alter system set statistics_level=all;
System altered.
SQL>SELECT statistics_name,
         session_status,
         system_status,
         activation_level,
         session_settable
  FROM   v$statistics_level
order by statistics_name;

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History         DISABLED        DISABLED        TYPICAL          NO
Adaptive Thresholds Enabled    DISABLED        DISABLED        TYPICAL          NO
Automated Maintenance Tasks    DISABLED        DISABLED        TYPICAL          NO
Bind Data Capture              DISABLED        DISABLED        TYPICAL          NO
Buffer Cache Advice            DISABLED        DISABLED        TYPICAL          NO
Global Cache Statistics        DISABLED        DISABLED        TYPICAL          NO
Longops Statistics             DISABLED        DISABLED        TYPICAL          NO
MTTR Advice                    DISABLED        DISABLED        TYPICAL          NO
Modification Monitoring        DISABLED        DISABLED        TYPICAL          NO
PGA Advice                     DISABLED        DISABLED        TYPICAL          NO
Plan Execution Sampling        ENABLED         DISABLED        TYPICAL          YES

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics      ENABLED         DISABLED        ALL              YES
SQL Monitoring                 ENABLED         DISABLED        TYPICAL          YES
Segment Level Statistics       DISABLED        DISABLED        TYPICAL          NO
Shared Pool Advice             DISABLED        DISABLED        TYPICAL          NO
Streams Pool Advice            DISABLED        DISABLED        TYPICAL          NO
Threshold-based Alerts         DISABLED        DISABLED        TYPICAL          NO
Time Model Events              ENABLED         DISABLED        TYPICAL          YES
Timed OS Statistics            ENABLED         DISABLED        ALL              YES
Timed Statistics               ENABLED         ENABLED         TYPICAL          YES
Ultrafast Latch Statistics     DISABLED        DISABLED        TYPICAL          NO
Undo Advisor, Alerts and Fast  DISABLED        DISABLED        TYPICAL          NO

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics          DISABLED        DISABLED        TYPICAL          NO

23 rows selected.

The gathering of statistics is performed each day and stored in a repository for 30 days.

Gathering statistics
Use the DBMS_STATS to retrieve statistics for the entire database, a schema or a specific table (DBMS_STATS.GATHER_*_STATS()).
For the database : DBMS_STATS.GATHER_DATABASE_STATS()
For schema : DBMS_STATS.GATHER_SCHEMA_STATS()
For a table : DBMS_STATS.GATHER_TABLE_STATS()

Use DBMS_STATS.GATHER_*_STATS() when 10% of the database, schema or table changes during the last 24 hours.

Removing statistics from the repository
Use the DBMS_STATS.DELETE_*_STATS() procedures.

Statistics preferences
The DBA can assign default or prefered parameters values for users that invoke the DBMS_STATS package.
The DBMS_STATS.SET_*_PREFS() procedures of the package allows the DBA to set preferences for the following parameters :
CASCADE
DEGREE
ESTIMATE_PERCENT
NO_INVALIDATE
METHOD_OPT
GRANULARITY
INCREMENTAL
PUBLISH
STALE_PERCENT

Preferences can be deleted using DBMS_STATS.DELETE_*_PREFS()
It is all possible to reset values to the initial setup using DBMS_STATS.RESET_PARAM_PREFS()

Remark : If preferences have been setted at a table level, setting preferences at the schema or database level overwritting preferences setted previously at the table level.

View statistics parameters preferences
Object prefrences can be retrieve from the DBA_TAB_STAT_PREFS table.
For preferences setted are a global level, use the DBMS_STATS.GET_PREFS() function of the DBMS_STATS package.

vendredi 19 août 2011

Oracle11g: Auditing features

For database created manually
Manually created database doesn't have the auditing feature activated by default.
The value of the initial parameter AUDIT_TRAIL is defined to NONE.

For database created using DBCA
For database created automatically through the DBCA utility, the audit feature is activated using the parameter AUDIT_TRAIL set to DB.
All audit information and activity is traced into a vue DBA_AUDIT_TRAIL.

Mandatory auditing information
Even if the auditing feature is disabled, Oracle traces some specific actions that are mandatory to ensure the database security.
Oracle traces all audit information in a directory that is defined through the AUDIT_FILE_DEST initialization parameter.

The default auditing options for Oracle 11g are seen in the following table:
Default audited priviledge
Alter any procedure
Alter any table
Alter database
Alter profile
Alter system
Alter user
Audit system
Create any library
Create any procedure
Create any table
Create external job
Create public database link
Create session
Create user
Create any job
Drop any table
Drop profile
Drop user
Drop any procedure
Exempt access policy
Grant any object privilege
Grant any privilege
Audit system
Grant any role

Default audited statement
System audit by access
Role by access

Audit levels
There are 5 levels of audit :

Mandatory auditing
Standard database audit
Data audit
Fine grained audit
SYSDBA & SYSOPER audit

Available values for AUDIT_TRAIL parameter
See here under all values available for the AUDIT_TRAIL parameter that defines how the audit feature will make traces :

NONE : Audit is not activated
OS : all audit information are redirected to a file where the location is defined through the AUDIT_FILE_DEST initialization parameter.
DB : all audit information are write in the DBA_AUDIT_TRAIL table defined under the SYS schema.
DB, EXTENDED : When extended is specified the SQL text, bind variables and handled columns are traced in the DBA_AUDIT_TRAIL table.
XML : all audit information are writen in xml files where the location is defined through the AUDIT_FILE_DEST initialization parameter. You can check instead the V$XML_AUDIT_TRAIL view to see all the content of all xml files managed by the audit trail feature.
XML, EXTENDED : When extended is specified the SQL text, bind variables and handled columns are added to the audit information.

Remark : The AUDIT_TRAIL parameter is a static database initialization parameter. The database need to be shutdowned and restarted after any modification of this parameter.

Audit trail views
See here under some important views :
DBA_AUDIT_TRAIL : view used when AUDIT_TRAIL set to DB or DB,EXTENDED
DBA_FGA_AUDIT_TRAIL : view used when fine grained audit activated

DBA_COMMON_AUDIT_TRAIL : Is the merge of the 2 previous views.

Audit options
There is 3 kinds of audit options:

Audit of SQL instructions: Trace all DDL instructions that affect a table (CREATE TABLE, ALTER TABLE, DROP TABLE)
Audit of system priviledges: Audit all system priviledge used by all users or by a specific user
Audit of object priviledges: Audit all object priviledges used by all users or a specific user

Audit trace could be generated when used (BY ACCESS) or could be grouped and generated at the session level (BY SESSION).

How to see audit information
Check the following views :
DBA_AUDIT_TRAIL : view used when AUDIT_TRAIL set to DB or DB,EXTENDED
DBA_FGA_AUDIT_TRAIL : view used when fine grained audit activated
DBA_COMMON_AUDIT_TRAIL : Is the merge of the 2 previous views.
DBA_AUDIT_OBJECT : View used when instruction options used
DBA_AUDIT_POLICIES :
DBA_AUDIT_SESSION :

How to retrieve activated audit options
Check the following views :
DBA_OBJ_AUDIT_OPTS : display object priviledge audited
DBA_PRIV_AUDIT_OPTS : display system priviledge audited
DBA_STMT_AUDIT_OPTS : display statement audited

Data based audit
The standard audit level register the fact that something has been added in a table or something has changed but it does not take care of the value of the columns that has changed. The Data base audit enhance the standard audit by taking into account and register the values of a table that has been modified.

To handle table modification, oracle register triggers to audited tables and use the value of the AUDIT_TRAIL & AUDIT_FILE_DEST initialization parameter to how and where to trace audit information.

when a table <table> is audited, Oracle create a table SYSTEM.AUDIT_<table> to store all audit information in relation the audited table.

Fine grained audit
Fine grained audit brings more flexibility than the detailed audit.
It is possible to specify columns to audit and define also a condition for auditing.
If the result of a specific request matches the audit condition then a trace is generated in the audit trail whatever the number of lines that match the condition.

SELECT, UPDATE,DELETE & MERGE are the statement handled by the FGA audit.

A procedure can be executed if a statement matches the audit condition, audit columns and the type of statement to audit.

New FGA audit feature, can be enabled using the DBMS_FGA package through the use of the DBMS_FGA.ADD_POLICY procedure.

DBMS_FGA.ADD_POLICY(
  object_schema => '...',
  object_name => '...',
  policy_name => '...',
  audit_condition => 'employee_id > 10',
  audit_column => 'EMP_ID, SALARY',
  handler_schema => 'audit_schema',
  handler_module => 'log_audit_proc',
  enable => TRUE,
  statement_types => 'SELECT, INSERT, UPDATE, DELETE'
);

FGA policy could be deleted using DBMS_FGA_DROP_POLICY
FGA policy could be enabled/disabled using DBMS_FGA.ENABLE_POLICY, DBMS_FGA.DISABLE_POLICY

SYSDBA & SYSOPER audit
The audit level is activated using the AUDIT_SYS_OPERATIONS parameter setted to TRUE.
The default value of the parameter is FALSE.

All audit traces a written in files located under the directory defined by the value of the AUDIT_FILE_DEST parameter.

Deleting audit information
For standard auditing, truncate the SYS.AUD$ table.
For fine grained auditing, truncate the SYS.FGA_LOG$ table

Some external References :
- good samples at http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
- Managment of audit trace @see : New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information [ID 731908.1] for oracle support

mercredi 10 août 2011

Oracle11g: How to configure a database to register dynamically a service in a listener that not use the default port

First you need to define the listener with a port that is not the default port by registering your listener configuration in the listener.ora file.

# listener.ora Network Configuration File: /opt/oracle/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1523))
  )

ADR_BASE_LISTENER = /opt/oracle

Run the listener using the following command:

>lsnrctl start

or using the srvctl command:

>srvctl start listener

Then you can update your database parameter in order to define the address of the listener:

Using sql ;
SQL>ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOl=TCP)(HOST=myhost)(PORT=1523))' SCOPE=BOTH;

If you listener is running on a different host then the database, you need to update the REMOTE_LISTER database parameter. This parameter will reference a network alias that must be declared in the tnsnames.ora file of the database.
For example, you can write in your tnsnames.ora file where is located your database :

# tnsnames.ora Network Configuration File: /opt/oracle/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

MY_REMOTE_ALIAS =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2)(PORT = 1523))
  )

and update the remote_listener database parameter in this way :

SQL>ALTER SYSTEM SET REMOTE_LISTENER='MY_REMOTE_ALIAS' SCOPE=BOTH;

The database will try to connect on host2 and port number 1523 to register dynamically all its services.

Remark:
Check the following situation :

Define a listener that has a name different than the default one (for example MY_LISTNER instead of LISTENER)

# listener.ora Network Configuration File: /opt/oracle/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

MY_LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1523))
  )

ADR_BASE_LISTENER = /opt/oracle

start the listener

>lsnrctl start MY_LISTENER

>srvctl start listener -l MY_LISTNER

Set the LOCAL_LISTENER parameter to its initial value (an empty string)

SQL> ALTER SYSTEM SET LOCAL_LISTENER='' SCOPE=BOTH;
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string

Start the default listener
```
>lsnrctl start
```

Then, you will see that the database register its configuration under the listener MY_LISTENER listening on port 1523 but also in the default listener LISTENER listening on port 1521. This is due to the fact that we do not update the LOCAL_LISTNER value !

Oracle11g: Dealing with dynamic service registration

Oracle11g provides a dynamic service registration.
Unlike static service registration, dynamic registration need less information in listerner.ora file and more configuration in database parameter.

listener.ora configuration
The listener.ora file will contain only the adress of the listener :

# listener.ora Network Configuration File: /opt/oracle/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.gemalto.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /opt/oracle

Database configuration
The SERVICE_NAMES database parameter contains a string that list all services that the database will register automaticatlly in the default listener.
This parameter goes along with the DB_DOMAIN parameter.

For example, if you define this parameters like here under :

SERVICE_NAMES = 'MY_BASE'

DB_DOMAIN = 'gemalto.com'

then the database will register the service 'MY_BASE.gemalto.com' in the default listener.

if DB_DOMAIN is defined using an empty string then the service will be 'MY_BASE'.

To define more then one services in the listener then you can write the following

SERVICE_NAMES='DB1, DB2,DB3'
DB_DOMAIN = 'gemalto.com'

in this way, three services will be registered dynamically in the listener : DB1.gemalto.com, DB2.gemalto.com and DB3.gemalto.com

To set the parameters you can use the following SQL syntax:

SQL>ALTER SYSTEM SET SERVICE_NAMES='MY_BASE,MY_PROD' scope=BOTH;

scope=BOTH indicates that the memory of the database will be altered and the spfile will be updated in the same time.

The DB_DOMAIN name is a static parameter and could not be modified dynamically.
You can try the following syntax

SQL>ALTER SYSTEM SET SERVICE_NAMES='MY_BASE,MY_PROD' scope=SPFILE DEFERRED

or modify your init<SID>.ora initialization file.
To be taken into account, restart your database.

Checking the listener

>lsnrctl status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.gemalto.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                10-AUG-2011 17:32:49
Uptime                    0 days 0 hr. 36 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/11.2.0/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/myhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.gemalto.com)(PORT=1521)))
Services Summary...
Service "MY_BASE" has 1 instance(s).
  Instance "INSTANCE", status READY, has 1 handler(s) for this service...
Service "MY_PROD" has 1 instance(s).
  Instance "INSTANCE", status READY, has 1 handler(s) for this service...
The command completed successfully

mardi 9 août 2011

Oracle11g : ASM mandatory parameters

An ASM instance can be configured through 74 parameters.
Only 8 parameters are required :

INSTANCE_TYPE = ASM
ASM_POWER_LIMIT = 1
ASM_DISKSTRING = '/dev/sda1','/dev/sda2','/dev/sdb*'
ASM_DISKGROUPS = DATA,FRA
ASM_PREFERRED_READ_FAILURE_GROUPS = DATA.FailGroup1
DIAGNOSTIC_DEST = /u01/app/oracle
LARGE_POOL_SIZE = 12M
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

The INSTANCE_TYPE is the only mandatory parameter !

ASM_POWER_LIMIT is used to define the rebalancing speed. Value is in range [0,11]
ASM_DISKSTRING is used for disk automatic discovery
ASM_DISKGROUPS defines all groups that will be monted automatically at startup
ASM_PREFERRED_READ_FAILURE_GROUPS defined the disk failure group that will be used in a cluster environment
DIAGNOSTIC_DEST defines the location of the ADR repository (Automatic Diagnostic Repository)
REMOTE_LOGIN_PASSWORDFILE check the availability of a password file. Default value is EXCLUSIVE.

Ant: Deploy an application over a Weblogic instance or cluster

Some ant stuff to help deployment of an application over a veblogic cluster or instance :

<project name="deployment tool">
    <!-- ********************************************************************************** -->
    <!-- Imports the properties files used for correct deployment                           -->
    <!-- ********************************************************************************** -->
    <property file="deploy.properties" />

    <!-- ********************************************************************************** -->
    <!-- Taks definitions                                                                   -->
    <!-- ********************************************************************************** -->
    <taskdef resource="net/sf/antcontrib/antcontrib.properties">
        <classpath>
            <pathelement location="${ant.lib.dir}/ant-contrib-1.0b3.jar" />
        </classpath>
    </taskdef>

    <taskdef name="wldeploy" classname="weblogic.ant.taskdefs.management.WLDeploy">
        <classpath>
            <pathelement path="${commonlibs.dir}/Storage/wlfullclient.jar" />
        </classpath>
    </taskdef>


    <!-- ********************************************************************************** -->
    <!-- deploying/undeploying module in application server                                 -->
    <!-- ********************************************************************************** -->
    <macrodef name="deploy-module">
       <attribute name="file" default="NOT_SET"/>
       <attribute name="wl.name" default="NOT_SET"/>
       <attribute name="wl.username" default="weblogic"/>
       <attribute name="wl.password" default="testpwd"/>
       <attribute name="wl.adminurl" default="t3://localhost:7001"/>
       <attribute name="wl.targets" default="NOT_SET"/>
       <sequential>
           <!-- Check that the admin server is running before deployment -->
           <property name="weblogic.console" value="http://${server-host}:${server-adminPort}/console"/>
           <waitfor maxwait="2" maxwaitunit="minute" checkevery="100" checkeveryunit="millisecond">
               <http url="${weblogic.console}" />
           </waitfor>
           <if>
               <not>
                   <http url="${weblogic.console}" />
               </not>
               <then>
                   <fail message="Admin Server on http://${server-host}:${server-adminPort} is not in running state. Unable to deploy ${final.ear.name}"/>
               </then>
               <else>
                   <echo message="_____Admin Server on http://${server-host}:${server-adminPort} is running. Starting deployment of ${final.ear.name}"/>
               </else>
           </if>
           <!-- starting the deployment -->
           <echo>_____Deploying file @{file} to @{wl.adminurl} on targets @{wl.targets} </echo>
           <if>
               <equals arg1="${server-host}" arg2="localhost"/>
               <then>
                   <property name="wl.upload" value="false"/>
               </then>
               <else>
                   <property name="wl.upload" value="true"/>
                   <echo>_____Uploading file @{file} to @{wl.adminurl} </echo>
               </else>
           </if>
           <if>
               <equals  arg1="@{wl.targets}" arg2="NOT_SET"/>
               <then>
                   <wldeploy action="deploy" source="@{file}" name="@{wl.name}" user="@{wl.username}" password="@{wl.password}" adminurl="@{wl.adminurl}" verbose="true" debug="true" upload="${wl.upload}"/>
               </then>
               <else>
                   <wldeploy action="deploy" source="@{file}" name="@{wl.name}" user="@{wl.username}" password="@{wl.password}" adminurl="@{wl.adminurl}" targets="@{wl.targets}" verbose="true" debug="true" upload="${wl.upload}"/>
               </else>
           </if>
       </sequential>
    </macrodef>

    <macrodef name="undeploy-module">
       <attribute name="wl.name" default="NOT_SET"/>
       <attribute name="wl.username" default="weblogic"/>
       <attribute name="wl.password" default="testpwd"/>
       <attribute name="wl.adminurl" default="t3://localhost:7001"/>
       <attribute name="wl.targets" default="NOT_SET"/>
       <sequential>
           <echo>_____Undeploying module @{wl.name} to @{wl.adminurl} from targets @{wl.targets} </echo>
           <if>
               <equals  arg1="@{wl.targets}" arg2="NOT_SET"/>
               <then>
                   <wldeploy action="undeploy" name="@{wl.name}" user="@{wl.username}" password="@{wl.password}" adminurl="@{wl.adminurl}" verbose="true" debug="true" failonerror="false"/>
               </then>
               <else>
                   <wldeploy action="undeploy" name="@{wl.name}" user="@{wl.username}" password="@{wl.password}" adminurl="@{wl.adminurl}" targets="@{wl.targets}" verbose="true" debug="true" failonerror="false"/>
               </else>
           </if>
       </sequential>
    </macrodef>

    <!-- ********************************************************************************** -->
    <!-- deploying/undeploying ear in application server                                    -->
    <!-- ********************************************************************************** -->
    <target name="deploy-ear">
        <!-- deploy now the module -->
        <deploy-module file="${final.module.name}"
                       wl.name="${project.name}"
                       wl.username="${serverWL-adminuser}"
                       wl.password="${serverWL-adminpasswd}"
                       wl.adminurl="t3://${server-host}:${server-adminPort}"
                       wl.targets="${cluster-name}"
                />
    </target>

    <target name="undeploy-ear">
        <undeploy-module wl.name="${project.name}"
                         wl.username="${serverWL-adminuser}"
                         wl.password="${serverWL-adminpasswd}"
                         wl.adminurl="t3://${server-host}:${server-adminPort}"
                         wl.targets="${cluster-name}"
                />
    </target>

</project>

and use a property file like this:

ant.lib.dir=D:/tools/apache-ant/lib

# Weblogic home dir and domain location
# Variable only used to start and stop the server when localhost is used
# in server location
weblogic.home.dir=D:/weblogic.10.3.4
weblogic.domain.dir=D:/weblogic.10.3.4/user_projects/domains/YuuWaa

# The command to run for starting and stoping web logic
start-admin-server=startWebLogic.cmd
stop-admin-server=stopWebLogic.cmd
start-managed-server=startManagedWebLogic.cmd
stop-managed-server=stopManagedWebLogic.cmd

# Cluste name where to deploy
cluster-name=

# host and port of the listening admin console
server-host=localhost
server-adminPort=7001

# User/Password used when connecting to the admin console of WLS
serverWL-adminuser=weblogic
serverWL-adminpasswd=testpwd

final.module.name=testEjb.ear

Ant : Starting/Stoping a weblogic instance or cluster

Here some ant script used to start/stop a weblogic instance or cluster

<!-- ********************************************************************************** -->
    <!-- Taks definitions                                                                   -->
    <!-- ********************************************************************************** -->
    <taskdef resource="net/sf/antcontrib/antcontrib.properties">
        <classpath>
            <pathelement location="${ant.lib.dir}/ant-contrib-1.0b3.jar" />
        </classpath>
    </taskdef>

    <!-- ********************************************************************************** -->
    <!-- startWeblogic                                                                      --v
    <!-- For remote access using ssh define the properties :                                -->
    <!--       server-user                                                                  -->
    <!--       server-passwd                                                                -->
    <!-- ********************************************************************************** -->
    <target name="start-weblogic" description="Start weblogic">
        <if>
         <equals arg1="${server-host}" arg2="localhost" />
         <then>
             <echo message="__________  Check Weblogic" />
             <if>
                 <available file="${weblogic.home.dir}/wlserver_10.3/server/lib/weblogic.jar"/>
                 <then>
                     <echo message="Weblogic server found" />
                 </then>
                 <else>
                     <fail message="No Weblogic server has been found" />
                 </else>
             </if>

             <echo message="__________ Check if libraries correctly installed" />

             <echo message="__________ Stop Weblogic server if started" />
             <antcall target="stop-weblogic" inheritrefs="true" />

             <echo message="__________ Start Weblogic" />

             <property name="weblogic.console" value="http://${server-host}:${server-adminPort}/console"/>
             <if>
                 <not>
                     <http url="${weblogic.console}" />
                 </not>
                 <then>
                     <echo message="Weblogic is not running" />
                     <echo message="Starting Weblogic server..." />
                     <forget>
                         <exec executable="${weblogic.domain.dir}/bin/${start-admin-server}" dir="${weblogic.domain.dir}/bin" />
                     </forget>
                     <waitfor maxwait="2" maxwaitunit="minute" checkevery="100" checkeveryunit="millisecond">
                         <http url="${weblogic.console}" />
                     </waitfor>
                     <echo message="Weblogic started" />
                 </then>
                 <else>
                     <echo message="Weblogic server is already running" />
                 </else>
             </if>
         </then>
         <else>
             <sshexec host="${server-host}" username="${server-user}" password="${server-passwd}" command="${weblogic.domain.dir}/bin/${start-managed-server}" trust="yes" failonerror="no" />
             <sleep seconds="60" />
         </else>
        </if>
	</target>

    <!-- ********************************************************************************** -->
    <!-- stopWeblogic                                                                       -->
    <!-- For remote access using ssh define the properties :                                -->
    <!--       server-user                                                                  -->
    <!--       server-passwd                                                                -->
    <!-- ********************************************************************************** -->
	<target name="stop-weblogic" description="Stop weblogic">
        <if>
           <equals arg1="${server-host}" arg2="localhost" />
           <then>
               <echo message="__________ Stop Weblogic" />
               <property name="weblogic.console" value="http://${server-host}:${server-adminPort}/console"/>

               <if>
                   <http url="${weblogic.console}" />
                   <then>
                       <echo message="Weblogic is running" />
                       <echo message="Stopping Weblogic server..." />
                       <forget>
                           <exec executable="${weblogic.domain.dir}/bin/${stop-admin-server}" dir="${weblogic.domain.dir}/bin" />
                       </forget>
                       <waitfor maxwait="2" maxwaitunit="minute" checkevery="100" checkeveryunit="millisecond">
                           <not>
                               <http url="${weblogic.console}" />
                           </not>
                       </waitfor>
                       <echo message="Weblogic stopped" />
                   </then>
                   <else>
                       <echo message="Weblogic server is already stopped" />
                   </else>
               </if>
           </then>
           <else>
		       <sshexec host="${server-host}" username="${server-user}" password="${server-passwd}" command="${weblogic.domain.dir}/bin/${stop-managed-server}" trust="yes" failonerror="no" />
		       <sleep seconds="20" />
           </else>
        </if>
	</target>

Define also some properties :

# Weblogic home dir and domain location
# Variable only used to start and stop the server when localhost is used
# in server location
weblogic.home.dir=D:/weblogic.10.3.4
weblogic.domain.dir=D:/weblogic.10.3.4/user_projects/domains/YuuWaa

# The command to run for starting and stoping web logic
start-admin-server=startWebLogic.cmd
stop-admin-server=stopWebLogic.cmd
start-managed-server=startManagedWebLogic.cmd
stop-managed-server=stopManagedWebLogic.cmd

# Cluste name where to deploy
cluster-name=

# host and port of the listening admin console
server-host=localhost
server-adminPort=7001

# User/Password used when connecting to the admin console of WLS
serverWL-adminuser=weblogic
serverWL-adminpasswd=testpwd

Oracle11g : How to obtain the name of all views defined in the database

To retrieve the name of all views in database, use the following command under SqlPlus :

SQL>select * from V$FIXED_TABLE;

Oracle11g : How to manage an instance using srvctl

Under Oracle11g you can use the srvctl executable to manage all components of your database:

To start a specific database

>srvctl start database -d your_db_sid -o open

To stop the database

>srvctl stop database -d your_db_sid -o immediate

To obtain the status of a database

>srvctl status database -d your_db_sid

To start a listener

>srvctl start listener -l your_listener_name

To stop a listener

>srvctl stop listener -l your_listener_name

To obtain the status of a listener

>srvctl status listener -l your_listener_name

To start an asm instance

>srvctl start asm -n node_name -i asm_instance_name -o oracle_home

where :
-n node_name Node name.
-i asm_instance_name The ASM instance name.
-o oracle_home Oracle home for the database.

for example :

>srvctl start asm -i asm1 -o /opt/oracle/11.2.0

To stop an asm instance

>srvctl stop asm -i asm_instance_name

To obtain the status of an asm instance

>srvctl status asm -i asm_instance_name

Oracle11g: find trace files localisation

To display the localisation of all trace files under Oracle11g, use the following command under sqlplus

SQL> select * from v$diag_info;

The result will be something like this:

INST_ID NAME                      VALUE
---------- ------------------------- --------------------------------------------------
1 Diag Enabled              TRUE
1 ADR Base                  /database/DB/sgbd/DB/bdump
1 ADR Home                  /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB
1 Diag Trace                /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/trace
1 Diag Alert                /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/alert
1 Diag Incident             /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/incident
1 Diag Cdump                /database/DB/sgbd/DB/cdump
1 Health Monitor            /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/hm

1 Default Trace File        /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/trace/DB_ora_7534.trc
1 Active Problem Count      0
1 Active Incident Count     0

How to define trace location for an Oracle instance

The root directory of the ADR referential is defined with the value of the DIAGNOSTIC_DEST initialization parameter.

If the parameter DIAGNOSTIC_DEST is not defined :
If the DIAGNOSTIC_DEST initialization parameter is not set and if the ORACLE_BASE is defined then the DIAGNOSTIC_DEST takes the value of the ORACLE_BASE variable. In this way, trace files are located under the directory :

$ORACLE_BASE/diag/rdbms/<database_id>/<instance_id>/trace

If ORACLE_BASE is not defined and ORACLE_HOME is defined then the DIAGNOSTIC_DEST takes the value of ORACLE_HOME/log. In this way trace files are located under :

$ORACLE_HOME/log/diag/rdbms/<database_id>/<instance_id>/trace

If DIAGNOSTIC_DEST is defined :
Trace files are located using the DIAGNOSTIC_DEST database parameter.
Oracle use this parameter in order to define the full directory path of all trace files like this :

DIAGNOSTIC_DEST/diag/<instance_type>/<database_id>/<instance_id>/

For example, if the instance name is ORCL and the value of the DIAGNOSTIC_DEST parameter is /opt/oracle then the path will be /opt/oracle/diag/rdbms/orcl/ORCL.
The trace directory will be /opt/oracle/diag/rdbms/orcl/ORCL/trace.

Alert log location
Alert log in text format is defined under the trace directory of the ADR referential :

DIAGNOSTIC_DEST/diag/<instance_type>/<database_id>/<instance_id>/trace

Alert log in xml format is defined under the alert directory of the ADR referential :

DIAGNOSTIC_DEST/diag/<instance_type>/<database_id>/<instance_id>/alert

The alert log file is commonly named log.xml.

Oracle : adrci - How to display alerts...

To display trace and alerts from a 11g database use the adrci tools in $ORACLE_HOME/bin

>adrci

and after ask the alert log

adrci> show alert -tail -f

and other way to display alerts under adrci could be

>adrci exec="show alert -tail -f"

To display alerts generated by the listener you can use also the adrci tool:

>adrci
adrci>set homepath diag/tnslsnr
adrci>show alert -tail 200 -f

To display incident of the orcl database

$>adrci
adrci> set homepath diag/rdbms/orcl/orcl
adrci> show incident

note: To display adrci policy :

adrci> show control

note :To package an incident see use of ips command with adrci tool.

note : To visualize the report of health monitor task use

adrci>show hm_run
----------------------------------------------------------                                                                                                    RUN_ID                       11081
RUN_NAME                     HM_RUN_11081
CHECK_NAME                   Cross Check
NAME_ID                      2
MODE                         2
START_TIME                   2007-04-13 03:20:31.161396 -07:00
RESUME_TIME
END_TIME                     2007-04-13 03:20:37.903984 -07:00
MODIFIED_TIME                2007-04-17 01:16:37.106344 -07:00
TIMEOUT                      0
FLAGS                        0
STATUS                       5
SRC_INCIDENT_ID              0
NUM_INCIDENTS                0
ERR_NUMBER                   0
REPORT_FILE

adrci> create report hm_run HM_RUN_11081
adrci> show report hm_run HM_RUN_11081

Oracle 11g & J2EE Samples