Which oracle version has what Oracle Compression:

8i:
Can's:
IOT compression
Unique and non-Unique index compression

Can'ts:
No table compression
No Materialized Views compression
No Bitmap index compression
No LOB compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit


9i:
Can's:
Table compression
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
Adding a column
Dropping a column
No table compression with more than 255 columns
No Bitmap index compression
No LOB compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit


10g:
Can's:
Adding a column
Table compression
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
Dropping a column
No table compression with more than 255 columns
No LOB compression (only with utl_compress package)
No Bitmap index compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit


11g:
Can's:
Dropping a column
Adding a column
Table compression (Row-level compression in a block)
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
No LOB compression (only with utl_compress package)
No Bitmap index compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit


What is Oracle Advanced Compression in 11g?

OLTP compression (Normal DML's also included)
Compression for Unstructured data like documents, spreadsheets and XML files
RMAN compression
Data Pump DATA compression


How to test the performance impact between compress and no compress tables?

CREATE TABLE comp_test_1 (
id NUMBER(10),
data VARCHAR2(50)
)
TABLESPACE users;

CREATE TABLE comp_test_2 (
id NUMBER(10),
data VARCHAR2(50)
)
TABLESPACE users
compress;


SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
l_loops NUMBER := 100000;
l_data VARCHAR2(50);
l_start NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE comp_test_1';
EXECUTE IMMEDIATE 'TRUNCATE TABLE comp_test_2';

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
INSERT INTO comp_test_1 (id, data)
VALUES (i, 'Data for ' || i);
END LOOP;
DBMS_OUTPUT.put_line('Normal Insert : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
INSERT INTO comp_test_2 (id, data)
VALUES (i, 'Data for ' || i);
END LOOP;
DBMS_OUTPUT.put_line('Compressed Insert: ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
UPDATE comp_test_1
set data = 'VDUMPA'
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Normal Update : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
UPDATE comp_test_2
set data = 'VDUMPA'
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Uncompressed Update : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
SELECT data
INTO l_data
FROM comp_test_1
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Normal Query : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
SELECT data
INTO l_data
FROM comp_test_2
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Uncompressed Query : ' || (DBMS_UTILITY.get_time - l_start));
END;
/

How to delete jobs stay in 'Running' status in OEM Grid Control?

Problem Description:
Job stays in 'Running' status, never complete and can't delete from OEM Grid Control.


1. How to find the job_id:

connect sysman@poem
select job_id
from mgmt_job
where job_name = 'BACKUP_ODS_ARCH_2HRS'
and job_owner = 'SYSMAN';


2. How to stop running job:

connect sysman@poem
exec mgmt_job_engine.stop_all_executions_with_id('459582F20C8B6AF4E0403E0ACECC4DCB',TRUE);
commit;

3. How to delete the job:

connect sysman@poem
exec mgmt_job_engine.delete_job('BACKUP_ODS_ARCH_2HRS','SYSMAN');
commit;


Reference:
Note:430626.1
Note:404211.1

ASM access through ftp and html using XDB from Oracle 10gR2:

1. Set up the Oracle XML DB access to the ASM folders:

sqlplus / as sysdba
@?/rdbms/admin/catxdbdbca 7777 8080

Note:
a. This script takes two parameters (port numbers) for the FTP and HTTP services respectively.


2. Look for the datafiles you want to ftp to other DB:

SQL> select file_name from dba_data_files
2 where tablespace_name = 'USERS';

FILE_NAME
-----------------------------------------------
+ORADB_DBF_DG/oradb/datafile/users.272.583155107

SQL> create tablespace TTS datafile '+ORADB_DBF_DG/oradb/datafile/tts_01.dbf' SIZE 50M;

Tablespace created.

SQL> select file_name from dba_data_files
2 where tablespace_name = 'TTS';

FILE_NAME
--------------------------------------
+ORADB_DBF_DG/oradb/datafile/tts_01.dbf

SQL> alter tablespace tts read only;

Tablespace altered.

SQL> exit


3. How do I access ASM files using ftp:


From the local PC:
C:\ASM_FTP_TEST>ftp
ftp> open atlxd215 7777
Connected to atlxd215.
220- atlxd215
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 atlxd215 FTP Server (Oracle XML DB/Oracle Database) ready.
User (atlxd215:(none)): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
ftp> bye


From the server:
atlxd215 | ORADB | /sys
> ftp
ftp> open atlxd215 7777
Connected to atlxd215.
220- atlxd215
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 atlxd215 FTP Server (Oracle XML DB/Oracle Database) ready.
530 Please login with USER and PASS.
530 Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (atlxd215:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> bye
221 QUIT Goodbye.
atlxd215 | ORADB | /sys

Note's:
a. All the asm diskgroup directories should be under /sys/asm directory.
b. Use system DB account to login


4. How to access from the browser:

connect / as sysdba
execute dbms_xdb.sethttpport(8080);
execute dbms_xdb.setftpport(7777);
commit;


for FTP type the url : ftp://atlxd215:7777/
for HTTP type the url: http://atlxd215:8080/

Sample Automated Table Compression procedures:

1. Procedure to compress partition table:

CREATE OR REPLACE procedure compress_tab_par
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

DBMS_OUTPUT.put_line('Table Partitions compression for ' || own_name || '.' || tab_name || ' started...');
DBMS_OUTPUT.put_line(' ');

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Make Indexes partitions Unusable */
begin
for x1 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name
and index_type = 'BITMAP')
and status = 'USABLE')

loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY PARTITION '||x1.partition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index partitions Unusable COMPLETED.');


/* Step2 - Alter table Partitions to compress */
begin
for x2 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x2.table_name||' MODIFY PARTITION '||x2.partition_name||' COMPRESS';
end loop;
end;

DBMS_OUTPUT.put_line(' Modifying Table partitions to compress COMPLETED.');



/* Step3 - Table partitions compress */
begin
for x3 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE PARTITION '||x3.partition_name||' COMPRESS NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Table partitions compress COMPLETED.');


/* Step4 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name)
and status = 'UNUSABLE')
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD PARTITION '||x4.partition_name||' PARALLEL 8 NOLOGGING';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');


/* Step5 - Analyze the table and indexes */
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 8);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');


select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');
DBMS_OUTPUT.put_line(' NOTE: don''t forget to resize the datafiles !!!!!!!!!!');
DBMS_OUTPUT.put_line('Table Partitions compression for ' || own_name || '.' || tab_name || ' completed.');

EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end compress_tab_par;
/


2. Procedure to compress Subpartition table:

CREATE OR REPLACE procedure compress_tab_subpar
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Making Indexes partitions Unusable */
begin
for x1 in ( select index_name, subpartition_name
from user_ind_subpartitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name))
loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY SUBPARTITION '||x1.subpartition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index subpartitions Unusable COMPLETED.');


/* Step2 - Alter table to compress */
execute immediate 'ALTER TABLE '||tab_name||' COMPRESS';

DBMS_OUTPUT.put_line(' Altering the table to compress has been COMPLETED.');


/* Step3 - Modifying table partitions compress */
begin
for x2 in ( select table_name, partition_name
from user_tab_subpartitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x2.table_name||' MODIFY PARTITION '||x2.partition_name||' COMPRESS NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Modifying table partitions to compress COMPLETED.');


/* Step4 - Table subpartitions compress */
begin
for x3 in ( select table_name, subpartition_name
from user_tab_subpartitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE SUBPARTITION '||x3.subpartition_name||' NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Table subpartitions compress COMPLETED.');


/* Step5 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, subpartition_name
from user_ind_subpartitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name))
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD SUBPARTITION '||x4.subpartition_name||' PARALLEL 8';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');


/* Step6 - Analyze the table and indexes*/
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 8);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');


select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');
DBMS_OUTPUT.put_line(' Note: Hai don''t forget to resize the datafiles !!!!!!!!!!');


EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end compress_tab_subpar;
/


3. Procedure to Back out compressed partition table:

CREATE OR REPLACE procedure EDW.nocompress_tab_par
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Alter table Partitions to NOcompress */
begin
for x3 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE PARTITION '||x3.partition_name||' NOCOMPRESS';
end loop;
end;

DBMS_OUTPUT.put_line(' Table partitions NOcompress COMPLETED.');


/* Step2 - Make Indexes partitions Unusable */
begin
for x1 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name
and index_type = 'BITMAP')
and status = 'USABLE')

loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY PARTITION '||x1.partition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index partitions Unusable COMPLETED.');


/* Step3 - Alter table to NOcompress */
execute immediate 'ALTER TABLE '||tab_name||' NOCOMPRESS';

DBMS_OUTPUT.put_line(' Altering the table to NOcompress has been COMPLETED.');


/* Step4 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name)
and status = 'UNUSABLE')
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD PARTITION '||x4.partition_name||' PARALLEL 8 NOLOGGING';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');


/* Step5 - Analyze the table and indexes */
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 4);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');


select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');

EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end nocompress_tab_par;
/

ASM SAN migration Case Study:

Problem:
I need to remove 3 old SAN disks(size of 150 G each) from my ASM database without any down time.

Server, OS and DB details:
OS: RHEL 4 Update 4 (64 bit)
DB: 10.2.0.3.0
Server: DELL PowerEdgeTM 2950(16G Memory, 4 CPU's(Intel(R) Xeon(R) CPU 5160 @ 3.00GHz))


Procedure:
1. Make sure you have free disks available to add to the ASM diskgroup, before dropping the disk's:

On ASM instance:
sqlplus / as sysdba

set linesize 200;
col path format a20;

select NAME, PATH, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, TOTAL_MB, FREE_MB
from v$asm_disk;


2. Add new ASM disks to the diskgroup:

alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1521';
alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1522';
alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1523';

alter diskgroup ORADB_DATA01_DG REBALANCE POWER 10; or
alter system set asm_power_limit=10 scope=memory;

-- Check the rebalance operation
select * from v$asm_operation;


Time taken to rebalance the diskgroup: app. 90 min.


3. Identify the disks to drop from the diskgroup:

set linesize 200;
col path format a20;

select NAME, PATH, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, TOTAL_MB, FREE_MB
from v$asm_disk;

ALTER DISKGROUP ORADB_DATA01_DG DROP DISK B_1530_1091;
ALTER DISKGROUP ORADB_DATA01_DG DROP DISK C_1530_1090;
ALTER DISKGROUP ORADB_DATA01_DG DROP DISK D_1530_1081;

alter diskgroup ORADB_DATA01_DG REBALANCE POWER 10; or
alter system set asm_power_limit=10 scope=memory;

-- Check the rebalance operation
select * from v$asm_operation;


Time taken to rebalance the diskgroup: app. 90 min.


4. Physically removing the disks from the server:

as root only!!!

/etc/init.d/oracleasm deletedisk B_1530_1091
/etc/init.d/oracleasm deletedisk C_1530_1090
/etc/init.d/oracleasm deletedisk D_1530_1081


How to find mapping of ASM disks to Physical Devices?

a.
atlxd215 | +ASM | /dev
> /etc/init.d/oracleasm querydisk H_1253_1521
Disk "H_1253_1521" is a valid ASM disk on device [120, 113]

b.
atlxd215 | +ASM | /dev
> ls -l /dev | grep 120 | grep 113
brwxrwx--- 1 oracle dba 120, 113 Oct 1 10:55 emcpowerh1

[or]

atlxd215 | +ASM | /dev
> cd oracleasm
atlxd215 | +ASM | /dev/oracleasm
> cd disks

atlxd215 | +ASM | /dev/oracleasm/disks
> ls -lt
total 0
brw-rw---- 1 oracle dba 120, 177 Oct 1 11:04 L_1253_1561
brw-rw---- 1 oracle dba 120, 161 Oct 1 11:04 K_1253_1560
brw-rw---- 1 oracle dba 120, 145 Oct 1 11:04 J_1253_1541
brw-rw---- 1 oracle dba 120, 129 Oct 1 11:03 I_1253_1540
brw-rw---- 1 oracle dba 120, 113 Oct 1 11:03 H_1253_1521
brw-rw---- 1 oracle dba 120, 97 Oct 1 11:03 G_1253_1520
brw-rw---- 1 oracle dba 120, 81 Oct 1 11:03 F_1253_1501
brw-rw---- 1 oracle dba 120, 65 Oct 1 10:59 E_1253_1500

c.
If you are using multi-path, you will need an additional step to map the physical device to the multi-path device:

as root only!!!

# /sbin/powermt display dev=emcpowerh1