ORA-39213: Metadata processing is not available
Cause: The Data Pump could not use the Metadata API.
Typically, this is caused by the XSL stylesheets not being set up properly.
Action:
Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
to reload the stylesheets.
Wednesday, February 23, 2011
Monday, February 7, 2011
Killing Oracle Sessions
Make sure you know what you are doing There are a number of ways to kill rogue sessions both within Oracle and externally.
Identify the Session to be Killed
ALTER SYSTEM KILL SESSION
ALTER SYSTEM DISCONNECT SESSION
The Windows Approach
The UNIX Approach
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the V$SESSION or GV$SESSION view as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)
1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)
The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.
ALTER SYSTEM DISCONNECT SESSION
Oracle 11g introduced the ALTER SYSTEM DISCONNECT SESSION syntax as a new method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
SQL>
The inclusion of this command in 11g means you should no longer need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.
Identify the Session to be Killed
ALTER SYSTEM KILL SESSION
ALTER SYSTEM DISCONNECT SESSION
The Windows Approach
The UNIX Approach
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the V$SESSION or GV$SESSION view as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)
1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)
The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.
ALTER SYSTEM DISCONNECT SESSION
Oracle 11g introduced the ALTER SYSTEM DISCONNECT SESSION syntax as a new method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
SQL>
The inclusion of this command in 11g means you should no longer need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.
Thursday, January 27, 2011
flashback tables
to flashback update
select * from v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
begin
dbms_logmnr.start_logmnr
(
starttime => '27-JAN-2011 11:25:10',
endtime => '27-JAN-2011 11:28:35',
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/
select seg_name ,seg_owner ,username, xid , operation , sql_redo
from v$logmnr_contents
where seg_owner = 'CCARE'
and username = 'DBAHAKKI';
--where seg_name like 'ACC' ;
-- from sys user
execute dbms_logmnr.end_logmnr;
begin
dbms_flashback.TRANSACTION_BACKOUT
(
numtxns => 1 ,
xids => xid_array('01001900A70C0000'),
options => DBMS_FLASHBACK.CASCADE
);
end;
/
--to flashback delete
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
insert into table_name
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
SQL>commit;
select * from v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
begin
dbms_logmnr.start_logmnr
(
starttime => '27-JAN-2011 11:25:10',
endtime => '27-JAN-2011 11:28:35',
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/
select seg_name ,seg_owner ,username, xid , operation , sql_redo
from v$logmnr_contents
where seg_owner = 'CCARE'
and username = 'DBAHAKKI';
--where seg_name like 'ACC' ;
-- from sys user
execute dbms_logmnr.end_logmnr;
begin
dbms_flashback.TRANSACTION_BACKOUT
(
numtxns => 1 ,
xids => xid_array('01001900A70C0000'),
options => DBMS_FLASHBACK.CASCADE
);
end;
/
--to flashback delete
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
insert into table_name
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
SQL>commit;
Monday, January 17, 2011
how to find what is locking my table
1-
SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc
2-
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id
3-
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object,object_type
, decode( l.block,0, 'Not Blocking',1, 'Blocking',2, 'Global') status
, decode(v.locked_mode,0, 'None',1, 'Null',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive'
, TO_CHAR(lmode)) mode_held
from v$locked_object v, dba_objects d, v$lock l, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username, session_id
to kill the session
ALTER SYSTEM KILL SESSION 'sid,serial#';
SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc
2-
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id
3-
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object,object_type
, decode( l.block,0, 'Not Blocking',1, 'Blocking',2, 'Global') status
, decode(v.locked_mode,0, 'None',1, 'Null',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive'
, TO_CHAR(lmode)) mode_held
from v$locked_object v, dba_objects d, v$lock l, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username, session_id
to kill the session
ALTER SYSTEM KILL SESSION 'sid,serial#';
Tuesday, January 4, 2011
Case Sensitive Passwords in Oracle Database 11g
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.
SQL>
Monday, January 3, 2011
Temporary Tablespace Groups
Temporary Tablespace Groups
Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:
-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;
-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;The tablespaces assigned to a group can be viewed using:
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2
2 rows selected.Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:
-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;
-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;A tablespace can be removed from a group using:
ALTER TABLESPACE temp2 TABLESPACE GROUP '';
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
1 row selected.There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.
-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';
-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;
no rows selected
Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:
-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;
-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;The tablespaces assigned to a group can be viewed using:
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2
2 rows selected.Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:
-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;
-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;A tablespace can be removed from a group using:
ALTER TABLESPACE temp2 TABLESPACE GROUP '';
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
1 row selected.There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.
-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';
-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;
no rows selected
Wednesday, December 29, 2010
Kill Job in oracle expdp utility
When you are inside the session that started the expdp,
then just ctrl+C will get you to the
EXPDP> prompt from where you can issue KILL_JOB command to kill the session you are in.
Issue help for more commands in that control utility.
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.
After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because, the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach to the job by typing the following command
$expdp system/oracle ATTACH=SYS_EXPORT_SCHEMA_01
expdp backup ATTACH=SYS_EXPORT_SCHEMA_16
After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode and restart the myfulljob job.
Export> CONTINUE_CLIENT
select * from DBA_DATAPUMP_JOBS;
then just ctrl+C will get you to the
EXPDP> prompt from where you can issue KILL_JOB command to kill the session you are in.
Issue help for more commands in that control utility.
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.
After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because, the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach to the job by typing the following command
$expdp system/oracle ATTACH=SYS_EXPORT_SCHEMA_01
expdp backup ATTACH=SYS_EXPORT_SCHEMA_16
After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode and restart the myfulljob job.
Export> CONTINUE_CLIENT
select * from DBA_DATAPUMP_JOBS;
Sunday, December 26, 2010
Tuesday, December 14, 2010
recreate emctl
#emca -repos recreate
Database SID: bossarc
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
#emca -deconfig dbcontrol db
#emca -config dbcontrol db
Database SID: bossarc
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
#emca -deconfig dbcontrol db
#emca -config dbcontrol db
session browser
SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.port,
s.terminal, UPPER (s.program) program, s.TYPE, s.sql_address,
s.sql_hash_value, s.sql_id, s.sql_child_number, s.sql_exec_start,
s.sql_exec_id, s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.top_level_call#, s.logon_time, s.last_call_et,
s.pdml_enabled, s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.final_blocking_session_status, s.final_blocking_instance,
s.final_blocking_session, s.seq#, s.event#, s.event, s.p1text, s.p1,
s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw,
s.wait_class_id, s.wait_class#, s.wait_class, s.wait_time,
s.seconds_in_wait, s.state, s.wait_time_micro,
s.time_remaining_micro, s.time_since_last_wait_micro, s.service_name,
s.sql_trace, s.sql_trace_waits, s.sql_trace_binds,
s.sql_trace_plan_stats, s.session_edition_id, s.creator_addr,
s.creator_serial#, s.ecid
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND')
)
ORDER BY "USERNAME", ownerid
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.port,
s.terminal, UPPER (s.program) program, s.TYPE, s.sql_address,
s.sql_hash_value, s.sql_id, s.sql_child_number, s.sql_exec_start,
s.sql_exec_id, s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.top_level_call#, s.logon_time, s.last_call_et,
s.pdml_enabled, s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.final_blocking_session_status, s.final_blocking_instance,
s.final_blocking_session, s.seq#, s.event#, s.event, s.p1text, s.p1,
s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw,
s.wait_class_id, s.wait_class#, s.wait_class, s.wait_time,
s.seconds_in_wait, s.state, s.wait_time_micro,
s.time_remaining_micro, s.time_since_last_wait_micro, s.service_name,
s.sql_trace, s.sql_trace_waits, s.sql_trace_binds,
s.sql_trace_plan_stats, s.session_edition_id, s.creator_addr,
s.creator_serial#, s.ecid
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND')
)
ORDER BY "USERNAME", ownerid
Monday, December 13, 2010
Wednesday, December 8, 2010
Monday, November 15, 2010
Database Migration with Unix-pipe and Export/Import Table of Contents
Database Migration with Unix-pipe and Export/Import Table of Contents
A. Introduction...............................
B. Required utilities.........................
1. What is Unix Pipe?.........................
2. What is import/export ?....................
3. How fast a table is imported?..............
C. Migration Scripts..........................
D. What I did to finish in 5 Hours............
E. Contact Information........................
A. Introduction Migration is the process of transforming from one version to another.
Export/Import is only available utility to migrate the database across the platform.
Migration of production database across the platform using export/import needs downtime and this effort becomes critical for those companies
whose production database is 24x7 and can’t afford long duration of production downtime.
The aim of this document is to share the technical knowledge of using export/import with Unix pipe,
which needs minimal downtime.
Before finalizing this scripts I tested all combinations of export/import.
The results are based on my experience, which may vary and depends upon server capacity.
It took a little time to fix the scripts but we migrated the production database of 160 GB in 5 hours.
B. Required utilities Migration scripts need following three utilities.
Unix pipe
Oracle export (exp)
Oracle import (imp)
1. What is Unix Pipe?
In Unix, apipe is a unidirectional, stream communication abstraction.
One process writes to the ``write end'' of the pipe,
and a second process reads from the ``read end'' of the pipe.
Its very useful utility and I have used in the scripts .
2. What is import/export ?
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery.
They are also used to move Oracle data from one machine,
database or schema to another.
3. How fast a table is imported?
If you need to monitor how fast rows are imported from a running import job, try following sql :
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-ddhh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
C. Migration Scripts Save your export parameter file as exp_ALL.par
Save your import parameter file as imp_ALL.par
Save the following scripts as remote_exp_imp_all.ksh
Run the scripts from unix prompt as
remote_exp_imp_all.ksh > remote_exp_imp_all.out 2>&1 &
--------------------------------Cut the scripts ----------------------------------------------
#!/bin/ksh
## Assumption
# REMOTE SERVER is server on which existing production database is running
# LOCAL SERVER is new server on which database will be imported.
# In this case remote server is saqhdp01 and database name is C4PR
# All scripts are executed from LOCAL SERVER.
# Only export parameter file is required on REMOTE SERVER
# exp_ALL.par is export parameter file
# imp_ALL.par is import parameter file
set -xv . $HOME/C4PR.env
# C4PR.env sets Database environment TIME_START=`date +%T` touch start_ALL R_SERVER=saqhdp01
# Remote database server REMOTE_HOME=/u333/app
# Location on remote server L_PIPE=$HOME/upgrade/imp_pipe_ALL
# Local Unix Pipe R_PIPE=$REMOTE_HOME/upgrade/exp_pipe_ALL
# Remote Unix Pipe
# Create the local and remote pipes and change the permissions
# Remove the files prior rsh $R_SERVER "/bin/rm $R_PIPE" /usr/bin/rm $L_PIPE rsh $R_SERVER "/etc/mknod $R_PIPE p;
#chmod 777 $R_PIPE" /usr/sbin/mknod $L_PIPE p
# Start the import from the pipe in the background
#imp parfile=$HOME/upgrade/imp_ALL.par > $HOME/upgrade/imp_ALL.out 2>&1 &
# Start the export on the remote server in the background
#rsh $R_SERVER ". /u333/app/upgrade/C4PR.env;
exp parfile=/u333/app/upgrade/exp_ALL.par > /u333/app/upgrade/exp_ALL.out 2>&1" > ALL.out 2>&1 &
# cat (concatenate) the export pipe to the import pipe on the remote server
#rsh $R_SERVER "cat < $R_PIPE " | cat > $L_PIPE TIME_END=`date +%T` touch $HOME/upgrade/end_ALL
# Remove the local and remote pipes
rsh $R_SERVER "rm $R_PIPE"
rm $L_PIPE
mailx -s "Start/End time for piped export/import " <
D. What I did to finish in 5 Hours In order to achieve it, I performed various iterations.
At the end of each iteration results were analyzed and issues (problematic area) were resolved.
Also there were support from internal Engineering, Operations, DBA and management.
Following points were considered in final iterations .
• Gather the statistics from various iterations •
Create a blank database on RAID-1 file systems.
Since import is write intensive operations, RAID-1 takes at least 30% less time.
• Distributes the data files based on I/O pattern. STATSCPACK results are helpful
• Validate the parameters
disk_asynch_io = false
db_writer_processes = (No of cpu/2 )
_system_trig_enabled=FALSE
• Used a large buffer size. I used 100000000
• Created rollback segments of bigger size (initial 1 GB and next 100 MB ).
• Increased the online Redo log size . 200MB in my case .
• Drop the snapshot logs before export
• Disable the database triggers before export
• Set database in noarchivelog mode
• Using the above script , export/import the database structure only without indexes, constraints
• Fix the invalid objects and compare the objects
• With help of above scripts , Prepare unix-pipe scripts to run in parallel at user level .
Divide the users in multiple groups based on their timing so that all can finish around same time .
In our case the biggest table was MTL_MATERIAL_TRANSACTION of INV user with 14 Million rows and it was taking 3.5 hours.
So I divided the group in such way that all group should finish in 4 hours.
I used 4 set of unix-pipe scripts .
• Also wrote scripts to build the index parallel to import. \
I wrote the scripts which was checking the tables from imported logs on regular intervals (10 minutes)
and starts rebuilding the index automatically on those tables which were imported in last 10 minutes.
This way index rebuild was also completing on tables which were imported.
The remaining indexes were only on those tables which were imported in last 10 minutes.
This script reduced the index rebuild time .
• Finally compare the objects
E. Contact Information Send your questions and feedback atapp
A. Introduction...............................
B. Required utilities.........................
1. What is Unix Pipe?.........................
2. What is import/export ?....................
3. How fast a table is imported?..............
C. Migration Scripts..........................
D. What I did to finish in 5 Hours............
E. Contact Information........................
A. Introduction Migration is the process of transforming from one version to another.
Export/Import is only available utility to migrate the database across the platform.
Migration of production database across the platform using export/import needs downtime and this effort becomes critical for those companies
whose production database is 24x7 and can’t afford long duration of production downtime.
The aim of this document is to share the technical knowledge of using export/import with Unix pipe,
which needs minimal downtime.
Before finalizing this scripts I tested all combinations of export/import.
The results are based on my experience, which may vary and depends upon server capacity.
It took a little time to fix the scripts but we migrated the production database of 160 GB in 5 hours.
B. Required utilities Migration scripts need following three utilities.
Unix pipe
Oracle export (exp)
Oracle import (imp)
1. What is Unix Pipe?
In Unix, apipe is a unidirectional, stream communication abstraction.
One process writes to the ``write end'' of the pipe,
and a second process reads from the ``read end'' of the pipe.
Its very useful utility and I have used in the scripts .
2. What is import/export ?
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery.
They are also used to move Oracle data from one machine,
database or schema to another.
3. How fast a table is imported?
If you need to monitor how fast rows are imported from a running import job, try following sql :
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-ddhh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
C. Migration Scripts Save your export parameter file as exp_ALL.par
Save your import parameter file as imp_ALL.par
Save the following scripts as remote_exp_imp_all.ksh
Run the scripts from unix prompt as
remote_exp_imp_all.ksh > remote_exp_imp_all.out 2>&1 &
--------------------------------Cut the scripts ----------------------------------------------
#!/bin/ksh
## Assumption
# REMOTE SERVER is server on which existing production database is running
# LOCAL SERVER is new server on which database will be imported.
# In this case remote server is saqhdp01 and database name is C4PR
# All scripts are executed from LOCAL SERVER.
# Only export parameter file is required on REMOTE SERVER
# exp_ALL.par is export parameter file
# imp_ALL.par is import parameter file
set -xv . $HOME/C4PR.env
# C4PR.env sets Database environment TIME_START=`date +%T` touch start_ALL R_SERVER=saqhdp01
# Remote database server REMOTE_HOME=/u333/app
# Location on remote server L_PIPE=$HOME/upgrade/imp_pipe_ALL
# Local Unix Pipe R_PIPE=$REMOTE_HOME/upgrade/exp_pipe_ALL
# Remote Unix Pipe
# Create the local and remote pipes and change the permissions
# Remove the files prior rsh $R_SERVER "/bin/rm $R_PIPE" /usr/bin/rm $L_PIPE rsh $R_SERVER "/etc/mknod $R_PIPE p;
#chmod 777 $R_PIPE" /usr/sbin/mknod $L_PIPE p
# Start the import from the pipe in the background
#imp parfile=$HOME/upgrade/imp_ALL.par > $HOME/upgrade/imp_ALL.out 2>&1 &
# Start the export on the remote server in the background
#rsh $R_SERVER ". /u333/app/upgrade/C4PR.env;
exp parfile=/u333/app/upgrade/exp_ALL.par > /u333/app/upgrade/exp_ALL.out 2>&1" > ALL.out 2>&1 &
# cat (concatenate) the export pipe to the import pipe on the remote server
#rsh $R_SERVER "cat < $R_PIPE " | cat > $L_PIPE TIME_END=`date +%T` touch $HOME/upgrade/end_ALL
# Remove the local and remote pipes
rsh $R_SERVER "rm $R_PIPE"
rm $L_PIPE
mailx -s "Start/End time for piped export/import " <
D. What I did to finish in 5 Hours In order to achieve it, I performed various iterations.
At the end of each iteration results were analyzed and issues (problematic area) were resolved.
Also there were support from internal Engineering, Operations, DBA and management.
Following points were considered in final iterations .
• Gather the statistics from various iterations •
Create a blank database on RAID-1 file systems.
Since import is write intensive operations, RAID-1 takes at least 30% less time.
• Distributes the data files based on I/O pattern. STATSCPACK results are helpful
• Validate the parameters
disk_asynch_io = false
db_writer_processes = (No of cpu/2 )
_system_trig_enabled=FALSE
• Used a large buffer size. I used 100000000
• Created rollback segments of bigger size (initial 1 GB and next 100 MB ).
• Increased the online Redo log size . 200MB in my case .
• Drop the snapshot logs before export
• Disable the database triggers before export
• Set database in noarchivelog mode
• Using the above script , export/import the database structure only without indexes, constraints
• Fix the invalid objects and compare the objects
• With help of above scripts , Prepare unix-pipe scripts to run in parallel at user level .
Divide the users in multiple groups based on their timing so that all can finish around same time .
In our case the biggest table was MTL_MATERIAL_TRANSACTION of INV user with 14 Million rows and it was taking 3.5 hours.
So I divided the group in such way that all group should finish in 4 hours.
I used 4 set of unix-pipe scripts .
• Also wrote scripts to build the index parallel to import. \
I wrote the scripts which was checking the tables from imported logs on regular intervals (10 minutes)
and starts rebuilding the index automatically on those tables which were imported in last 10 minutes.
This way index rebuild was also completing on tables which were imported.
The remaining indexes were only on those tables which were imported in last 10 minutes.
This script reduced the index rebuild time .
• Finally compare the objects
E. Contact Information Send your questions and feedback atapp
Oracle Export and Import Compress Scripts
Here are two scripts that export and import databases and make compressed files for Oracle. Tested on Oracle 8.0.5 and Sun 2.7 If the resulting compressed file is too big for the file system you can run the file through the Unix utility "split" to make smaller files.
#!/bin/ksh
# Export with flags (full, consistent) to a compressed file that
# is named by SID-month-day using Oracle exp
##############################################
# Main
# Configure environment
. oracle_environment.ksh # Oracle environment.
export ORACLE_SID=XXX1
DIRECTORY=/apps/home/oracle/local; export DIRECTORY
FILENAME=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.dmp.gz; export FILENAME
LOG=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.log; export LOG
PIPE=${DIRECTORY}/export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod ${PIPE} p
chmod +rw ${PIPE}
cat $PIPE | /usr/local/bin/gzip > ${FILENAME} &
${ORACLE_HOME}/bin/exp log=${LOG} buffer=10485760 file=${PIPE} consistent=yes full=yes << EOF
system/password
EOF
#############################################################
#!/bin/ksh
# Oracle imp import for gzipped Oracle exp file.
##############################################
# Main
# Path of exp file.
FILENAME=$1; export FILENAME
PIPE=export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod export_pipe.dmp p
chmod +rw ${PIPE}
/usr/local/bin/zcat ${FILENAME} > ${PIPE} &
${ORACLE_HOME}/bin/imp buffer=10485760 file=${PIPE} full=yes log=impzip.log << EOF
system/password
EOF
#########################################################
#!/bin/ksh
# Export with flags (full, consistent) to a compressed file that
# is named by SID-month-day using Oracle exp
##############################################
# Main
# Configure environment
. oracle_environment.ksh # Oracle environment.
export ORACLE_SID=XXX1
DIRECTORY=/apps/home/oracle/local; export DIRECTORY
FILENAME=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.dmp.gz; export FILENAME
LOG=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.log; export LOG
PIPE=${DIRECTORY}/export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod ${PIPE} p
chmod +rw ${PIPE}
cat $PIPE | /usr/local/bin/gzip > ${FILENAME} &
${ORACLE_HOME}/bin/exp log=${LOG} buffer=10485760 file=${PIPE} consistent=yes full=yes << EOF
system/password
EOF
#############################################################
#!/bin/ksh
# Oracle imp import for gzipped Oracle exp file.
##############################################
# Main
# Path of exp file.
FILENAME=$1; export FILENAME
PIPE=export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod export_pipe.dmp p
chmod +rw ${PIPE}
/usr/local/bin/zcat ${FILENAME} > ${PIPE} &
${ORACLE_HOME}/bin/imp buffer=10485760 file=${PIPE} full=yes log=impzip.log << EOF
system/password
EOF
#########################################################
Thursday, November 4, 2010
Delete old file script in windows
delete all file under d:\tmp older than 4 days
FORFILES /p "D:\tmp" /D -4 /C "cmd /c del @fname.* "
FORFILES /p "D:\tmp" /D -4 /C "cmd /c del @fname.* "
Monday, May 31, 2010
Saturday, May 22, 2010
Wednesday, December 9, 2009
Make shared folder in linux
First, you must allow the Linux server full rights and access to the Windows workgroup and local domain. That means that either in your Active Server environment you need to reserve an IP address for the Linux server, or you simply need to ensure a hard coded IP address is given to the Linux server. The server must have a reserved IP address whether you deliver it via DHCP or statically assign it in the DNS. This also means that you need to ALLOW SMB DAEMON to operate. You can ensure your system security does this by editing your Firewall setup or using system-config-securitylevel .
Second, you must ensure that SAMBA (SAMBA tools and the SMB protocol) is installed and running on the Linux server. Most versions of Fedora come with the necessary packages. You should also be sure to update your SAMBA security levels properly if you are having trouble accessing a shared directory. This is found under Samba Server Configuration tool, main menu Properties, option Server Settings, and then the tab: Security.
Now you are ready to configure your Fedora Linux/SAMBA machine... aka. SAMBA Server.
STEP 1: Enable Network Connectivity to the SAMBA Server
Using the Fedora Network Configuration tool you will need to ensure that the ethernet card is enabled and properly functioning. Get quick access to the tool through this command: system-config-network
Once in the Network Configuration tool, you should ensure that your ethernet device is enabled. If it is not, select the eth device and then click on the Edit button. This will allow you to input the vital network adapter settings including: statically set IP address, subnet mask, and gateway. You should also select the top checkbox labeled Activate device when computer starts. Read this article for more details regarding configuring a network card in Fedora, Mandrake, or SuSE.
Close and save any changes you've made. The main goal is to ensure you have an ACTIVE and functioning network card on the SAMBA server.
Restart the network services or simply reboot your SAMBA server. Now try a ping to the server from another PC on the same subnet. At a command prompt, for example, type: ping 10.2.2.3
The ping should come back good validating your network connection. If you need more Linux system administration help read my Admin Commands List.
STEP 2: Update Firewall Settings
In most cases the default Firewall setting on the SAMBA server locks out any inbound network requests. I've had a great many people come running to me about this issue. If you're setting up a basic SAMBA server within your business intranet, allow your ethernet connection to be a trusted device so others can get to your SAMBA server and not be bounced by the server's Firewall.
NOTICE: if you plan to use the SAMBA server outside of your business firewall/intranet you should NOT follow the next step. Instead you allow your local server to receive packets by making changes to your IPTABLES, such as:
iptables -A INPUT -s 192.168.0.0/255.255.255.0 -j ACCEPT
The following step is for those using an intranet business server configuration.
Okay, now to allow your intranet based SAMBA server to properly accept incoming requests, from your Main menu choose System Settings, then Security Level. You can access this also by typing the command:
system-config-securitylevel
Please select the box next to the ethernet card you are using for intranet connectivity so that it becomes a TRUSTED DEVICE. Otherwise you have a super secure server that bounces inbound requests. Notice, this selection effects all the items in the Services listing above it, so please be careful in what context you allow a trusted device!
Press OK when finished.
STEP 3: Enable SMB Services
Ironically, the SMB daemon and other core services are usually NOT started by default. You will need to change this so that your SMB daemon is now started.
Using the GUI from the main menu, go to System Settings, then Server Settings, then choose Services. You can also get to this using the command: system-config-services
While you're looking over this long list of services, please DISABLE things you know for sure you do not need to run on this SAMBA server. For instance apmd, isdn, etc. But also ensure that key services such as SMB are selected and RUNNING. Select SMB and press the Start button. If it is supposedly already running you can press the Restart button to be sure it is indeed running correctly now.
Now press the Save button to make sure the configuration changes have been saved for future restarts.
Sometimes using the GUI just does not properly restart the SMB daemon. In such odd cases, I want to suggest you force a manual restart from the command line with this command: /etc/rc.d/init.d/smb restart
If you keep having startup failures, where for every reboot you need to perform Step 3, you may need to manually configure your start up processes so that SMB will always be in the init.d bootup. Okay, if that sounded ridiculously confusing then how about just going to my article on manually starting services and configurations.
STEP 4: Create Server Users & Directories
You will need to ensure that people also have a login to the SAMBA server to do their work. Logins should be provided on an as needed basis. Obviously, in most cases the users accessing the SAMBA server will be a subset of the total users on the Windows business network.
Create user logins with the Gnome User Manager tool in Fedora. You can find this from the main menu by choosing System Settings, then Users & Groups. The command for this is: system-config-users
Notice this is the first step in creating SAMBA users, which comes later.
Add as many users as you need and then move on to the next part, which is creating directories (aka. folders) for use.
This is such an obvious step most people usually forget to think about it before hand. However, it is very helpful to think ahead what directories you will allow access to on the SAMBA Server for business use. In my case the people needing SAMBA server access will be updating webpages. Therefore, I do not need to add any other folders for file sharing or group interaction. Be sure you add any folders in a reasonable and ordered fashion.
A big TIP for those who are not aware, the permissions settings for your folders are very important. If you have no idea how to change permissions, then you may want to review this article on Linux filemanagement and permissions.
STEP 5: Configure the SAMBA Server
It's time to configure your SAMBA server to allow others on the intranet to login and use the server from Windows or Linux PCs.
From the main Fedora menu, choose System Settings, then Server Settings, then Samba. You can also get to this tool by typing the command: system-config-samba
You are about to make changes to the SAMBA Configuration file called smb.conf. This file is found under /etc/samba. If you encounter issues you may want to first start by using my example smb.conf file and then make the changes below. I also want you to be aware that you can edit configuration files with the web interface tool called Samba Web Administration Tool(SWAT) and several others. Now lets move ahead using the Configuration tool using the preloaded Fedora tools. NOTICE that many people begin by tinkering with their .conf file... this is NOT a good idea. First ensure that the basic samba connectivity works and THEN you can tinker with the smb.conf! (see troubleshooting below)
A. Begin by Making Changes to the Server Settings
Under the Preference menu item choose Server Settings...
Be sure to include the Windows workgroup name. In the example above the workgroup has been changed to net. Your situation may be different. In many cases naming the workgroup simply workgroup is fine, so long as your Windows PCs connect to this same name.
Under this same window, click on the Security tab. It comes by default with the appropriate settings for a basic SAMBA Server. The Authentication mode should be User. You would need to change this only if you plan to allow logins based on the Microsoft ADS.
Press OK to finish making basic changes to the server.
B. Select SAMBA Users
Under the Preference menu item choose Samba Users
In this window you must Add at least one user who will have access to the SAMBA Server. Notice that only user accounts you created in step 4 should be added to this listing.
Press the Add User button, then from the pull down select a user. Fill out the additional information needed for this SAMBA user. Press OK when finished.
C. Adding A Shared Folder
Under the SAMBA Server Configuration window, you must create at least one SAMBA share directory.
Press the Add button and then the Browse button. Now choose a folder you wish to make available to SAMBA users. Be careful, some folders have permissions settings that do not allow sharing. Now be sure to select the Read/Write option to allow people full access. Don't press OK yet!
You should see your shared folder appear under the listing as shown in the example above.
D. Adding Users
In the same window, select the second tab labeled Access. From here choose the first option labeled Only allow access to specific users and select the users you wish to give access to this specific SAMBA shared folder. Press OK when finished.
You can repeat steps C and D for each new shared folder.
Once completed, please choose File from the menu then choose Quit.
Hopefully this saved all of your settings properly. If you encounter issues with the graphic SAMBA configuration tool, such as it failing to accept your changes, then please read the Troubleshooting Tip 4 located below.
STEP 6: Restart the SAMBA Services
Now you need to restart all SAMBA services. You can use the process found in Step 3, except press the Restart button or use the word restart instead of the word start.
I mentioned earlier that sometimes your changes do not get properly picked up. I've installed so many different Fedora SAMBA configurations that I can't recall every reason. This may be a very good time to simply reboot the LINUX/SAMBA Server. Rebooting will ensure everything gets properly started up and all of the configuration changes are included. More importantly, this is likely the last time you will ever restart your SAMBA server again. Some of my FEDORA servers haven't been restarted in years.
STEP 7: Access the SAMBA Server from Windows
You're now ready to fully utilize your new intranet SAMBA Server for work. On any Windows PC you can access the server by simply going to the main Start menu, choosing Run and typing in the hostname of your SAMBA server. For example: \\linuxserver
Please notice that in the Windows environment you use different slashes and you need to ensure this syntax.
If this does not work, perhaps if the server is not yet included in your DNS, try accessing the SAMBA Server through its IP address: \\10.2.2.3
Obviously you need to use an actual hostname or IP address and not my example.
If all works well you should instantly see a SERVER LOGIN window. Now login using a SAMBA created username.
You should then instantly see the shared folder as well as the individual user's personal folder that exist on the SAMBA Server.
Congratulations, you're done.
Second, you must ensure that SAMBA (SAMBA tools and the SMB protocol) is installed and running on the Linux server. Most versions of Fedora come with the necessary packages. You should also be sure to update your SAMBA security levels properly if you are having trouble accessing a shared directory. This is found under Samba Server Configuration tool, main menu Properties, option Server Settings, and then the tab: Security.
Now you are ready to configure your Fedora Linux/SAMBA machine... aka. SAMBA Server.
STEP 1: Enable Network Connectivity to the SAMBA Server
Using the Fedora Network Configuration tool you will need to ensure that the ethernet card is enabled and properly functioning. Get quick access to the tool through this command: system-config-network
Once in the Network Configuration tool, you should ensure that your ethernet device is enabled. If it is not, select the eth device and then click on the Edit button. This will allow you to input the vital network adapter settings including: statically set IP address, subnet mask, and gateway. You should also select the top checkbox labeled Activate device when computer starts. Read this article for more details regarding configuring a network card in Fedora, Mandrake, or SuSE.
Close and save any changes you've made. The main goal is to ensure you have an ACTIVE and functioning network card on the SAMBA server.
Restart the network services or simply reboot your SAMBA server. Now try a ping to the server from another PC on the same subnet. At a command prompt, for example, type: ping 10.2.2.3
The ping should come back good validating your network connection. If you need more Linux system administration help read my Admin Commands List.
STEP 2: Update Firewall Settings
In most cases the default Firewall setting on the SAMBA server locks out any inbound network requests. I've had a great many people come running to me about this issue. If you're setting up a basic SAMBA server within your business intranet, allow your ethernet connection to be a trusted device so others can get to your SAMBA server and not be bounced by the server's Firewall.
NOTICE: if you plan to use the SAMBA server outside of your business firewall/intranet you should NOT follow the next step. Instead you allow your local server to receive packets by making changes to your IPTABLES, such as:
iptables -A INPUT -s 192.168.0.0/255.255.255.0 -j ACCEPT
The following step is for those using an intranet business server configuration.
Okay, now to allow your intranet based SAMBA server to properly accept incoming requests, from your Main menu choose System Settings, then Security Level. You can access this also by typing the command:
system-config-securitylevel
Please select the box next to the ethernet card you are using for intranet connectivity so that it becomes a TRUSTED DEVICE. Otherwise you have a super secure server that bounces inbound requests. Notice, this selection effects all the items in the Services listing above it, so please be careful in what context you allow a trusted device!
Press OK when finished.
STEP 3: Enable SMB Services
Ironically, the SMB daemon and other core services are usually NOT started by default. You will need to change this so that your SMB daemon is now started.
Using the GUI from the main menu, go to System Settings, then Server Settings, then choose Services. You can also get to this using the command: system-config-services
While you're looking over this long list of services, please DISABLE things you know for sure you do not need to run on this SAMBA server. For instance apmd, isdn, etc. But also ensure that key services such as SMB are selected and RUNNING. Select SMB and press the Start button. If it is supposedly already running you can press the Restart button to be sure it is indeed running correctly now.
Now press the Save button to make sure the configuration changes have been saved for future restarts.
Sometimes using the GUI just does not properly restart the SMB daemon. In such odd cases, I want to suggest you force a manual restart from the command line with this command: /etc/rc.d/init.d/smb restart
If you keep having startup failures, where for every reboot you need to perform Step 3, you may need to manually configure your start up processes so that SMB will always be in the init.d bootup. Okay, if that sounded ridiculously confusing then how about just going to my article on manually starting services and configurations.
STEP 4: Create Server Users & Directories
You will need to ensure that people also have a login to the SAMBA server to do their work. Logins should be provided on an as needed basis. Obviously, in most cases the users accessing the SAMBA server will be a subset of the total users on the Windows business network.
Create user logins with the Gnome User Manager tool in Fedora. You can find this from the main menu by choosing System Settings, then Users & Groups. The command for this is: system-config-users
Notice this is the first step in creating SAMBA users, which comes later.
Add as many users as you need and then move on to the next part, which is creating directories (aka. folders) for use.
This is such an obvious step most people usually forget to think about it before hand. However, it is very helpful to think ahead what directories you will allow access to on the SAMBA Server for business use. In my case the people needing SAMBA server access will be updating webpages. Therefore, I do not need to add any other folders for file sharing or group interaction. Be sure you add any folders in a reasonable and ordered fashion.
A big TIP for those who are not aware, the permissions settings for your folders are very important. If you have no idea how to change permissions, then you may want to review this article on Linux filemanagement and permissions.
STEP 5: Configure the SAMBA Server
It's time to configure your SAMBA server to allow others on the intranet to login and use the server from Windows or Linux PCs.
From the main Fedora menu, choose System Settings, then Server Settings, then Samba. You can also get to this tool by typing the command: system-config-samba
You are about to make changes to the SAMBA Configuration file called smb.conf. This file is found under /etc/samba. If you encounter issues you may want to first start by using my example smb.conf file and then make the changes below. I also want you to be aware that you can edit configuration files with the web interface tool called Samba Web Administration Tool(SWAT) and several others. Now lets move ahead using the Configuration tool using the preloaded Fedora tools. NOTICE that many people begin by tinkering with their .conf file... this is NOT a good idea. First ensure that the basic samba connectivity works and THEN you can tinker with the smb.conf! (see troubleshooting below)
A. Begin by Making Changes to the Server Settings
Under the Preference menu item choose Server Settings...
Be sure to include the Windows workgroup name. In the example above the workgroup has been changed to net. Your situation may be different. In many cases naming the workgroup simply workgroup is fine, so long as your Windows PCs connect to this same name.
Under this same window, click on the Security tab. It comes by default with the appropriate settings for a basic SAMBA Server. The Authentication mode should be User. You would need to change this only if you plan to allow logins based on the Microsoft ADS.
Press OK to finish making basic changes to the server.
B. Select SAMBA Users
Under the Preference menu item choose Samba Users
In this window you must Add at least one user who will have access to the SAMBA Server. Notice that only user accounts you created in step 4 should be added to this listing.
Press the Add User button, then from the pull down select a user. Fill out the additional information needed for this SAMBA user. Press OK when finished.
C. Adding A Shared Folder
Under the SAMBA Server Configuration window, you must create at least one SAMBA share directory.
Press the Add button and then the Browse button. Now choose a folder you wish to make available to SAMBA users. Be careful, some folders have permissions settings that do not allow sharing. Now be sure to select the Read/Write option to allow people full access. Don't press OK yet!
You should see your shared folder appear under the listing as shown in the example above.
D. Adding Users
In the same window, select the second tab labeled Access. From here choose the first option labeled Only allow access to specific users and select the users you wish to give access to this specific SAMBA shared folder. Press OK when finished.
You can repeat steps C and D for each new shared folder.
Once completed, please choose File from the menu then choose Quit.
Hopefully this saved all of your settings properly. If you encounter issues with the graphic SAMBA configuration tool, such as it failing to accept your changes, then please read the Troubleshooting Tip 4 located below.
STEP 6: Restart the SAMBA Services
Now you need to restart all SAMBA services. You can use the process found in Step 3, except press the Restart button or use the word restart instead of the word start.
I mentioned earlier that sometimes your changes do not get properly picked up. I've installed so many different Fedora SAMBA configurations that I can't recall every reason. This may be a very good time to simply reboot the LINUX/SAMBA Server. Rebooting will ensure everything gets properly started up and all of the configuration changes are included. More importantly, this is likely the last time you will ever restart your SAMBA server again. Some of my FEDORA servers haven't been restarted in years.
STEP 7: Access the SAMBA Server from Windows
You're now ready to fully utilize your new intranet SAMBA Server for work. On any Windows PC you can access the server by simply going to the main Start menu, choosing Run and typing in the hostname of your SAMBA server. For example: \\linuxserver
Please notice that in the Windows environment you use different slashes and you need to ensure this syntax.
If this does not work, perhaps if the server is not yet included in your DNS, try accessing the SAMBA Server through its IP address: \\10.2.2.3
Obviously you need to use an actual hostname or IP address and not my example.
If all works well you should instantly see a SERVER LOGIN window. Now login using a SAMBA created username.
You should then instantly see the shared folder as well as the individual user's personal folder that exist on the SAMBA Server.
Congratulations, you're done.
Thursday, December 3, 2009
Creating a LVM in Linux
To create a LVM, we follow a three step process.
Step One : We need to select the physical storage resources that are going to be used for LVM. Typically, these are standard partitions but can also be Linux software RAID volumes that we've created. In LVM terminology, these storage resources are called "physical volumes" (eg: /dev/hda1, /dev/hda2 ... etc).
Our first step in setting up LVM involves properly initializing these partitions so that they can be recognized by the LVM system. This involves setting the correct partition type (usually using the fdisk command, and entering the type of partition as 'Linux LVM' - 0x8e ) if we're adding a physical partition; and then running the pvcreate command.
# pvcreate /dev/hda1 /dev/hda2 /dev/hda3
# pvscan
The above step creates a physical volume from 3 partitions which I want to initialize for inclusion in a volume group.
Step Two : Creating a volume group. You can think of a volume group as a pool of storage that consists of one or more physical volumes. While LVM is running, we can add physical volumes to the volume group or even remove them.
First initialize the /etc/lvmtab and /etc/lvmtab.d files by running the following command:
# vgscan
Now you can create a volume group and assign one or more physical volumes to the volume group.
# vgcreate my_vol_grp /dev/hda1 /dev/hda2
Behind the scenes, the LVM system allocates storage in equal-sized "chunks", called extents. We can specify the particular extent size to use at volume group creation time. The size of an extent defaults to 4Mb, which is perfect for most uses.You can use the -s flag to change the size of the extent. The extent affects the minimum size of changes which can be made to a logical volume in the volume group, and the maximum size of logical and physical volumes in the volume group. A logical volume can contain at most 65534 extents, so the default extent size (4 MB) limits the volume to about 256 GB; a size of 1 TB would require extents of atleast 16 MB. So to accomodate a 1 TB size, the above command can be rewriten as :
# vgcreate -s 16M my_vol_grp /dev/hda1 /dev/hda2
You can check the result of your work at this stage by entering the command:
# vgdisplay
This command displays the total physical extends in a volume group, size of each extent, the allocated size and so on.
Step Three : This step involves the creation of one or more "logical volumes" using our volume group storage pool. The logical volumes are created from volume groups, and may have arbitary names. The size of the new volume may be requested in either extents (-l switch) or in KB, MB, GB or TB ( -L switch) rounding up to whole extents.
# lvcreate -l 50 -n my_logical_vol my_vol_grp
The above command allocates 50 extents of space in my_vol_grp to the newly created my_logical_vol. The -n switch specifies the name of the logical volume we are creating.
Now you can check if you got the desired results by using the command :
# lvdisplay
which shows the information of your newly created logical volume.
Once a logical volume is created, we can go ahead and put a filesystem on it, mount it, and start using the volume to store our files. For creating a filesystem, we do the following:
# mke2fs -j /dev/my_vol_grp/my_logical_vol
The -j signifies journaling support for the ext3 filesystem we are creating.
Mount the newly created file system :
# mount /dev/my_vol_grp/my_logical_vol /data
Also do not forget to append the corresponding line in the /etc/fstab file:
#File: /etc/fstab
/dev/my_vol_grp/my_logical_vol /data ext3 defaults 0 0
Resizing Logical Volumes
This is a continuation of my earlier post Creating Logical Volumes in Linux . Here I will explain how to resize an existing logical volume. Logical volumes may be resized dynamically while preserving the data on the volume. Here is how:
Reducing a logical volume
Reduce the filesystem residing on the logical volume.
Reduce the logical volume.
For different file systems, it is achieved differently.
For ext2 file system
If you are using LVM 1, then both the above steps could be acomplished by executing a single utility called e2fsadm.
# umount /data# e2fsadm -L -1G /dev/my_vol_grp/my_logical_vol# mount /dataThe above command first reduces the filesystem in the 'my_logical_vol' by 1 GB and then reduces the my_logical_vol itself by the same amount.
If you are using LVM 2 - more recent linux distributions like Fedora use LVM 2 - then you do not have the 'e2fsadm' utility. So you have to first reduce the filesystem using 'resize2fs' and then reduce the logical volume using 'lvreduce'.
# umount /data# resize2fs /dev/my_vol_grp/my_logical_vol 1G# lvreduce -L 1G /dev/my_vol_grp/my_logical_vol# mount /dataIn the above case, I have reduced my file system "to" 1 GB size ...
Note: I didn't use the minus (-) sign while using resize2fs
... And then used the lvreduce command to reduce the logical volume "to" 1 GB. If I want to reduce the logical volume "by" 1 GB, then I give the same command but with "-L -1G" instead of "-L 1G".
Reiserfs file system
If you have a reiserfs filesystem, then the commands are a bit different than ext2(3).
# umount /data# resize_reiserfs -s -1G /dev/my_vol_grp/my_logical_vol# lvreduce -L -1G /dev/my_vol_grp/my_logical_vol# mount -t reiserfs /dataXFS and JFS filesystems
As of now, there is no way to shrink these filesystems residing on logical volumes.
Grow a Logical Volume
The steps for growing a logical volume are the exact opposite of those for shrinking the logical volume.
1.Enlarge the logical volume first.
2.Then resize the filesystem to the new size of your logical volume.
Update (July 22nd 2005) : I came across this very interesting article on LVM at RedHat Magazine which I found really informative.
Posted by Ravi
Step One : We need to select the physical storage resources that are going to be used for LVM. Typically, these are standard partitions but can also be Linux software RAID volumes that we've created. In LVM terminology, these storage resources are called "physical volumes" (eg: /dev/hda1, /dev/hda2 ... etc).
Our first step in setting up LVM involves properly initializing these partitions so that they can be recognized by the LVM system. This involves setting the correct partition type (usually using the fdisk command, and entering the type of partition as 'Linux LVM' - 0x8e ) if we're adding a physical partition; and then running the pvcreate command.
# pvcreate /dev/hda1 /dev/hda2 /dev/hda3
# pvscan
The above step creates a physical volume from 3 partitions which I want to initialize for inclusion in a volume group.
Step Two : Creating a volume group. You can think of a volume group as a pool of storage that consists of one or more physical volumes. While LVM is running, we can add physical volumes to the volume group or even remove them.
First initialize the /etc/lvmtab and /etc/lvmtab.d files by running the following command:
# vgscan
Now you can create a volume group and assign one or more physical volumes to the volume group.
# vgcreate my_vol_grp /dev/hda1 /dev/hda2
Behind the scenes, the LVM system allocates storage in equal-sized "chunks", called extents. We can specify the particular extent size to use at volume group creation time. The size of an extent defaults to 4Mb, which is perfect for most uses.You can use the -s flag to change the size of the extent. The extent affects the minimum size of changes which can be made to a logical volume in the volume group, and the maximum size of logical and physical volumes in the volume group. A logical volume can contain at most 65534 extents, so the default extent size (4 MB) limits the volume to about 256 GB; a size of 1 TB would require extents of atleast 16 MB. So to accomodate a 1 TB size, the above command can be rewriten as :
# vgcreate -s 16M my_vol_grp /dev/hda1 /dev/hda2
You can check the result of your work at this stage by entering the command:
# vgdisplay
This command displays the total physical extends in a volume group, size of each extent, the allocated size and so on.
Step Three : This step involves the creation of one or more "logical volumes" using our volume group storage pool. The logical volumes are created from volume groups, and may have arbitary names. The size of the new volume may be requested in either extents (-l switch) or in KB, MB, GB or TB ( -L switch) rounding up to whole extents.
# lvcreate -l 50 -n my_logical_vol my_vol_grp
The above command allocates 50 extents of space in my_vol_grp to the newly created my_logical_vol. The -n switch specifies the name of the logical volume we are creating.
Now you can check if you got the desired results by using the command :
# lvdisplay
which shows the information of your newly created logical volume.
Once a logical volume is created, we can go ahead and put a filesystem on it, mount it, and start using the volume to store our files. For creating a filesystem, we do the following:
# mke2fs -j /dev/my_vol_grp/my_logical_vol
The -j signifies journaling support for the ext3 filesystem we are creating.
Mount the newly created file system :
# mount /dev/my_vol_grp/my_logical_vol /data
Also do not forget to append the corresponding line in the /etc/fstab file:
#File: /etc/fstab
/dev/my_vol_grp/my_logical_vol /data ext3 defaults 0 0
Resizing Logical Volumes
This is a continuation of my earlier post Creating Logical Volumes in Linux . Here I will explain how to resize an existing logical volume. Logical volumes may be resized dynamically while preserving the data on the volume. Here is how:
Reducing a logical volume
Reduce the filesystem residing on the logical volume.
Reduce the logical volume.
For different file systems, it is achieved differently.
For ext2 file system
If you are using LVM 1, then both the above steps could be acomplished by executing a single utility called e2fsadm.
# umount /data# e2fsadm -L -1G /dev/my_vol_grp/my_logical_vol# mount /dataThe above command first reduces the filesystem in the 'my_logical_vol' by 1 GB and then reduces the my_logical_vol itself by the same amount.
If you are using LVM 2 - more recent linux distributions like Fedora use LVM 2 - then you do not have the 'e2fsadm' utility. So you have to first reduce the filesystem using 'resize2fs' and then reduce the logical volume using 'lvreduce'.
# umount /data# resize2fs /dev/my_vol_grp/my_logical_vol 1G# lvreduce -L 1G /dev/my_vol_grp/my_logical_vol# mount /dataIn the above case, I have reduced my file system "to" 1 GB size ...
Note: I didn't use the minus (-) sign while using resize2fs
... And then used the lvreduce command to reduce the logical volume "to" 1 GB. If I want to reduce the logical volume "by" 1 GB, then I give the same command but with "-L -1G" instead of "-L 1G".
Reiserfs file system
If you have a reiserfs filesystem, then the commands are a bit different than ext2(3).
# umount /data# resize_reiserfs -s -1G /dev/my_vol_grp/my_logical_vol# lvreduce -L -1G /dev/my_vol_grp/my_logical_vol# mount -t reiserfs /dataXFS and JFS filesystems
As of now, there is no way to shrink these filesystems residing on logical volumes.
Grow a Logical Volume
The steps for growing a logical volume are the exact opposite of those for shrinking the logical volume.
1.Enlarge the logical volume first.
2.Then resize the filesystem to the new size of your logical volume.
Update (July 22nd 2005) : I came across this very interesting article on LVM at RedHat Magazine which I found really informative.
Posted by Ravi
Wednesday, October 21, 2009
Oracle Table Compression
Oracle Table Compression
Oracle employs a very nice compression algorithm on database block level which is quite efficient and can yield threefold compression ratio in most cases.
The table compression is highly recommended for Data Warehouse environments where the operations tend to be read heavy and batch. Table compression is not recommended for OLTP environments or on tables with high OLTP like operations because of the cost associated with DML operations on compressed tables. This is true with Oracle 10g. Oracle 11g has gone wild, and offers OLTP compression as well!
I have found this quite nice short explenation in Oracle Documentation about what Oracle Table compression is:
"...Oracle table compression feature compresses data by eliminating duplicate values in a database block. Compressed data stored in a database block (also known as disk page) is self-contained. That is, all the information needed to re-create the uncompressed data in a block is available within that block. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table..."
The benefits associated with Table Compression are:
Save space
Faster backups
Query Performance
1. Find out the candidate table to compress
Usually NUM_ROWS will reveal the table in need for compression. In Data Warehouses these are usually fact tables. The fact tables tend to have lots of redundant data in the columns such as keys, data and status flag data. The compression algorithm uses a symbolic table structure to record this redundancy numerically rather than physically and repeatedly a the table level. That is compression on the 'Relational Table' level rather than byte level.
My worked example:
As example we will use the X table, the largest table which I will partition and compress all its partitions except the current partition. The reason we don't compress the current partition is because in the current month partition there will be load of ETL activity (DML) and we do not want to introduce high CPU usage because of compression. Compression is no good when there is lots of DML.
The size of the table before compression is 6GB:
SQL> select segment_name, sum(bytes)/1024/1024 MB from user_Segments where segment_name like 'X'
group by segment_name
SQL> /
SEGMENT_NAME MB
------------------------------ ----------
X 6179
2. List the partitions of the candidate table and decide which to compress
You can list the partition names for the tables you are interested to compress like this:
SQL> set pages 49999
SQL> l
1* select table_name, partition_name, compression from user_tab_partitions where table_name ='X'
SQL> /TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
...
X P0201 DISABLED
X P0202 DISABLED
...
Then you can write an SQL query to build the command which will move and compress the partitions. Just by altering the property of the table to COMPRESS => ENABLED doesn't compress existing rows in the table. It will compress only the new rows which will be added to the table. To save space we must compress what is there and leave only the current partition and future partitions uncompressed. You must remember to NOT compress the current partition and the future partitions, as the script below will generate commands to compress all partitions. To find out the current partition look at the PARTITION_POSITION in USER_TAB_PARTITIONS, the highest positioned partition is the current partition. You can also order by PARTITION_POSITION as below and don't do the first line.
Generate PARTITION COMPRESS commands with the following script.
SQL> select 'ALTER TABLE 'table_name' MOVE PARTITION 'partition_name ' TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL; ' from user_tab_partitions where table_name ='X'
order by partition_position desc;
'ALTERTABLE'TABLE_NAME'MOVEPARTITION'PARTITION_NAME'TABLESPACENEW_TABLESPACECOMPRESSNOLOGGINGPARALLEL;'
---------------------------------------------------------------------------------------------------
ALTER TABLE X MOVE PARTITION P0207 TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL;
ALTER TABLE X MOVE PARTITION P0107 TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL;
WARNING : It is very important to choose to compress the tables in NEW tablespace as compressing them alone doesn't reorganize the extents in the datafiles in a way for them to be able to be re-sized and shrunk later on.
3. If there are bitmap indexes on the candidate table you must first mark them unusable
WARNING : To be able to apply the commands below the table in question must not have bitmap indexes , if it does you must mark them as unusable otherwise you get ORA-14646 err.
ORA-14646: Specified alter table operation involving compression cannot be performed
in the presence of usable bitmap indexes
See here for more info :http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/parpart.htm#i1006787
You will have to rebuild the indexes after you have finished with compressing the partitions.
4. Run the compression commands
For each partition you wish to compress you are now ready to run the commands like :
ALTER TABLE X MOVE PARTITION P0207 TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL;
ALTER TABLE X MOVE PARTITION P0107 TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL;
You can choose to use NOLOGGING for this operation as it generates lots of redo and will affect your archive logging and PARALLEL to make it faster.
After compressing all partitions on the X table the savings realised are as follows:
A 41% saving!
-- Before Compression
SEGMENT_NAME MB
------------------------------ ----------
X 6179
-- After Compression
SEGMENT_NAME MB
------------------------------ ----------
X 3670
5. You must rebuild the BITMAP INDEXES marked UNUSABLE at the previous step 4.
You will have to rebuild all bitmap , and other if any, indexes marked unusable at the previous steps, to do it quickly you ca use NOLOGGING and PARALLEL. Similarly write an SQL statement querying the USER_IND_PARTITIONS table and generate statement like the following for all indexes.
ALTER INDEX my_bitmap_index REBUILD PARTITION P1001 NOLOGGING;
ALTER INDEX my_bitmap_index REBUILD PARTITION P1002 NOLOGGING;
...
This is a one-off operation you won't do it again for the table, ie when you add a new partition.
6. You must rebuild ALL LOCAL INDEXES in the partitioned tables as their state has now become UNUSABLE
After the compression of the table partitions all local indexes associate with the table become UNUSABLE. As the oracle documentation categorically indicates:
If you use the ALTER TABLE ... MOVE statement, the local indexes for a partition become unusable. You have to rebuild them afterwards. Otherwise you will get errors in partition maintenance operations like this:
*
ERROR at line 1:
ORA-01502: index 'my_schema.my_X_table_index' or partition of such index is in unusable
state
You can do that by writing something like this and change the table_name=X for each table.
SQL> select 'alter table X modify partition ' partition_name ' rebuild unusable local indexes;' from user_tab_partitions where table_name='X'
NOTE: You will also have to rebuild indexes whenever you COMPRESS/NOCOMPRESS any partitions. That is, when you use any of these two operations, data moves, and indexes break and have to be rebuild.
7. Check to see the space you have gained.
After moving the table partition segments to their new tablespace and rebuilding necessary indexes you ought to gain some space. You will realize that the space which the objects before compression has decreased. You will require less space to store them, when compressed, in their new tablespace. After you move everything from the old tablespace into the new and you are sure the old tablespace is empty you can then drop the old tablespace and its datafiles.
Well the only approach to 'claim' space back after a table compression operation seems to be the use of ALTER TABLE ... MOVE TABLESPACE 'YOURNEWTABLESPACE' on a 'new' tablespace. Although we can claim physical space back from the database only by resizing datafiles and we could have 'moved' the tables withing their 'own' tablespace, it is not recommended.
Moving the tables within the same tablespace is not a good idea, because you are never sure if the reorganization of the table segments will actually move the HWM of the datafile down so that you can resize the datafile. (see below asktom.oracle.com links for more info)
There might be 'that' extent still hanging at the end of the tablespace which will prevent you from resizing the datafiles. Although you can fish that 'hanging at the end' extent out and reorganize the segment you are better off by moving all to a new tablespace.
Use DBA_SEGMENTS before you decide to drop a tablespace to see if any segments are left in it. Alternativaly you can choose to resize the datafiles to very small files.
I am also reading lately that in Oracle 11g you can get Advanced Table Compression in OLTP environments as well! It is an option which you have to puchase separately in Enterprise Edition though.
References:
Forum Questions to Asktom :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7873209745771#240368600346159666
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899
Oracle Docs :
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/parpart.htm#sthref196
Oracle employs a very nice compression algorithm on database block level which is quite efficient and can yield threefold compression ratio in most cases.
The table compression is highly recommended for Data Warehouse environments where the operations tend to be read heavy and batch. Table compression is not recommended for OLTP environments or on tables with high OLTP like operations because of the cost associated with DML operations on compressed tables. This is true with Oracle 10g. Oracle 11g has gone wild, and offers OLTP compression as well!
I have found this quite nice short explenation in Oracle Documentation about what Oracle Table compression is:
"...Oracle table compression feature compresses data by eliminating duplicate values in a database block. Compressed data stored in a database block (also known as disk page) is self-contained. That is, all the information needed to re-create the uncompressed data in a block is available within that block. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table..."
The benefits associated with Table Compression are:
Save space
Faster backups
Query Performance
1. Find out the candidate table to compress
Usually NUM_ROWS will reveal the table in need for compression. In Data Warehouses these are usually fact tables. The fact tables tend to have lots of redundant data in the columns such as keys, data and status flag data. The compression algorithm uses a symbolic table structure to record this redundancy numerically rather than physically and repeatedly a the table level. That is compression on the 'Relational Table' level rather than byte level.
My worked example:
As example we will use the X table, the largest table which I will partition and compress all its partitions except the current partition. The reason we don't compress the current partition is because in the current month partition there will be load of ETL activity (DML) and we do not want to introduce high CPU usage because of compression. Compression is no good when there is lots of DML.
The size of the table before compression is 6GB:
SQL> select segment_name, sum(bytes)/1024/1024 MB from user_Segments where segment_name like 'X'
group by segment_name
SQL> /
SEGMENT_NAME MB
------------------------------ ----------
X 6179
2. List the partitions of the candidate table and decide which to compress
You can list the partition names for the tables you are interested to compress like this:
SQL> set pages 49999
SQL> l
1* select table_name, partition_name, compression from user_tab_partitions where table_name ='X'
SQL> /TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
...
X P0201 DISABLED
X P0202 DISABLED
...
Then you can write an SQL query to build the command which will move and compress the partitions. Just by altering the property of the table to COMPRESS => ENABLED doesn't compress existing rows in the table. It will compress only the new rows which will be added to the table. To save space we must compress what is there and leave only the current partition and future partitions uncompressed. You must remember to NOT compress the current partition and the future partitions, as the script below will generate commands to compress all partitions. To find out the current partition look at the PARTITION_POSITION in USER_TAB_PARTITIONS, the highest positioned partition is the current partition. You can also order by PARTITION_POSITION as below and don't do the first line.
Generate PARTITION COMPRESS commands with the following script.
SQL> select 'ALTER TABLE 'table_name' MOVE PARTITION 'partition_name ' TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL; ' from user_tab_partitions where table_name ='X'
order by partition_position desc;
'ALTERTABLE'TABLE_NAME'MOVEPARTITION'PARTITION_NAME'TABLESPACENEW_TABLESPACECOMPRESSNOLOGGINGPARALLEL;'
---------------------------------------------------------------------------------------------------
ALTER TABLE X MOVE PARTITION P0207 TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL;
ALTER TABLE X MOVE PARTITION P0107 TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL;
WARNING : It is very important to choose to compress the tables in NEW tablespace as compressing them alone doesn't reorganize the extents in the datafiles in a way for them to be able to be re-sized and shrunk later on.
3. If there are bitmap indexes on the candidate table you must first mark them unusable
WARNING : To be able to apply the commands below the table in question must not have bitmap indexes , if it does you must mark them as unusable otherwise you get ORA-14646 err.
ORA-14646: Specified alter table operation involving compression cannot be performed
in the presence of usable bitmap indexes
See here for more info :http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/parpart.htm#i1006787
You will have to rebuild the indexes after you have finished with compressing the partitions.
4. Run the compression commands
For each partition you wish to compress you are now ready to run the commands like :
ALTER TABLE X MOVE PARTITION P0207 TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL;
ALTER TABLE X MOVE PARTITION P0107 TABLESPACE NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL;
You can choose to use NOLOGGING for this operation as it generates lots of redo and will affect your archive logging and PARALLEL to make it faster.
After compressing all partitions on the X table the savings realised are as follows:
A 41% saving!
-- Before Compression
SEGMENT_NAME MB
------------------------------ ----------
X 6179
-- After Compression
SEGMENT_NAME MB
------------------------------ ----------
X 3670
5. You must rebuild the BITMAP INDEXES marked UNUSABLE at the previous step 4.
You will have to rebuild all bitmap , and other if any, indexes marked unusable at the previous steps, to do it quickly you ca use NOLOGGING and PARALLEL. Similarly write an SQL statement querying the USER_IND_PARTITIONS table and generate statement like the following for all indexes.
ALTER INDEX my_bitmap_index REBUILD PARTITION P1001 NOLOGGING;
ALTER INDEX my_bitmap_index REBUILD PARTITION P1002 NOLOGGING;
...
This is a one-off operation you won't do it again for the table, ie when you add a new partition.
6. You must rebuild ALL LOCAL INDEXES in the partitioned tables as their state has now become UNUSABLE
After the compression of the table partitions all local indexes associate with the table become UNUSABLE. As the oracle documentation categorically indicates:
If you use the ALTER TABLE ... MOVE statement, the local indexes for a partition become unusable. You have to rebuild them afterwards. Otherwise you will get errors in partition maintenance operations like this:
*
ERROR at line 1:
ORA-01502: index 'my_schema.my_X_table_index' or partition of such index is in unusable
state
You can do that by writing something like this and change the table_name=X for each table.
SQL> select 'alter table X modify partition ' partition_name ' rebuild unusable local indexes;' from user_tab_partitions where table_name='X'
NOTE: You will also have to rebuild indexes whenever you COMPRESS/NOCOMPRESS any partitions. That is, when you use any of these two operations, data moves, and indexes break and have to be rebuild.
7. Check to see the space you have gained.
After moving the table partition segments to their new tablespace and rebuilding necessary indexes you ought to gain some space. You will realize that the space which the objects before compression has decreased. You will require less space to store them, when compressed, in their new tablespace. After you move everything from the old tablespace into the new and you are sure the old tablespace is empty you can then drop the old tablespace and its datafiles.
Well the only approach to 'claim' space back after a table compression operation seems to be the use of ALTER TABLE ... MOVE TABLESPACE 'YOURNEWTABLESPACE' on a 'new' tablespace. Although we can claim physical space back from the database only by resizing datafiles and we could have 'moved' the tables withing their 'own' tablespace, it is not recommended.
Moving the tables within the same tablespace is not a good idea, because you are never sure if the reorganization of the table segments will actually move the HWM of the datafile down so that you can resize the datafile. (see below asktom.oracle.com links for more info)
There might be 'that' extent still hanging at the end of the tablespace which will prevent you from resizing the datafiles. Although you can fish that 'hanging at the end' extent out and reorganize the segment you are better off by moving all to a new tablespace.
Use DBA_SEGMENTS before you decide to drop a tablespace to see if any segments are left in it. Alternativaly you can choose to resize the datafiles to very small files.
I am also reading lately that in Oracle 11g you can get Advanced Table Compression in OLTP environments as well! It is an option which you have to puchase separately in Enterprise Edition though.
References:
Forum Questions to Asktom :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7873209745771#240368600346159666
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899
Oracle Docs :
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/parpart.htm#sthref196
Subscribe to:
Posts (Atom)