Wednesday, January 6, 2021

Independent Watch Brands I follow


https://passionfinejewelry.com/collections/watches



HOT Independent Watch Brands:
https://ming.watch/

https://kuronotokyo.com/

https://sartory-billard.com/

https://lundis-bleus.com/en/shop/

https://atelierwen.com/

http://habring2.com/index.php/en/

https://www.sufhelsinki.com/

https://www.ochsundjunior.swiss/#


COOL Independent Watch Brands:
https://oakandoscar.com/collections/all-watches

https://halioswatches.com/

https://www.unimaticwatches.com/current-editions/

https://doxawatches.com/

https://synchronusa.com/pre-order/

https://baltic-watches.com/en

https://www.christopherward.com/watches

https://www.lorierwatches.com/collections/watches

https://usd.farer.com/collections/all-watches

https://anordain.com/collections/all

https://www.peltonusa.com/watches

https://www.nezumistudios.com/product-category/watches/

https://www.autodromo.com/watches/

https://weisswatchcompany.com/collections/all

https://montawatch.com/collections

https://danhenrywatches.com/collections/

https://www.traskawatch.com/en

https://zeloswatches.com/collections/all

https://www.undone.com/en/collection/lists/collaborations

https://www.bangalorewatchco.in/

https://www.wmtwatches.com/

https://ow-watch.ch/collections/main

https://www.stowa.de/Flieger/

https://www.laco-watches.com/en/index

https://www.furlanmarri.com/


High End Independent Watch Brands:
https://www.fpjourne.com/en

https://www.urwerk.com/

https://www.mbandf.com/en

https://ressencewatches.com/

https://www.rogerdubuis.com/en

https://www.voutilainen.ch/the-watches/

https://www.debethune.ch/en

https://www.romaingauthier.com/

https://www.hytwatches.com/

https://www.h-moser.com/

https://www.sarpanevawatches.com/

https://www.alange-soehne.com/en/home


Watch Collaborations:
https://shop.hodinkee.com/collections/limited

https://www.massenalab.com/

https://us.bape.com/collections/collaboration/GOODS

https://revolution.watch/shop/watches/limited-edition-pieces.html

https://shop.fratello.com/collections/limited-editions

https://windupwatchshop.com/collections/limited-editions

https://thennowbeyond.thehourglass.com/collections/commemorative-watches

https://www.harrods.com/en-gb/sets/fine-watches-exclusives-limited-editions

https://www.bucherer.com/bucherer-blue.html



Posted by at No comments:

Sunday, December 1, 2019

Watch Strap sites I follow

Watch Strap sites I follow:

https://shop.hodinkee.com/collections/straps

https://shop.analogshift.com/collections/straps

https://windupwatchshop.com/collections/straps

https://www.crownandbuckle.com/

https://shop.fratello.com/collections/straps

https://shop.synchronwatches.com/product/tropic-strap-2/

https://aquastar.ch/product/genuine-tropic-22mm-strap-for-aquastar-deepstar/

https://molequin.com/collections/all-straps

http://www.basandlokes.com/view-all-watch-straps/

https://www.veblenist.com/straps

https://revolutionwatch.com/shop/accessories/watch-straps.html

https://www.cheapestnatostraps.com/

https://www.lugs.shop/shop/

https://delugs.com/

https://www.genteel-handmade.com/products/

https://erikasoriginals.com/

https://www.laccrado.com/

http://www.strapsbyfleur.com/

http://www.kastantona.com/shop/

https://www.bandrbands.com/

https://www.colareb.it/en/products/watch-strap/

https://www.uncleseiko.com/store/c1/Featured_Products.html

https://forstnerbands.com/collections/full-collection

https://bulangandsons.com/collections/watch-straps-accessories

https://en.accurate-form.jp/collections/all-strap

Posted by at 1 comment:

Sunday, April 29, 2018

Wristwatch News and Reviews sites I follow

Just want to share some of the Wristwatch News and Reviews online sites I follow, below sites are not in any particular order.

https://www.hodinkee.com/

http://www.timezone.com/

https://www.ablogtowatch.com/

https://www.fratellowatches.com/

http://watchesbysjx.com/

http://wornandwound.com/

https://monochrome-watches.com/

https://timeandtidewatches.com/

https://www.plus9time.com/

https://www.watchprosite.com/

https://www.watchtime.com/

https://www.watchtime.in/

https://iwmagazine.com/

https://timeandtidewatches.com/

http://www.timeandwatches.com/

https://quillandpad.com/

http://www.onthedash.com/

https://www.revolution.watch/

https://www.deployant.com/

http://www.atimelyperspective.com/

https://www.crownandbuckle.com/blog/

https://www.timeline.watch/?year_filter=1970

http://oceanictime.blogspot.com/

Posted by at No comments:

Tuesday, March 6, 2018

Vintage watch online sites I follow

Just want to share some of the Vintage watch online sites I follow, other than www.ebay.com, www.chrono24.com and rolexpassionmarket.com.
Below sites are not in any particular order.

https://shop.hodinkee.com/collections/vintage-watches

https://shop.analogshift.com/collections/watches

https://www.shucktheoyster.com/portfolio-category/vintage-watches/

https://thekeystone.com/collections/mens

https://www.craftandtailored.com/collections/current-timepiece-collection

https://revolution.watch/shop/watches/vintage.html

https://shinyhappyobjects.com/watches

http://www.db1983.com

https://www.windvintage.com/watches

https://amsterdamvintagewatches.com/shop/

https://www.acollectedman.com/collections/all

https://mentawatches.com/shop/

http://www.matthewbaininc.com/vintage-watches/

https://www.harbor-watches.com/

https://www.lunaroyster.com/shop

https://www.ssongwatches.com/collections/vintage

https://www.coolvintagewatches.com/shop-2

https://www.visionvintagewatches.com/vintage-watches

https://www.omegaenthusiastltd.com/

https://www.thosewatchguys.com/watches/

https://www.carsandwatches.com/watches/new-watches-arrivals

https://bulangandsons.com/collections/watches-for-sale

https://vintagewatchspecialist.com/

https://ticktockguru.com/collections/watches

https://theoandharris.com/product-category/vintage-watches/

https://www.watchpool24.com/en/all-offers/all-watches

https://shop.watchsteez.com/collections/watches

https://corrvintagewatches.com/shop/

https://classicwatchny.com/watch-collection-2/

https://brusselsvintagewatches.com/en/watches-for-sale/

https://universalgenevepolerouter.com/marketplace-adverts/

https://dannysvintagewatches.com/shop

https://www.retrowatchguy.com/collections/all?sort_by=created-descending

https://dutchwatchclassics.com/watches/

https://vintagewatchhunter.com/

https://thegrandseikoguy.com/product-category/grand-seiko/

https://thetimecurator.com/collections/frontpage

https://kleinvintagewatch.com/watchesforsale

https://www.dcvintagewatches.com/?Collection=All

https://www.hubcityvintage.com/shop

https://blairwatchproject.com/sales/

https://www.thetokeiclub.jp/

https://classicseiko.com

https://en.arbitro.shop/collections/vintage-watches

https://www.wristlabs.store/?Collection=All

https://watches83.com/en/

https://www.thepaleh.com/collections/vintage

https://hairspring.com/

https://centurytimewatches.co.uk/shop/

https://www.adamvintage.com/store/c1/vintage-watches

https://www.collectorscornerny.com/watches


https://www.watchrecon.com/

https://www.watchpatrol.net/

http://www.secondtimearoundwatchco.com/

https://wannabuyawatch.com/

https://www.hqmilton.com/timepieces/eras/vintage?era=vintage&sort=lowest-price

https://watchestobuy.com/
Posted by at No comments:

Monday, August 14, 2017

My other passion!!

Just want to post something not Oracle related, my other passion!!

Vintage Watch buying questioner:

Is the watch running? If yes, is it keeping time?
Do you know how old it is?
How long does it run if fully wound (if a manual-wind mechanical watch)?
Is the watch is manual-wind / Automatic / Quartz(Battery)?
Where did you get the watch?
Why are you selling it?
Do you know when it was last serviced?
Can you provide additional photos of the inner case back / outer case back / crown / serial number(s) / lugs / strap / buckle, etc.?
Do you know if the dial has been refinished or replaced?
Do you know if the dial has been re-lumed? Does the lume glow in the dark for a long period of time?
What is the length (in millimeters) of the watch from the left side of the case to the right side of the case excluding the crown?
Does it come with the original box and / or papers such as the directions and receipt?
Posted by at No comments:

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
Posted by at No comments:

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);
Posted by at 4 comments:

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;
Posted by at No comments:

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.
Posted by at 2 comments:

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
Posted by at 2 comments:

Tuesday, May 29, 2012

Configure InfiniBand Listener on Exadata:

Configure InfiniBand Listener on Exadata:

InfiniBand listener can be used for ETL's, RMAN active duplicate procedure etc.. for fast data transfer using IB network.


Make sure following entries are in sqlnet.ora file on all the database servers, we use soft link from GRID home to DB home.

AUTOMATIC_IPC = ON
names.directory_path = (TNSNAMES,EZCONNECT)
names.default_domain = world
name.default_zone = world
sqlnet.expire_time = 15



1.
Add/Identify 8 new IP addresses on the private Infiniband, which are different from existing "-priv" IPs (Same subnet as the IB network) -- Network Admin Task.

Add Private Virtutal IPs to /etc/hosts for all Exadata compute nodes and client (Informatica Servers or any other):

### Compute Node Listener Interface details
192.168.16.xx atl01db01-ibvip.corp.dwire.com atl01db01-ibvip
192.168.16.xx atl01db02-ibvip.corp.dwire.com atl01db02-ibvip
192.168.16.xx atl01db03-ibvip.corp.dwire.com atl01db03-ibvip
192.168.16.xx atl01db04-ibvip.corp.dwire.com atl01db04-ibvip
192.168.16.xx atl01db05-ibvip.corp.dwire.com atl01db05-ibvip
192.168.16.xx atl01db06-ibvip.corp.dwire.com atl01db06-ibvip
192.168.16.xx atl01db07-ibvip.corp.dwire.com atl01db07-ibvip
192.168.16.xx atl01db08-ibvip.corp.dwire.com atl01db08-ibvip

-- Infiniband Sample config:

cat /etc/sysconfig/network-scripts/ifcfg-bondib0

#### DO NOT REMOVE THESE LINES ####
#### %GENERATED BY CELL% ####
DEVICE=bondib0
USERCTL=no
BOOTPROTO=none
ONBOOT=yes
IPADDR=192.168.16.1
NETMASK=255.255.252.0
NETWORK=192.168.16.0
BROADCAST=192.168.19.255
BONDING_OPTS="mode=active-backup miimon=100 downdelay=5000 updelay=5000 num_grat_arp=100"
IPV6INIT=no
MTU=65520


2.
As root user, add secondary network to the cluster:

-- Currenly How many networks configured? -- Only one network found
/u01/app/11.2.0.2/grid/bin/crsctl stat res -t | grep net

Output:
$ /u01/app/11.2.0.2/grid/bin/crsctl stat res -t | grep net
ora.net1.network


 -- Add the second network
/u01/app/11.2.0.2/grid/bin/srvctl add network -k 2 -S 192.168.16.0/255.255.252.0/bondib0

-- Help 11.2.0.2
> srvctl add network -help

Adds a network configuration to the Oracle Clusterware.
Usage: srvctl add network [-k net_num] -S subnet/netmask/[if1[if2...]] [-w network_type] [-v]
-k net_num network number (default number is 1)
-S subnet/netmask/[if1[if2...]] NET address spec for network
-w network_type The network type (static, dhcp, mixed)
-h Print usage
-v Verbose output

-- Verify newly added network has been added
/u01/app/11.2.0.2/grid/bin/crsctl stat res -t | grep net

Output:
$ /u01/app/11.2.0.2/grid/bin/crsctl stat res -t | grep net
ora.net1.network
ora.net2.network


3.
-- Register newly created Virtual IP's with the CRS as root user:

cd /u01/app/11.2.0.2/grid/bin
srvctl add vip -n atl01db01 -A atl01db01-ibvip/255.255.252.0/bondib0 -k 2
srvctl add vip -n atl01db02 -A atl01db02-ibvip/255.255.252.0/bondib0 -k 2
srvctl add vip -n atl01db03 -A atl01db03-ibvip/255.255.252.0/bondib0 -k 2
srvctl add vip -n atl01db04 -A atl01db04-ibvip/255.255.252.0/bondib0 -k 2
srvctl add vip -n atl01db05 -A atl01db05-ibvip/255.255.252.0/bondib0 -k 2
srvctl add vip -n atl01db06 -A atl01db06-ibvip/255.255.252.0/bondib0 -k 2
srvctl add vip -n atl01db07 -A atl01db07-ibvip/255.255.252.0/bondib0 -k 2
srvctl add vip -n atl01db08 -A atl01db08-ibvip/255.255.252.0/bondib0 -k 2


4.
As oracle user:
-- Register newly added listener to CRS to make sure listener will come up automatically after the server restart.

srvctl add listener -l LISTENER_IB -k 2 -p TCP:1522
srvctl config listener

Output:
$ srvctl config listener
Name: LISTENER
Network: 1, Owner: oracle
Home: CRS home
End points: TCP:1521
Name: LISTENER_IB
Network: 2, Owner: oracle
Home: CRS home
End points: TCP:1522

-- Help 11.2.0.2
> srvctl add listener -help

Adds a listener configuration to the Oracle Clusterware.

Usage: srvctl add listener [-l lsnr_name] [-s] [-p "[TCP:]port[, ...][/IPC:key][/NMP:pipe_name][/TCPS:s_port] [/SDP:port]"] [-o oracle_home] [-k net_num]

-l lsnr_name Listener name (default name is LISTENER)
-o oracle_home ORACLE_HOME path (default value is CRS_HOME)
-k net_num network number (default number is 1)
-s Skip the checking of ports
-p "[TCP:]port[, ...][/IPC:key][/NMP:pipe_name][/TCPS:s_port] [/SDP:port]" Comma separated tcp ports or listener endpoints
-h Print usage


5.
Update $ORACLE_HOME/network/admin/tnsnames.ora file on all nodes:

Imp:
Each TNSNAMES.ORA file is different for each server depending on the Local and Remote listener settings.

Local Listener,    have information about local node and local IB vip host information.
Remote Listener, have information about other IB vip hosts information except the Local node


Sample entries for node 1 (atl01db01):

Local:
LISTENER_IBLOCAL.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db01-ibvip)(PORT = 1522))
)
)

LISTENER_IPLOCAL.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db01)(PORT = 1521))
)
)

Remote:
LISTENER_IBREMOTE.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db02-ibvip)(PORT = 1522)) -- no node 1 information on node 1!!
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db03-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db04-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db05-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db06-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db07-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db08-ibvip)(PORT = 1522))
)
)

LISTENER_IPREMOTE.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ATL02-SCAN)(PORT = 1521))
)
)

Imp:
Repeat the same for the other nodes, changing Local and Remote values accordingly.


6 .
-- Update the listeners_network DB parameter on all the databases with the listener addresses from both the networks, i.,e network1 and network2

alter system set listener_networks='((NAME=network2) (LOCAL_LISTENER=LISTENER_IBLOCAL)(REMOTE_LISTENER=LISTENER_IBREMOTE))','((NAME=network1)(LOCAL_LISTENER=LISTENER_IPLOCAL)(REMOTE_LISTENER=LISTENER_IPREMOTE))' scope=both sid='*';

-- Restart the listener and validate the entry.
show parameter listener_networks


-- What do I see with "ps -ef"?
> ps -ef | grep LISTENER
oracle 12874 1 0 May09 ? 00:01:20 /u01/app/11.2.0.2/grid/bin/tnslsnr LISTENER -inherit
oracle 24335 1 0 May12 ? 00:05:22 /u01/app/11.2.0.2/grid/bin/tnslsnr LISTENER_IB -inherit


7.
-- Add TNS entry on client side:

Sample TNS entry:

ODS.WORLD =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db01-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db02-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db03-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db04-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db05-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db06-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db07-ibvip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = atl01db08-ibvip)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ODS_INFA_SERVICE.world)
)
)


-- How did you create the Service?:
$ srvctl add service -d ODS -s ODS_INFA_SERVICE -r ODS1,ODS2,ODS3,ODS4 -a ODS5,ODS6,ODS7,ODS8 -q TRUE -m BASIC -e SELECT -z 180 -w 5 -j LONG -k2

-- Service configuration:
$ srvctl config service -d ODS -s ODS_INFA_SERVICE
Service name: ODS_INFA_SERVICE
Service is enabled
Server pool: ODS_ODS_INFA_SERVICE
Cardinality: 4
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 180
TAF failover delay: 5
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: ODS1,ODS2,ODS3,ODS4
Available instances: ODS5,ODS6,ODS7,ODS8


-- Help 11.2.0.2
> srvctl add service -help

Adds a service configuration to the Oracle Clusterware.

Usage: srvctl add service -d db_unique_name -s service_name {-r "preferred_list" [-a "available_list"] [-P {BASIC | NONE | PRECONNECT}]
-g pool_name [-c {UNIFORM | SINGLETON}] } [-k net_num] [-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]]
[-y {AUTOMATIC | MANUAL}] [-q {TRUE | FALSE}]
[-x {TRUE | FALSE}] [-j {SHORT | LONG}]
[-B {NONE | SERVICE_TIME | THROUGHPUT}]
[-e {NONE | SESSION | SELECT}] [-m {NONE | BASIC}]
[-z failover_retries] [-w failover_delay] [-t edition] [-f]

-d db_unique_name Unique name for the database

-s service Service name
-r "preferred_list" Comma separated list of preferred instances
-a "available_list" Comma separated list of available instances
-g pool_name Server pool name
-c {UNIFORM | SINGLETON} Service runs on every active server in the server pool hosting this service (UNIFORM) or just one server (SINGLETON)
-k net_num network number (default number is 1)
-P {NONE | BASIC | PRECONNECT} TAF policy specification
-l role Role of the service (primary, physical_standby, logical_standby, snapshot_standby)
-y policy Management policy for the service (AUTOMATIC or MANUAL)
-e Failover type Failover type (NONE, SESSION, or SELECT)
-m Failover method Failover method (NONE or BASIC)
-w integer Failover delay
-z integer Failover retries
-t edition Edition (or "" for empty edition value)
-j clb_goal Connection Load Balancing Goal (SHORT or LONG). Default is LONG.
-B Runtime Load Balancing Goal Runtime Load Balancing Goal (SERVICE_TIME, THROUGHPUT, or NONE)
-x Distributed Transaction Processing Distributed Transaction Processing (TRUE or FALSE)
-q AQ HA notifications AQ HA notifications (TRUE or FALSE)
Usage: srvctl add service -d db_unique_name -s service_name -u {-r "new_pref_inst"
-a "new_avail_inst"} [-f]
-d db_unique_name Unique name for the database
-s service Service name
-u Add a new instance to service configuration
-r new_pref_inst Name of new preferred instance
-a new_avail_inst Name of new available instance
-f Force the add operation even though a listener is not configured for a network
-h Print usage
Posted by at 5 comments:

Friday, May 25, 2012

Exadata Flash Cache Maintenance:

Exadata Flash Cache Maintenance:
This is one of the common maintenance task on Exadata cell nodes; just want to publish the steps to correct Flash Cache issue.

-- Pre re-create flashcache steps
1.
-- Run the following command to check if there are other offline disks
CellCLI> LIST GRIDDISK ATTRIBUTES name WHERE asmdeactivationoutcome != 'Yes'

If any grid disks are returned, then it is not safe to take the storage server offline because proper Oracle ASM disk group redundancy will not be intact.

Taking the storage server offline when one or more grid disks are in this state will cause Oracle ASM to dismount the affected disk group, causing the databases to shut down abruptly.

Output:
CellCLI> LIST GRIDDISK ATTRIBUTES name WHERE asmdeactivationoutcome != 'Yes' -- No records selected. -- Safe
CellCLI>

CellCLI> LIST GRIDDISK ATTRIBUTES name WHERE asmdeactivationoutcome = 'Yes'
DATA_CD_00_atl02cel06
DATA_CD_01_atl02cel06
DATA_CD_02_atl02cel06
DATA_CD_03_atl02cel06
DATA_CD_04_atl02cel06
DATA_CD_05_atl02cel06
DATA_CD_06_atl02cel06
DATA_CD_07_atl02cel06
DATA_CD_08_atl02cel06
DATA_CD_09_atl02cel06
DATA_CD_10_atl02cel06
DATA_CD_11_atl02cel06
RECO_CD_00_atl02cel06
RECO_CD_01_atl02cel06
RECO_CD_02_atl02cel06
RECO_CD_03_atl02cel06
RECO_CD_04_atl02cel06
RECO_CD_05_atl02cel06
RECO_CD_06_atl02cel06
RECO_CD_07_atl02cel06
RECO_CD_08_atl02cel06
RECO_CD_09_atl02cel06
RECO_CD_10_atl02cel06
RECO_CD_11_atl02cel06
SYSTEMDG_CD_02_atl02cel06
SYSTEMDG_CD_03_atl02cel06
SYSTEMDG_CD_04_atl02cel06
SYSTEMDG_CD_05_atl02cel06
SYSTEMDG_CD_06_atl02cel06
SYSTEMDG_CD_07_atl02cel06
SYSTEMDG_CD_08_atl02cel06
SYSTEMDG_CD_09_atl02cel06
SYSTEMDG_CD_10_atl02cel06
SYSTEMDG_CD_11_atl02cel06


2.
-- Get a listing of physical disks and celldisk. You can use this later to match up the WWN's to ensure you are removing the correct module.
CellCLI> list physicaldisk
CellCLI> list celldisk

Output:
-- Missing disk output: - missing few disks from 3 and 4 in this output.
CellCLI> list physicaldisk
16:0 JK1130YAHARN4T normal
16:1 JK1130YAHBWWZT normal
16:2 JK1130YAHBV76T normal
16:3 JK1130YAHBV7TT normal
16:4 JK1130YAH49UJT normal
16:5 JK1130YAHBJ5HT normal
16:6 JK1130YAHBJNHT normal
16:7 JK1130YAHATA3T normal
16:8 JK1130YAHAJX7T normal
16:9 JK1130YAHBJNJT normal
16:10 JK1130YAHBSBZT normal
16:11 JK1130YAHBV7PT normal
[1:0:0:0] 5080020000c47c0FMOD0 normal
[1:0:1:0] 5080020000c47c0FMOD1 normal
[1:0:2:0] 5080020000c47c0FMOD2 normal
[1:0:3:0] 5080020000c47c0FMOD3 normal
[2:0:0:0] 5080020000c47eaFMOD0 normal
[2:0:1:0] 5080020000c47eaFMOD1 normal
[2:0:2:0] 5080020000c47eaFMOD2 normal
[2:0:3:0] 5080020000c47eaFMOD3 normal

-- Some times output may look like this. -- This output is from different server
CellCLI> list physicaldisk
24:0 JK1130YAG52NAT normal
24:1 JK1130YAG4EPZT normal
24:2 JK1130YAG0VABT normal
24:3 JK1130YAG536XT normal
24:4 JK1130YAG51TVT normal
24:5 JK1130YAG0VA9T normal
24:6 JK1130YAG51TUT normal
24:7 JK1130YAG0VRKT normal
24:8 JK1130YAG51ZYT normal
24:9 JK1130YAG0VD4T normal
24:10 JK1130YAG0VN3T normal
24:11 JK1130YAG52NKT normal
[1:0:0:0] 5080020000c4262FMOD0 normal
[1:0:1:0] 5080020000c4262FMOD1 normal
[1:0:2:0] 5080020000c4262FMOD2 normal
[1:0:3:0] 5080020000c4262FMOD3 normal
[2:0:0:0] 5080020000c421cFMOD0 normal
[2:0:1:0] 5080020000c421cFMOD1 normal
[2:0:2:0] 5080020000c421cFMOD2 normal
[3:0:0:0] 5080020000c422eFMOD0 normal
[3:0:1:0] 5080020000c422eFMOD1 poor performance
[3:0:2:0] 5080020000c422eFMOD2 poor performance
[3:0:3:0] 5080020000c422eFMOD3 poor performance
[4:0:0:0] 5080020000c427cFMOD0 normal
[4:0:1:0] 5080020000c427cFMOD1 normal
[4:0:2:0] 5080020000c427cFMOD2 normal
[4:0:3:0] 5080020000c427cFMOD3 normal

CellCLI> list celldisk
CD_00_atl02cel06 normal
CD_01_atl02cel06 normal
CD_02_atl02cel06 normal
CD_03_atl02cel06 normal
CD_04_atl02cel06 normal
CD_05_atl02cel06 normal
CD_06_atl02cel06 normal
CD_07_atl02cel06 normal
CD_08_atl02cel06 normal
CD_09_atl02cel06 normal
CD_10_atl02cel06 normal
CD_11_atl02cel06 normal
FD_00_atl02cel06 normal
FD_01_atl02cel06 normal
FD_02_atl02cel06 normal
FD_03_atl02cel06 normal
FD_04_atl02cel06 not present
FD_05_atl02cel06 not present
FD_06_atl02cel06 not present
FD_07_atl02cel06 not present
FD_08_atl02cel06 normal
FD_09_atl02cel06 normal
FD_10_atl02cel06 normal
FD_11_atl02cel06 normal
FD_12_atl02cel06 not present
FD_13_atl02cel06 not present
FD_14_atl02cel06 not present
FD_15_atl02cel06 not present


3.
-- Inactivate all the grid disks when Oracle Exadata Storage Server is safe to take offline using the following command:
CellCLI> ALTER GRIDDISK ALL INACTIVE

The preceding command will complete once all disks are inactive and offline.
Depending on the storage server activity, it may take several minutes for this command to complete.


4.
-- Verify all grid disks are INACTIVE to allow safe storage server shut down by running the following command:
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus
CellCLI> LIST GRIDDISK

If all grid disks are INACTIVE, then the storage server can be shutdown without affecting database availability

Output:
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus
DATA_CD_00_atl02cel06 UNKNOWN
DATA_CD_01_atl02cel06 UNKNOWN
DATA_CD_02_atl02cel06 UNKNOWN
DATA_CD_03_atl02cel06 UNKNOWN
DATA_CD_04_atl02cel06 UNKNOWN
DATA_CD_05_atl02cel06 UNKNOWN
DATA_CD_06_atl02cel06 UNKNOWN
DATA_CD_07_atl02cel06 UNKNOWN
DATA_CD_08_atl02cel06 UNKNOWN
DATA_CD_09_atl02cel06 UNKNOWN
DATA_CD_10_atl02cel06 UNKNOWN
DATA_CD_11_atl02cel06 UNKNOWN
RECO_CD_00_atl02cel06 UNKNOWN
RECO_CD_01_atl02cel06 UNKNOWN
RECO_CD_02_atl02cel06 UNKNOWN
RECO_CD_03_atl02cel06 UNKNOWN
RECO_CD_04_atl02cel06 UNKNOWN
RECO_CD_05_atl02cel06 UNKNOWN
RECO_CD_06_atl02cel06 UNKNOWN
RECO_CD_07_atl02cel06 UNKNOWN
RECO_CD_08_atl02cel06 UNKNOWN
RECO_CD_09_atl02cel06 UNKNOWN
RECO_CD_10_atl02cel06 UNKNOWN
RECO_CD_11_atl02cel06 UNKNOWN
SYSTEMDG_CD_02_atl02cel06 UNKNOWN
SYSTEMDG_CD_03_atl02cel06 UNKNOWN
SYSTEMDG_CD_04_atl02cel06 UNKNOWN
SYSTEMDG_CD_05_atl02cel06 UNKNOWN
SYSTEMDG_CD_06_atl02cel06 UNKNOWN
SYSTEMDG_CD_07_atl02cel06 UNKNOWN
SYSTEMDG_CD_08_atl02cel06 UNKNOWN
SYSTEMDG_CD_09_atl02cel06 UNKNOWN
SYSTEMDG_CD_10_atl02cel06 UNKNOWN
SYSTEMDG_CD_11_atl02cel06 UNKNOWN

-- This should be INACTIVE, when you do "ALTER GRIDDISK ALL INACTIVE"
CellCLI> LIST GRIDDISK
DATA_CD_00_atl02cel06 active
DATA_CD_01_atl02cel06 active
DATA_CD_02_atl02cel06 active
DATA_CD_03_atl02cel06 active
DATA_CD_04_atl02cel06 active
DATA_CD_05_atl02cel06 active
DATA_CD_06_atl02cel06 active
DATA_CD_07_atl02cel06 active
DATA_CD_08_atl02cel06 active
DATA_CD_09_atl02cel06 active
DATA_CD_10_atl02cel06 active
DATA_CD_11_atl02cel06 active
RECO_CD_00_atl02cel06 active
RECO_CD_01_atl02cel06 active
RECO_CD_02_atl02cel06 active
RECO_CD_03_atl02cel06 active
RECO_CD_04_atl02cel06 active
RECO_CD_05_atl02cel06 active
RECO_CD_06_atl02cel06 active
RECO_CD_07_atl02cel06 active
RECO_CD_08_atl02cel06 active
RECO_CD_09_atl02cel06 active
RECO_CD_10_atl02cel06 active
RECO_CD_11_atl02cel06 active
SYSTEMDG_CD_02_atl02cel06 active
SYSTEMDG_CD_03_atl02cel06 active
SYSTEMDG_CD_04_atl02cel06 active
SYSTEMDG_CD_05_atl02cel06 active
SYSTEMDG_CD_06_atl02cel06 active
SYSTEMDG_CD_07_atl02cel06 active
SYSTEMDG_CD_08_atl02cel06 active
SYSTEMDG_CD_09_atl02cel06 active
SYSTEMDG_CD_10_atl02cel06 active
SYSTEMDG_CD_11_atl02cel06 active


5.
-- Check the status of FLASHCACHE
CellCLI> LIST FLASHCACHE detail

Output: -- When have issue.
CellCLI> LIST FLASHCACHE detail
name: atl02cel06_FLASHCACHE
cellDisk: FD_10_atl02cel06,FD_09_atl02cel06,FD_01_atl02cel06,FD_11_atl02cel06,FD_03_atl02cel06,FD_08_atl02cel06,FD_00_atl02cel06,FD_02_atl02cel06
creationTime: 2010-07-14T19:51:22-04:00
degradedCelldisks: FD_13_atl02cel06,FD_14_atl02cel06,FD_07_atl02cel06,FD_15_atl02cel06,FD_05_atl02cel06,FD_06_atl02cel06,FD_04_atl02cel06,FD_12_atl02cel06
effectiveCacheSize: 182.625G
id: 60a179f7-2fdf-44f4-a63d-9cfdd03d42cc
size: 365.25G
status: warning

-- Status should be normal, but here it's warning...


6.
-- We can now drop the flashcache here using the command

CellCLI> drop flashcache all

This will ensure that on startup the flashcache does not start


7.
-- Stop the cell services using the following command:
CellCLI> ALTER CELL SHUTDOWN SERVICES ALL


8.
Unix SA -- REBOOT atl02cel06.(root) for replacing the FLASHCACHE cards

#shutdown -h -y now

The cell services will be started automatically.

---------------------------------------------------

a. Replace the failed flash disk based on the PCI number and FDOM number.
NOTE: You can also use the WWN from the "list physicaldisk" command earlier.
Example: [2:0:0:0] 5080020000fcf12FMOD0 normal
               [2:0:0:0] 5080020000f "cf12" FMOD0 normal
The number "cf12" above should be on an orange sticker on the back of each flash pci card.

b. Power up the cell. The cell services will be started automatically.

c. After boot, verify the replaced disks have the same firmware...

/opt/oracle.SupportTools/CheckHWnFWProfile -c strict
-OR-
dmesg | grep -i marvell # Verify all revisions are the same

-- If firmware is not the same across all disks, then do the following...
rm /opt/oracle.cellos/TRIED_FW_UPDATE_ONCE
reboot
---------------------------------------------------



-- Re-create flashcache Steps
9.
-- Configure Flash Cache

CellCLI> drop celldisk all flashdisk force
CellCLI> create celldisk all flashdisk
CellCLI> create flashcache all
CellCLI> LIST FLASHCACHE detail


10.
-- Bring all grid disks online using the following command:

CellCLI> ALTER GRIDDISK ALL ACTIVE

Note:
-- When the grid disks become active, Oracle ASM will automatically synchronize the gird disks to bring them back into the disk group.
-- If ASM is down, need to bring up ASM on atleast one node to see the celldisk "ONLINE", if not celldisk will NOT be in ONLINE until the ASM is up!!




--Validation steps
11.
-- Verify all grid disks have been successfully put online using the following command:
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus

Wait until asmmodestatus is ONLINE for all grid disks.

Output:
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus
DATA_CD_00_atl02cel06 ONLINE
DATA_CD_01_atl02cel06 ONLINE
DATA_CD_02_atl02cel06 ONLINE
DATA_CD_03_atl02cel06 ONLINE
DATA_CD_04_atl02cel06 ONLINE
DATA_CD_05_atl02cel06 ONLINE
DATA_CD_06_atl02cel06 ONLINE
DATA_CD_07_atl02cel06 ONLINE
DATA_CD_08_atl02cel06 ONLINE
DATA_CD_09_atl02cel06 ONLINE
DATA_CD_10_atl02cel06 ONLINE
DATA_CD_11_atl02cel06 ONLINE
RECO_CD_00_atl02cel06 ONLINE
RECO_CD_01_atl02cel06 ONLINE
RECO_CD_02_atl02cel06 ONLINE
RECO_CD_03_atl02cel06 ONLINE
RECO_CD_04_atl02cel06 ONLINE
RECO_CD_05_atl02cel06 ONLINE
RECO_CD_06_atl02cel06 ONLINE
RECO_CD_07_atl02cel06 ONLINE
RECO_CD_08_atl02cel06 ONLINE
RECO_CD_09_atl02cel06 ONLINE
RECO_CD_10_atl02cel06 ONLINE
RECO_CD_11_atl02cel06 ONLINE
SYSTEMDG_CD_02_atl02cel06 ONLINE
SYSTEMDG_CD_03_atl02cel06 ONLINE
SYSTEMDG_CD_04_atl02cel06 ONLINE
SYSTEMDG_CD_05_atl02cel06 ONLINE
SYSTEMDG_CD_06_atl02cel06 ONLINE
SYSTEMDG_CD_07_atl02cel06 ONLINE
SYSTEMDG_CD_08_atl02cel06 ONLINE
SYSTEMDG_CD_09_atl02cel06 ONLINE
SYSTEMDG_CD_10_atl02cel06 ONLINE
SYSTEMDG_CD_11_atl02cel06 ONLINE


12.
-- Oracle ASM synchronization is only complete when all grid disks show asmmodestatus=ONLINE.
-- Before taking another storage server offline, Oracle ASM synchronization must complete on the restarted Oracle Exadata Storage Server.
-- If synchronization is not complete, then the check performed on another storage server will fail.

CellCLI> list griddisk attributes name where asmdeactivationoutcome != 'Yes'


13.
-- Wait until asmmodestatus shows ONLINE for all grid disks.
-- List the disks and confirm they are all normal.

CellCLI> LIST GRIDDISK ATTRIBUTES asmmodestatus
CellCLI> list physicaldisk
CellCLI> list celldisk
CellCLI> list griddisk

-- All disks are visable after re-creating the flashcache/ compare the output from Step 2
CellCLI> list physicaldisk
16:0 JK1130YAHARN4T normal
16:1 JK1130YAHBWWZT normal
16:2 JK1130YAHBV76T normal
16:3 JK1130YAHBV7TT normal
16:4 JK1130YAH49UJT normal
16:5 JK1130YAHBJ5HT normal
16:6 JK1130YAHBJNHT normal
16:7 JK1130YAHATA3T normal
16:8 JK1130YAHAJX7T normal
16:9 JK1130YAHBJNJT normal
16:10 JK1130YAHBSBZT normal
16:11 JK1130YAHBV7PT normal
[1:0:0:0] 5080020000c47c0FMOD0 normal
[1:0:1:0] 5080020000c47c0FMOD1 normal
[1:0:2:0] 5080020000c47c0FMOD2 normal
[1:0:3:0] 5080020000c47c0FMOD3 normal
[2:0:0:0] 5080020000c47eaFMOD0 normal
[2:0:1:0] 5080020000c47eaFMOD1 normal
[2:0:2:0] 5080020000c47eaFMOD2 normal
[2:0:3:0] 5080020000c47eaFMOD3 normal
[3:0:0:0] 5080020000c47feFMOD0 normal
[3:0:1:0] 5080020000c47feFMOD1 normal
[3:0:2:0] 5080020000c47feFMOD2 normal
[3:0:3:0] 5080020000c47feFMOD3 normal
[4:0:0:0] 5080020000c47b2FMOD0 normal
[4:0:1:0] 5080020000c47b2FMOD1 normal
[4:0:2:0] 5080020000c47b2FMOD2 normal
[4:0:3:0] 5080020000c47b2FMOD3 normal

--
CellCLI> list celldisk
CD_00_atl02cel06 normal
CD_01_atl02cel06 normal
CD_02_atl02cel06 normal
CD_03_atl02cel06 normal
CD_04_atl02cel06 normal
CD_05_atl02cel06 normal
CD_06_atl02cel06 normal
CD_07_atl02cel06 normal
CD_08_atl02cel06 normal
CD_09_atl02cel06 normal
CD_10_atl02cel06 normal
CD_11_atl02cel06 normal
FD_00_atl02cel06 normal
FD_01_atl02cel06 normal
FD_02_atl02cel06 normal
FD_03_atl02cel06 normal
FD_04_atl02cel06 normal
FD_05_atl02cel06 normal
FD_06_atl02cel06 normal
FD_07_atl02cel06 normal
FD_08_atl02cel06 normal
FD_09_atl02cel06 normal
FD_10_atl02cel06 normal
FD_11_atl02cel06 normal
FD_12_atl02cel06 normal
FD_13_atl02cel06 normal
FD_14_atl02cel06 normal
FD_15_atl02cel06 normal

--
CellCLI> list griddisk
DATA_CD_00_atl02cel06 active
DATA_CD_01_atl02cel06 active
DATA_CD_02_atl02cel06 active
DATA_CD_03_atl02cel06 active
DATA_CD_04_atl02cel06 active
DATA_CD_05_atl02cel06 active
DATA_CD_06_atl02cel06 active
DATA_CD_07_atl02cel06 active
DATA_CD_08_atl02cel06 active
DATA_CD_09_atl02cel06 active
DATA_CD_10_atl02cel06 active
DATA_CD_11_atl02cel06 active
RECO_CD_00_atl02cel06 active
RECO_CD_01_atl02cel06 active
RECO_CD_02_atl02cel06 active
RECO_CD_03_atl02cel06 active
RECO_CD_04_atl02cel06 active
RECO_CD_05_atl02cel06 active
RECO_CD_06_atl02cel06 active
RECO_CD_07_atl02cel06 active
RECO_CD_08_atl02cel06 active
RECO_CD_09_atl02cel06 active
RECO_CD_10_atl02cel06 active
RECO_CD_11_atl02cel06 active
SYSTEMDG_CD_02_atl02cel06 active
SYSTEMDG_CD_03_atl02cel06 active
SYSTEMDG_CD_04_atl02cel06 active
SYSTEMDG_CD_05_atl02cel06 active
SYSTEMDG_CD_06_atl02cel06 active
SYSTEMDG_CD_07_atl02cel06 active
SYSTEMDG_CD_08_atl02cel06 active
SYSTEMDG_CD_09_atl02cel06 active
SYSTEMDG_CD_10_atl02cel06 active
SYSTEMDG_CD_11_atl02cel06 active


14.
-- Go to all the cells and run this command:
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus

--
CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus
DATA_CD_00_atl02cel06 ONLINE
DATA_CD_01_atl02cel06 ONLINE
DATA_CD_02_atl02cel06 ONLINE
DATA_CD_03_atl02cel06 ONLINE
DATA_CD_04_atl02cel06 ONLINE
DATA_CD_05_atl02cel06 ONLINE
DATA_CD_06_atl02cel06 ONLINE
DATA_CD_07_atl02cel06 ONLINE
DATA_CD_08_atl02cel06 ONLINE
DATA_CD_09_atl02cel06 ONLINE
DATA_CD_10_atl02cel06 ONLINE
DATA_CD_11_atl02cel06 ONLINE
RECO_CD_00_atl02cel06 ONLINE
RECO_CD_01_atl02cel06 ONLINE
RECO_CD_02_atl02cel06 ONLINE
RECO_CD_03_atl02cel06 ONLINE
RECO_CD_04_atl02cel06 ONLINE
RECO_CD_05_atl02cel06 ONLINE
RECO_CD_06_atl02cel06 ONLINE
RECO_CD_07_atl02cel06 ONLINE
RECO_CD_08_atl02cel06 ONLINE
RECO_CD_09_atl02cel06 ONLINE
RECO_CD_10_atl02cel06 ONLINE
RECO_CD_11_atl02cel06 ONLINE
SYSTEMDG_CD_02_atl02cel06 ONLINE
SYSTEMDG_CD_03_atl02cel06 ONLINE
SYSTEMDG_CD_04_atl02cel06 ONLINE
SYSTEMDG_CD_05_atl02cel06 ONLINE
SYSTEMDG_CD_06_atl02cel06 ONLINE
SYSTEMDG_CD_07_atl02cel06 ONLINE
SYSTEMDG_CD_08_atl02cel06 ONLINE
SYSTEMDG_CD_09_atl02cel06 ONLINE
SYSTEMDG_CD_10_atl02cel06 ONLINE
SYSTEMDG_CD_11_atl02cel06 ONLINE
Posted by at 2 comments:

Monday, January 16, 2012

I/O Resource Management(IORM) with Exadata

I/O Resource Management(IORM) with Exadata

a)
-- How to configure IORM on cell nodes.

ODS and EDW- Level 1 (40%+40%=80%)
All other(EDWSTG and any other) - Level 2 (100% - that's nothing but overall 20%)
Note: IORM have 8 levels like DBRM.


-- To create IORM, need to create the IORMPLAN on each cell node
CellCLI> alter iormplan dbplan=((name='ODS', level=1, allocation=40),(name='EDW', level=1, allocation=40),(name=other,level=2,allocation=100))
IORMPLAN successfully altered

-- How to Activate
CellCLI> alter iormplan active

-- How to Inactivate
CellCLI> alter iormplan inactive

-- Check if the IORM is Active?
CellCLI> LIST IORMPLAN
atl02cel02_IORMPLAN active

-- Check if current IORM plan in place?
CellCLI> LIST IORMPLAN detail
name: atl02cel01_IORMPLAN
catPlan:
dbPlan: name=ODS,level=1,allocation=40
name=EDW,level=1,allocation=40
name=other,level=2,allocation=100
status: active


b)
-- Monitoring IORM with cellcli command.

-- How to check Small IO load per database
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM
DB_IO_RQ_SM EDW 7,387,543 IO requests
DB_IO_RQ_SM ODS 5,262,853 IO requests
DB_IO_RQ_SM EDWSTG 11,526,325 IO requests

-- How to check Large IO load per database
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG
DB_IO_RQ_LG EDW 13,210,476 IO requests
DB_IO_RQ_LG ODS 5,865,946 IO requests
DB_IO_RQ_LG EDWSTG 4,644,494 IO requests


-- How to check Small IO load/sec. per database, last minute
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM_SEC
DB_IO_RQ_SM_SEC EDW 14.5 IO/sec
DB_IO_RQ_SM_SEC ODS 86.5 IO/sec
DB_IO_RQ_SM_SEC EDWSTG 135 IO/sec

-- How to check Large IO load/sec. per database, last minute
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG_SEC
DB_IO_RQ_LG_SEC EDW 54.4 IO/sec
DB_IO_RQ_LG_SEC ODS 47.0 IO/sec
DB_IO_RQ_LG_SEC EDWSTG 73.5 IO/sec


-- How to check Small IO Waits per database. per min.
CellCLI> LIST METRICCURRENT where name=DB_IO_WT_SM
DB_IO_WT_SM EDW 35,576,746 ms
DB_IO_WT_SM ODS 5,686,954 ms
DB_IO_WT_SM EDWSTG 7,236,957 ms

-- How to check Large IO Waits per database. per min.
CellCLI> LIST METRICCURRENT where name=DB_IO_WT_LG
DB_IO_WT_LG EDW 2,284,982,657 ms
DB_IO_WT_LG ODS 384,891,454 ms
DB_IO_WT_LG EDWSTG 394,093,606 ms
Posted by at 2 comments:
Subscribe to: Posts (Atom)