Oracle masala at work - 4

Oracle masala at work - 4:

1. I don't have Primary Key or Unique Key on the tables and the Oracle Streams apply process is slow, what should I do?:

2. How to mine a transaction using dictionary online catalog tables:
3. Securefile CLOB/BLOB column compression in 11g, minimum downtime plan:
4. How to format Oracle output in XML:
5. ACS and ORA-24247 errors after moving to oracle 11g:
6. Reclaiming Unused LOB Space (Starting with Oracle 10g R2):
7. AUTO DOP - Parallel hint (/*+ parallel (x,4) */) doesn’t work with 11.2.0.2:
8. Index status "INPROGRS"? -- Domain Index
9. How to stop propagting tranctions in Oracle streams. -- Tag
10. Myth behind the plan directive values of PARALLEL_DEGREE_LIMIT_P1 in DBRM?
11. TNS:connection closed:
12. How can i remove a job from another user?
13. Snapshot / Backup controlfile location, must be shared location in RAC environment from 11gR2:

Details:
1. I don't have Primary Key or Unique Key on the tables and the Oracle Streams apply process is slow, what should I do?:

-- If you can't create PK/UK's on the source table, you can create alternate keys on the target side.


Steps:
a).
-- Check if any alternate keys exist on the target database

SELECT tc.owner, tc.table_name, tc.column_name, a.compare_old_on_delete,
a.compare_old_on_update,
DECODE (k.column_name, NULL, 'N', 'Y') manual_key_column
FROM dba_tab_columns tc, dba_apply_table_columns a, dba_apply_key_columns k
WHERE a.object_owner(+) = tc.owner
AND a.object_name(+) = tc.table_name
AND a.column_name(+) = tc.column_name
AND k.object_owner(+) = tc.owner
AND k.object_name(+) = tc.table_name
AND k.column_name(+) = tc.column_name
AND DECODE (k.column_name, NULL, 'N', 'Y') = 'Y'
AND owner = ''
AND table_name = '';

b).
-- Add alternate keys on the target database

connect streams_admin
exec dbms_apply_adm.stop_apply (apply_name => '');

execute DBMS_APPLY_ADM.SET_KEY_COLUMNS(object_name => '.', column_list => ', ');

execute DBMS_APPLY_ADM.COMPARE_OLD_VALUES(object_name => '.', column_list => '*', operation => '*', compare => false);

exec dbms_apply_adm.start_apply (apply_name => '');


2. How to mine a transaction using dictionary online catalog tables:

Note: Without a dictionary file, LogMiner displays all tables and columns using their internal object ID's and all values as hex data.

a).
-- add the log file to the log miner session

execute dbms_logmnr.add_logfile (LOGFILENAME => '+ODS_T1_BACKUP_01/ODS/ARCHIVELOG/2011_03_16/thread_1_seq_197269.606.745926547', OPTIONS => DBMS_LOGMNR.NEW);

b).
-- alternatively, if dictionary from online catalog is being used :

EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

c).
alter session set nls_date_format='mm-dd-yyyy hh24:mi:ss';

set pagesize 10000
set termout off

spool mine.out

column scn format 9999999999999999
column cscn format 9999999999999999
-- determine what the transaction relates to .
select seg_name, operation, timestamp, sql_redo, sql_undo,scn, cscn, xidusn || '.' || xidslt || '.' || xidsqn trnsid, sql_redo, sql_undo
from v$logmnr_contents
where xidusn || '.' || xidslt || '.' || xidsqn='3.14.183859';

spool off


3. Securefile CLOB/BLOB column compression in 11g, minimum downtime plan:

-- The initialization parameter db_securefile determines the use of SecureFiles in the database. PERMITTED is the default value.

a).
set timing on
set time on
set echo on

spool 01_ODS_UTS_REP_COMPRESS_T345.log

ALTER SESSION ENABLE PARALLEL DML;

-- Check table can be redefined
EXEC DBMS_REDEFINITION.can_redef_table('UTS_REP', 'T345');

CREATE TABLE UTS_REP.T345COMP
TABLESPACE UET_COMP
COMPRESS FOR ARCHIVE HIGH
LOB (C900008110) STORE AS SECUREFILE
( DEDUPLICATE COMPRESS HIGH TABLESPACE UET_COMP )
PARALLEL LOGGING
AS SELECT * FROM UTS_REP.T345 WHERE ROWNUM < 1;

ALTER TABLE UTS_REP.T345 PARALLEL LOGGING;

-- *** Create indexes on the compressed table with different name
-- Start Redefinition
EXEC DBMS_REDEFINITION.start_redef_table('UTS_REP', 'T345', 'T345COMP');

-- Synchronize new table with interim data
EXEC DBMS_REDEFINITION.sync_interim_table('UTS_REP', 'T345', 'T345COMP');

SPOOL OFF;
exit;

Note: C900008110 column is CLOB in this case.


b).
-- OUTAGE STARTS HERE

spool 02_ODS_UTS_REP_SYNC_T345.log

-- Synchronize new table with interim data
EXEC DBMS_REDEFINITION.sync_interim_table('UTS_REP', 'T345', 'T345COMP');

-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table('UTS_REP', 'T345', 'T345COMP');

-- Validate the table counts.
SELECT COUNT(*) FROM UTS_REP.T345;

SELECT COUNT(*) FROM UTS_REP.T345COMP;

-- Confirm that the table was converted
SELECT column_name, securefile
FROM dba_lobs
WHERE table_name = 'T345';

SPOOL OFF;
exit;


c).
-- *** Rename the indexes on T345COMP table(here T345COMP table is the original table) and Rename indexes on T345 table with original names.
-- *** Re-create synonyms and Re-grant the permissions on the compressed T345 table.

-- VERIFY THE SIZES AND COMPRESSIONS ON THE TWO TABLES BEFORE DROPPING.
-- Now the T345 table should be compressed, but with missing grants. And T345COMP should be the original table without compression.

-- OUTAGE ENDS HERE

-- Do this step with CAUTION
-- If the Compression was successful, please drop UTS_REP.T345COMP else please drop the T345 table and rename T345COMP to T345(original uncompressed table)

-- *** DROP TABLE UTS_REP.T345COMP CASCADE CONSTRAINTS; -- (OR) Next week cleanup step


4. How to format Oracle output in XML:

set pages 0
set linesize 200
set long 9999999
set head off

select dbms_xmlgen.getxml('select * from emp where rownum < 6') xml from dual;


5. ACS and ORA-24247 errors after moving to oracle 11g:
Cause:
-- From 11gR1 the Oracle Database enhanced the level of security when users attempt to access External Network Services by implementing Access Control Lists (ACL)
-- using the new DBMS_NETWORK_ACL_ADMIN package.

Error:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at "EBI_STATS.INF_LONG_RUNNING_JOBS", line 78
ORA-06512: at line 2

Solution:
-- To resolve this error, connect to the database as a user with DBA privileges and issue the following script which will grant a user or role access
-- to External Network Services.
-- Be sure to replace the user names EBI_STATS and DBA_STATS with the name of the user or role in which you want to grant access. This value is case-sensitive.


BEGIN


DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'network_services.xml',
description => 'Network Services ACL',
principal => 'DBA_STATS',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'network_services.xml',
principal => 'DBA_STATS',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'network_services.xml',
principal => 'EBI_STATS',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'network_services.xml',
principal => 'EBI_STATS',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'network_services.xml',
host => '*');
COMMIT;

END;
/


6. Reclaiming Unused LOB Space (Starting with Oracle 10g R2):

-- The LOB segment must reside in an ASSM tablespace.
-- Shrinking a LOB segment does generate redo, For example, shrinking a 100GB LOB will generate approximately 100GB of redo.
-- CASCADE clause that shrinks all dependent objects (including LOBS) along with the table data itself.

ALTER TABLE ENABLE ROW MOVEMENT;

ALTER TABLE MODIFY LOB () (SHRINK SPACE);
-- OR --
ALTER TABLE SHRINK SPACE CASCADE;

ALTER TABLE DISABLE ROW MOVEMENT;

-- How to find candidate object for Shrinkage?:
set serveroutput on size 999999;

begin
if (dbms_space.verify_shrink_candidate
('','','TABLE', 1073741824) -- Target Shrinkage is 1GB (1073,741,824)
) then
DBMS_OUTPUT.PUT_LINE('Candidates for Shrinking?: YES');
else
DBMS_OUTPUT.PUT_LINE('Candidates for Shrinking?: NO');
end if;
end;
/



7. AUTO DOP - Parallel hint (/*+ parallel (x,4) */) doesn’t work with 11.2.0.2:

Solution: Apply patch 10628995 (or) goto 11.2.0.3 or up (or) remove x from the parallel hint (select /*+ parallel (4) */ count(*) from x;)


8. Index status "INPROGRS"? -- Domain Index

-- How to find INPROGRS domain indexes
SELECT index_name, table_name, status, domidx_status, domidx_opstatus FROM dba_indexes WHERE status = 'INPROGRS';

-- If I try to drop index, what happens?
DROP INDEX OSP.BCDMI_IDX_BUS_NAME;
-- Error: ORA-29868: cannot issue DDL on a domain index marked as LOADING

-- If I try to rebuild index, what happens?
ALTER INDEX OSP.IDX_BUS_NAME REBUILD PARTITION DNB_MARKET_INSIGHT_201206;
-- Error:
ERROR at line 1:
ORA-29952: cannot issue DDL on a domain index partition marked as LOADING

-- Fix:
-- Drop the index FORCE.
DROP INDEX DNB.BCDMI_IDX_BUS_NAME FORCE;

-- Recreate the index.
CREATE INDEX OSP.BCDMI_IDX_BUS_NAME ON OSP.BLD_COMM_DNB_MARKET_INSIGHT
(BUS_NAME)
INDEXTYPE IS CTXSYS.CONTEXT;


9. How to stop propagting tranctions in Oracle streams. -- Tag

execute DBMS_STREAMS.SET_TAG(hextoraw('99'));
COMMIT;

DELETE FROM APP.SESSION WHERE CREATIONTIME < trunc(SYSDATE-7);
COMMIT;
ALTER INDEX APP.SDO$PSESSION$ REBUILD ONLINE;

execute DBMS_STREAMS.SET_TAG(NULL);
COMMIT;


10. Myth behind the plan directive values of PARALLEL_DEGREE_LIMIT_P1 in DBRM?

Why would you see 2x+1 sessions in DBRM, where x is value of PARALLEL_DEGREE_LIMIT_P1?

formula: 2x+1

Where 2 is:
first one is for "join operation".
second one is for "sort operation".

where x is:
Value of PARALLEL_DEGREE_LIMIT_P1 when creating the plan directive
+1 is for "Query Coordinator".

So,
When you give PARALLEL_DEGREE_LIMIT_P1 => 4

Total: 2x+1 = 9 -- Maximum of 9 sessions.


11. TNS:connection closed:

Oracle Error:
TNS-12537: TNS:connection closed

Issue could be related to the permission on the $ORACLE_HOME/bin/oracle file too!!

-- Check the current permission of oracle file:
$ /home/oracle > cd /u01/app/oracle/product/11.2.0/dbhome_1/bin;ls -la oracle
-rwxrwsr-x 1 oracle dba 232443941 Jul 3 11:26 oracle*

-- Fix:
$ /u01/app/oracle/product/11.2.0/dbhome_1/bin > chmod 6751 oracle
$ /u01/app/oracle/product/11.2.0/dbhome_1/bin > ls -la oracle
-rwsr-s--x 1 oracle dba 232443941 Jul 3 11:26 oracle*


12. How can i remove a job from another user?

-- Check the Job id and broken status
select job, schema_user, last_date, broken, what from dba_jobs where schema_user in ('XYZ','ABC');

-- Is it still running?
select * from dba_jobs_running where job in (15490, 7602);


-- Remore the Job Id
EXECUTE SYS.DBMS_IJOB.REMOVE (15490);
EXECUTE SYS.DBMS_IJOB.REMOVE (7602);
COMMIT;



13. Snapshot / Backup controlfile location, must be shared location in RAC environment from 11gR2:

The snapshot controlfile MUST be accessible by all nodes of a RAC database; if the snapshot controlfile does not reside on a shared device, below error will be raised at the time of RMAN backup while taking snapshot of controlfile.
This applies to backing up controlfile using SQL*Plus / having autobackup of controlfile configured on non shared location.

Error:
ORA-00245: control file backup operation failed

Solution:
Solution is the change the Snapshot /backup controlfile location to shared device.

rman target /

show all;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ODS_T1_BACKUP_01/snapcf_ODS.f';

show all;