Thursday, January 10, 2008

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/

Tuesday, January 8, 2008

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

Friday, January 4, 2008

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