Monday, September 2, 2013

Oracle masala at work - 5:

Oracle masala at work - 5:


1. Exadata Instance crash with - ORA-00600: internal error code, arguments: [kjblpkeydrmqscchk:pkey] on 11.2.0.3 BP7
2. Bug 15936951: AUTO DOP IN 11.2.0.3 NOT OBEYING PARALLEL DEGREE LIMIT AS IT DID IN 11.2.0.2
3. Logical block corruption in Oracle:
4. Modifying Scan name:
5. Online rebuild index fails with ORA-08104:
6. How to change the session level parameters for a specific OS user:
7. NLSSORT filter shown in the SQL plan, causing the FULL table scans:
8. Modify the Timezone on Grid Infrastructure from EST to GMT:
9. How to switch the SCAN IP address:


Details:
1. Exadata Instance crash with - ORA-00600: internal error code, arguments: [kjblpkeydrmqscchk:pkey] on 11.2.0.3 BP7

Fix:
Apply Exadata Patch 14409183 on 11.2.0.3 BP7.

No-Exadata Instance crash with - ORA-00600: internal error code, arguments: [kjblpkeydrmqscchk:pkey] in 11.2.0.3
Fix: Hitting the bug-13397104 please apply the latest 11.2.0.3.4 GI PSU which will fix this issue. or apply the Patch 13397104 on 11.2.0.3.

Other workaround:
Setting the _gc_read_mostly_locking=false (11.2.0.3)


2. Bug 15936951: AUTO DOP IN 11.2.0.3 NOT OBEYING PARALLEL DEGREE LIMIT AS IT DID IN 11.2.0.2

Issue:
There is a hint that has been identified to cause this issue : /*+ parallel(table_alias) */
When we use the hint just with table_name/table_alias, it's calculating the AUTO DOP wrongly, causing other statements to queue.

As of this note no patch available,

WORKAROUND:
Do not specify parallel(table_alias) hints.
-- OR --

If we change this to any of the below, it should calculate DOP correctly and not queue:
/*+ parallel(table_alias, number) */
/*+ parallel */
-- OR --

Implement DBRM to cap the parallel threads.


3. Logical block corruption in Oracle:

Key Points:
RMAN Vs dbv Vs ANALYZE VALIDATE STRUCTURE CASCADE ?
Data block Vs Index block error (ORA-01498/ORA-01499) ?
Get the block dump for further analysis, what caused the corruption ?

RMAN Level 0 (Full backup) is required to fix the block corruption.
11g Active Data Guard Automatic Block Repair works well too.

Error/Issue:
SELECT Query failed with "ORA-12805: parallel query server died unexpectedly"

Alert Log Error:
ORA 600 [kjblpkeydrmqscchk:pkey]
ORA 603
ORA 7445 [_wordcopy_bwd_dest_aligned()+185]
ORA 7445 [_wordcopy_bwd_dest_aligned()+209]

a.
-- Export(expdp) error:
export - "ORA-00600: internal error code, arguments: [kluuscn_50]"


Tryed export with below init parameters, but no luck:
DB_BLOCK_CHECKSUM = OFF
DB_BLOCK_CHECKING = OFF


-- Tryed "DBMS_REPAIR.skip_corrupt_blocks" before taking export, "but no luck".
SQL> BEGIN
DBMS_REPAIR.skip_corrupt_blocks (
schema_name => 'UT_REP',
object_name => 'T1',
object_type => DBMS_REPAIR.table_object,
flags => DBMS_REPAIR.skip_flag);
END;
/
2 3 4 5 6 7 8
PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL> exit

b.
-- DBMS_STATS error:
dbms_stats - "ORA-20011: Approximate NDV failed: ORA-12805: parallel query server died unexpectedly"

c.
-- Analyze table error:
SQL> analyze table ut_rep.t1 VALIDATE STRUCTURE CASCADE;

analyze table ut_rep.t1 VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01498: block check failure - see trace file


-- How many files and blocks where effected/Identifying corrupt blocks?:
BACKUP VALIDATE TABLESPACE UT_COMP; -- Checks Physical corruption only on given tablespace level.
BACKUP VALIDATE CHECK LOGICAL TABLESPACE UT_COMP; -- Checks Logical and Physical corruption on given tablespace level.

BACKUP VALIDATE CHECK LOGICAL DATABASE; -- Checks Logical and Physical corruption on the database level.


RMAN e.g..:
rman target /
spool log to 'x.log'

BACKUP VALIDATE CHECK LOGICAL TABLESPACE UT_COMP;

spool log off;
exit;

-- Displays information about database blocks that were corrupted after running the above RMAN command.
-- When the RMAN recover the corrupt block then automatically updates this view.
select * from V$DATABASE_BLOCK_CORRUPTION;

-- Identifying the object name
select owner, segment_name, segment_type, partition_name
from dba_extents
where file_id = 486
and 2048140 between block_id and block_id + blocks - 1;

Fix:
a.
$ rman target / catalog_user/catalog_user_password@catalogdb

BLOCKRECOVER CORRUPTION LIST;
BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-10';
-- (OR) --

-- This checks FRA and backupset.
$ rman target / catalog_user/catalog_user_password@catalogdb
BLOCKRECOVER DATAFILE BLOCK ;
-- (OR) --

-- Recovery from backupset
BLOCKRECOVER DATAFILE BLOCK DATAFILE BLOCK FROM BACKUPSET;
-- (OR) --

-- Recovery from image copy
BLOCKRECOVER DATAFILE BLOCK DATAFILE BLOCK FROM DATAFILECOPY;
-- (OR) --

-- Recovery from backupset which have "FULL" tag
BLOCKRECOVER DATAFILE BLOCK DATAFILE BLOCK FROM TAG = FULL;

b.
-- If you don't have FULL RMAN backups!!! If it's OK to miss some data !!!
-- In this example, I am creating a GOOD_T1 table from T1 tables, without 3 corrupted blocks (2048140,2048862,2039785).
create table ut_rep.good_t1
nologging
parallel 8
tablespace ut_comp
as
select * from ut_rep.t1
where rowid not in (select rowid from ut_rep.t1
where to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') in (2048140,2048862,2039785));

-- *****
-- I used below logic to pull the effected blocks to analyze the data.
-- *****
rowid,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as obj_id,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as file_num,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as block_num,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as row_slot

c.
If above method did not work, we need to go by ROWID's method:

1a.
Find the object_id:
select data_object_id from dba_objects where owner = 'UT_REP' and OBJECT_NAME = 'T398'
-- 6540044

1b.
Problem file# - blocks#'s:
486 - 3619385
489 - 3006218

select dbms_rowid.rowid_create(rowid_type => 1,
object_number => 6540044,
relative_fno => 486,
block_number => 3619385,
row_number => 0) begin_rowid,
dbms_rowid.rowid_create(rowid_type => 1,
object_number => 6540044,
relative_fno => 486,
block_number => 3619385 + 1,
row_number => 0) end_rowid
from dual;

begin_rowid - AAY8sMAHmAANzo5AAA
end_rowid - AAY8sMAHmAANzo6AAA

select dbms_rowid.rowid_create(rowid_type => 1,
object_number => 6540044,
relative_fno => 489,
block_number => 3006218,
row_number => 0) begin_rowid,
dbms_rowid.rowid_create(rowid_type => 1,
object_number => 6540044,
relative_fno => 489,
block_number => 3006218 + 1,
row_number => 0) end_rowid
from dual;

begin_rowid - AAY8sMAHpAALd8KAAA
end_rowid - AAY8sMAHpAALd8LAAA

1c.
create table UT_REP.T398_good
as
SELECT /*+ ROWID(A) */ * FROM ut_rep.t398 A where rowid < 'AAY8sMAHmAANzo5AAA'; insert into UT_REP.T398_good SELECT /*+ ROWID(A) */ * FROM ut_rep.t398 A where rowid > 'AAY8sMAHmAANzo6AAA' and rowid < 'AAY8sMAHpAALd8KAAA'; insert into UT_REP.T398_good SELECT /*+ ROWID(A) */ * FROM ut_rep.t398 A * where rowid > 'AAY8sMAHpAALd8LAAA';

1d.
truncate table ut_rep.t398;
alter table ut_rep.t398 move;

select 'alter index ut_rep.'||index_name||' rebuild;' from dba_indexes where owner = 'UT_REP' and table_name = 'T398';

1e.
insert /*+ append */ into ut_rep.t398 select * from UT_REP.T398_good;
commit;

-- Missing records count:
select count(*) from ut_rep.t398;
select count(*) from UT_REP.T398_good;

d.
dbed utility !! -- Not tested.


-- Another very importenet thing is, What caused the logical block corruption?
-- Oracle might ask for block dumps for analysis.
sqlplus / as sysdba
oradebug setospid
oradebug unlimit
alter system dump datafile '' block ;
oradebug tracefile_name
exit


4. Modifying Scan name:

a. -- If existing databases are running on that cluster backup the spfile of all databases into a different location.
Example : create pfile='location' from spfile;

b. -- Update the pfile in the location from Step1 with the new scan name for remote_listener parameter.

c. -- Check if the new scan name has been setup on the cluster.

-- nslookup

Example:
nslookup ut-atl1.dwire.com

Server: atl1.dwire.com
Address: 10.xx.xxx.xx

Name: ut-atl1.dwire.com
Addresses: 10.xx.xxx.xx, 10.xx.xxx.xx, 10.xx.xxx.xx

d. -- Shutdown all instances on that cluster, stop the existing scan listener on the cluster.
-- Verify if everything is stopped.


Set the ORACLE_HOME to GRID_HOME.
. oraenv
+ASM1

$GRID_HOME/bin/srvctl stop scan_listener
$GRID_HOME/bin/srvctl stop scan

e. -- Configure new Scan Name.

-- $GRID_HOME/bin/srvctl modify scan -n

Example:
$GRID_HOME/bin/srvctl modify scan -n ut-atl1.dwire.com

f. -- Modify Scan Name in CRS. Execute this command as root

-- sudo crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE="

Example:
cd $GRID_HOME/bin/
sudo crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=ut-atl1.dwire.com"

g. $GRID_HOME/bin/srvctl modify scan_listener -u

h. $GRID_HOME/bin/srvctl start scan_listener

i. -- Confirm if the new scan is working properly and the correct name.
$GRID_HOME/bin/srvctl status scan_listener
$GRID_HOME/bin/srvctl config scan

j. -- Startup the instance through sqlplus in nomount state.

k. -- Create the spfile inside the ASM with the pfile that was changed as part of Step b.

l. -- shutdown the instance.

m. -- Start the database through srvctl.

n. -- Verify if everything is working properly. Check the remote_listener parameter value in the database.
-- It should have the new scan name.

Example :
show parameter remote_listener


5. Online rebuild index fails with ORA-08104:

Error:
SQL> alter index ut_rep.ih132 rebuild online;
alter index ut_rep.ih132 rebuild online
*
ERROR at line 1:
ORA-08104: this index object 2249271 is being online built or rebuilt

SQL> exit

Fix:
During the kill immediate(ALTER SYSTEM KILL SESSION ',,@2' IMMEDIATE;) some extents still exists on the tablespace and the index has a marker, when a online operation is initiated.
To cleanup use the DBMS_REPAIR.ONLINE_INDEX_CLEAN package as below.

a.
SELECT object_id FROM dba_objects WHERE object_name = 'IH132';
-- 123456789

b.
DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 123456789;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
COMMIT;
END;
/


6. How to change the session level parameters for a specific OS user:

a.
connect sys@DB1

create or replace trigger logon_test
AFTER LOGON ON test.SCHEMA

BEGIN

IF sys_context('USERENV','OS_USER') in ('vdumpa')
THEN
execute immediate ('ALTER SESSION SET optimizer_features_enable=''11.2.0.1''');
END IF;

END;
/

-- To check the execute immediate:
SQL> select 'ALTER SESSION SET optimizer_features_enable=''11.2.0.1''' from dual;

'ALTERSESSIONSETOPTIMIZER_FEATURES_ENABLE=''11.2.0.1''
------------------------------------------------------
ALTER SESSION SET optimizer_features_enable='11.2.0.1'

SQL> select 'ALTER SESSION SET "_push_join_union_view"=false' from dual;

'ALTERSESSIONSET"_PUSH_JOIN_UNION_VIEW"=FALSE'
------------------------------------------------
ALTER SESSION SET "_push_join_union_view"=false

-- Few important sys_context parameters:
sys_context('USERENV','HOST') -- Host name
sys_context('USERENV','OS_USER') -- OS user name
sys_context('USERENV','SERVICE_NAME') -- Service name
sys_context('USERENV','SESSION_USER') -- DB user name
sys_context('USERENV','TERMINAL') -- Terminal name
sys_context('USERENV','MODULE') -- Module name
sys_context('USERENV','IP_ADDRESS') -- IP address of the machine from which the client is connected.

b.
-- vdumpa is logging in as test user.
connect test@DB1

c.
-- This is the test to check the session parameter value.

connect sys@DB1
SQL> show parameter optimizer_features_enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 11.2.0.2

SQL> select sid, username, osuser from gv$session where username = 'TEST';

SID USERNAME OSUSER
---------- ------------------------------ ------------------------------
486 TEST vdumpa

SQL> select name,value from v$ses_optimizer_env where sid = 486 and name like '%optimizer_features_enable%';

NAME VALUE
---------------------------------------- -------------------------
optimizer_features_enable 11.2.0.1


7. NLSSORT filter shown in the SQL plan, causing the FULL table scans:

a.
-- Good plan
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('ap8rhqm23xdbz',0));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID ap8rhqm23xdbz, child number 0
-------------------------------------
SELECT * FROM XYZ WHERE BATCHMODULEID=:B1

Plan hash value: 848178827

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| XYZ | 1 | 189 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0021226 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("BATCHMODULEID"=:B1)


19 rows selected.

-- Bad plan
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('ap8rhqm23xdbz',1));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID ap8rhqm23xdbz, child number 1
-------------------------------------
SELECT * FROM XYZ WHERE BATCHMODULEID=:B1

Plan hash value: 2660499978

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4378 (100)| |
|* 1 | TABLE ACCESS FULL| XYZ | 7400 | 1365K| 4378 (2)| 00:00:53 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NLSSORT(INTERNAL_FUNCTION("BATCHMODULEID"),'nls_sort=''GENERIC_B
ASELETTER''')=NLSSORT(:B1,'nls_sort=''GENERIC_BASELETTER'''))


19 rows selected.

Note: Notice the NLSSORT filter in the bad plan.. Causing FULL table scans!!!
b.
SQL> set linesize 120;

SELECT inst_id, hash_value, child_number, PLAN_HASH_VALUE,OLD_HASH_VALUE, executions, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime
from gv$sql
where sql_id='ap8rhqm23xdbz';
SQL> SQL> SQL> 2 3
INST_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE OLD_HASH_VALUE EXECUTIONS AVG_ETIME
---------- ---------- ------------ --------------- -------------- ---------- ----------
4 3292444031 0 2660499978 3621482215 21 3.72152276
2 3292444031 0 2660499978 3621482215 5 41.48197
2 3292444031 1 2660499978 3621482215 5 43.6717298

c.
-- Created function Index to fix the issue:
SQL> create index KFAXPROD.mh_idx1_XYZ on KFAXPROD.XYZ (NLSSORT(BATCHMODULEID,'NLS_SORT=GENERIC_BASELETTER'))
tablespace KFAX_DATA;

d.
-- After index creation both hash values average execution time dropped dramatically.
SQL> set linesize 120
SQL> SELECT inst_id, hash_value, child_number, PLAN_HASH_VALUE,OLD_HASH_VALUE, executions, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime
from gv$sql
where sql_id='ap8rhqm23xdbz';
2 3
INST_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE OLD_HASH_VALUE EXECUTIONS AVG_ETIME
---------- ---------- ------------ --------------- -------------- ---------- ----------
1 3292444031 0 848178827 3621482215 11 .000612636
2 3292444031 0 3848578838 3621482215 5 .0176316


8. Modify the Timezone on Grid Infrastructure from EST to GMT:

a. Please make sure to stop the CRS on one node

b. Change the Timezone on the OS side.

c. To display current setting:
$ cat /etc/sysconfig/clock
ZONE="America/New_York"
UTC=false
ARC=false

d. To find out all valid setting: ls -l /usr/share/zoneinfo and Anything that appears in this directory is valid to use.

e. Once OS default timezone is changed, make sure:
1. For 11.2.0.1, shell environment variable TZ is set correctly for grid/oracle user and root.
2. For 11.2.0.2 and above, TZ entry in $GRID_HOME/crs/install/s_crsconfig__env.txt sets to correct time zone.
$cd /u01/app/grid/11.2.0.3/crs/install
$ grep TZ s_crsconfig__env.txt
#TZ=America/New_York
TZ=GMT

f. start the CRS and all DB's.

g. Go to the next node and repeat the same.

Reference:
Note: 1209444.1 - How To Change Timezone for 11gR2 Grid Infrastructure


9. How to switch the SCAN IP address:

Sometimes we need to use the same SCAN name, when we are building the new servers to support no application connection change during the cut over.
In normal procedure, SCAN IP's should be in DNS (Not in /etc/host file) and build the GRID InfoStructure. Here we will have IP's in /etc/host file to build the servers; during the cut over we move it to DNS.

Here is process that we useed to switch the scan IP address:

a. Have network team change the DNS ip address's for the SCAN name.

b. Remove scan name entry in /etc/hosts file on all nodes.

c. Run nslookup on SCAN name and check returns new IP addresses.

d. Shutdown scan listener:
srvctl stop scan_listener
srvctl stop scan

e. Modify SCAN:
srvctl modify scan –n

f. Restart scan listener:
srvctl modify scan_listener -u
srvctl start scan_listener

g. To confirm the change:
srvctl config scan
srvctl config scan_listener

h. Add new scan listener to the system(listener.ora) on all nodes.

Note: This procedure is somewhat similar to above "Modifying Scan name" procedure.

Reference:
Note: 972500.1 - How to Modify SCAN Setting or SCAN Listener Port after Installation

Thursday, October 11, 2012

DBMS_BACKUP_RESTORE.resetCfileSection() package:

DBMS_BACKUP_RESTORE.resetCfileSection() package:


-- Note that you can execute the following PL/SQL to remove all entries from V$RMAN_STATUS:
-- The preceding function removes all job-related entries. No rows will be visible until new backup jobs are shown in V$RMAN_BACKUP_JOB_DETAILS.

sqlplus / as sysdba
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(28);
commit;


-- Removing entries in v$DATABASE_BLOCK_CORRUPTION view
-- Be aware that the corrupted blocks will remain corrupted until we fix the block corruption issue.
-- Error: Bug 13386807 : RMAN BACKUP FAILED ORA-03114 ORA-07445: [KRBINSERTBCR()+665]

exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35);


-- Issue: V$DATABASE_BLOCK_CORRUPTION has rows referring to a non-existing datafile.

-- Removing entries in v$backup_corruption view
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(17);
-- Removing entries in v$copy_corruption view
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(18);


-- Removing entries in v$deleted_object view on RMAN catalog
-- Issue: RMAN backup is suddenly running very slowly after having deleted a large number of backupsets from catalog.

exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(19);


-- Removing entries in v$archived_log view
-- Issue: Removing entries in v$archived_log referencing a particluar DEST_ID

exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(11);


-- Removing entries in v$backup_spfile on RMAN catalog
-- RMAN Error: ORA-01400: cannot insert NULL into ("RMAN"."BSF"."MODIFICATION_TIME")

exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(21);

Thursday, September 6, 2012

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;

Friday, June 29, 2012

Oracle Exadata Preventive Maintenance:

Oracle Exadata Preventive Maintenance:

- Validate software versions are at recommended levels - [ID 888828.1]
- Validate firmware versions are at recommended levels - [ID 888828.1]
- Validate health of all hardware components in the machine - (exachk or HealthCheck [ID 1070954.1])
- Visually inspect the machine for failed or damaged components like slide rails, cable management arms


Exadata machine includes the following consumable components:

* Batteries in the disk controllers in the storage and database servers which protect the write cache in the event of a power failure.

* Energy Storage Module (ESM) in the PCIe flash cards in the storage servers which protect the DRAM cache in the event of a power failure.

Note: Failure of these consumable components will adversely impact performance (Note - there will be no loss of data or wrong results).


Replacement Schedule for Consumable Components:

Note:
1. Pro-active replacement schedule assumes worst case life expectancies.
2. Years 6,7, and later assume system was shipped within 5 years from last ship date or extended support is being offered.

 
Maintenance Window for pro-active replacement:


Note:
1. Times are estimates and will vary from system to system.
2. Full system downtime estimates vary based on whether 1 or 2 service engineers are used.


You will see below alert from cell nodes:
Hardware Alert: CRITICAL

Description: All Logical drives are in WriteThrough caching mode. Either battery is in a learn cycle or it needs to be replaced. Please contact Oracle Support


Affected Cell       Name                              atl03cel01

                           Server Model                  SUN MICROSYSTEMS SUN FIRE X4275 SERVER SATA

                           Chassis Serial Number     1005XFG05E

                            Version                           OSS_11.2.2.2.0.9909907V2_LINUX.X64_110310


Recommended Action: Battery is either in a learn cycle or it needs replacement. Please contact Oracle Support.

Thursday, May 31, 2012

NetBackup setup over InfiniBand on Exadata:

NetBackup setup over InfiniBand on Exadata:

In order to backup all the data within the Exadata compute nodes, we need to use a dedicated media server which is directly connected to the frame through an infiniband connection.

The storage admins must specify the infiniband IP as the target. However, the master server is not on the infiniband network. We must "trick" the master server to use the proper IP. In the local /etc/hosts file, associate the primary IP on a routeable network to the infiniband hostname (hostname-priv). The master server will try to backup the hostname-priv through the routable network. When the media servers recieves the request, it will lookup the IP associated with the hostname-priv, which will (on the media server) be the infiniband IP.

On the target exadata DB host, you must specify the media server name associated to the infiniband IP in the /etc/hosts file.


Example setup:

-- NetBackup Master Server /etc/hosts
# Aliases needed to trick exadata to backup over infiniband.
10.62.121.100    atl01db01-priv.corp.dwire.com       atl01db01-priv

-- Media Server /etc/hosts
### Compute Node Private Interface details farm 1
192.168.10.1    atl01db01-priv.corp.dwire.com       atl01db01-priv

-- DB server /etc/hosts
# EXADATA NBU Media Server Infiniband
192.168.16.89    atli1.corp.dwire.com       atli1

-- The IPs of the example target server (atl01db01) are:
192.168.10.1      atl01db01-priv.corp.dwire.com    atl01db01-priv
10.62.125.100    atl01db01.corp.dwire.com           atl01db01
10.62.121.100    atl0101.corp.dwire.com               atl0101


-- bp.conf file on DB server
atlcom01db01:
$ cat /opt/openv/netbackup/bp.conf
SERVER = atli1.corp.dwire.com
CLIENT_NAME = atlcom01db01-priv