November 15, 2009

PL/SQL – Tutorial – Basic 1

Hi All,

This is neto from Brazil

How are you?

PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the procedural features of programming languages.

A PL/SQL Block consists of three sections:

  • The Declaration section (optional).
  • The Execution section (mandatory).
  • The Exception (or Error) Handling section (optional).

 

The Syntax for a PL/SQL Block

DECLARE
 variable_declarations
BEGIN
 program_code
EXCEPTION
 exception_handlers
END;

First example:

declare
   x varchar2(30);


begin


  x:='NetApp – I love this company!';


  dbms_output.put_line('neto');


  dbms_output.put_line(x);


end;


/


neto

NetApp – I love this company!

PL/SQL procedure successfully completed.

All the best

neto

NetApp – I love this company!

Posted at 08:42 AM | | Comments (0)

Useful login.sql

Hi All,

This is neto from Brazil

How are you?

Useful login.sql

--
-- Copyright (c) 1988, 2004, Oracle Corporation.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command
--
-- USAGE
--   This script is automatically run
--

-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR    FORMAT A65  WORD_WRAPPED

-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24
COLUMN units_col_plus_show_sga FORMAT a15
-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SHOW RECYCLEBIN
COLUMN origname_plus_show_recyc   FORMAT a16 HEADING 'ORIGINAL NAME'
COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
COLUMN objtype_plus_show_recyc    FORMAT a12 HEADING 'OBJECT TYPE'
COLUMN droptime_plus_show_recyc   FORMAT a19 HEADING 'DROP TIME'

-- Defaults for SET AUTOTRACE EXPLAIN report
-- These column definitions are only used when SQL*Plus
-- is connected to Oracle 9.2 or earlier.
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

-- Default for XQUERY
COLUMN result_plus_xquery HEADING 'Result Sequence'
--
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL> "
DEFINE _EDITOR=vi

[oracle@atl46003][prod][~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Nov 15 11:15:41 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@prod SQL>

All the best

neto

NetApp – I love this company!

Posted at 08:15 AM | | Comments (0)

October 27, 2009

Procedure to collect manual AWR – Oracle 10g

Hi All,

This is neto from Brazil

How are you?

Checking AWR default interval:

[oracle@atl46004][eng][~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 26 12:03:41 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> column SNAP_INTERVAL format a20;
SQL> column RETENTION format a20;
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
  40136498 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Checking snapshots

[oracle@atl46004][eng][~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 26 12:14:14 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> column BEGIN_INTERVAL_TIME format a25;
SQL> column END_INTERVAL_TIME format a25;
SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       343 18-OCT-09 07.00.56.800 PM 18-OCT-09 08.00.58.834 PM
       344 18-OCT-09 08.00.58.834 PM 18-OCT-09 09.01.00.866 PM
       345 18-OCT-09 09.01.00.866 PM 18-OCT-09 10.00.02.879 PM
       346 18-OCT-09 10.00.02.879 PM 18-OCT-09 11.00.05.065 PM
       347 18-OCT-09 11.00.05.065 PM 19-OCT-09 12.00.07.079 AM
       348 19-OCT-09 12.00.07.079 AM 19-OCT-09 01.00.09.113 AM
       349 19-OCT-09 01.00.09.113 AM 19-OCT-09 02.00.11.152 AM
       350 19-OCT-09 02.00.11.152 AM 19-OCT-09 03.00.13.182 AM
       351 19-OCT-09 03.00.13.182 AM 19-OCT-09 04.00.15.332 AM
       352 19-OCT-09 04.00.15.332 AM 19-OCT-09 05.00.17.365 AM
       353 19-OCT-09 05.00.17.365 AM 19-OCT-09 06.00.19.401 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       354 19-OCT-09 06.00.19.401 AM 19-OCT-09 07.00.21.547 AM
       355 19-OCT-09 07.00.21.547 AM 19-OCT-09 08.00.23.560 AM
       356 19-OCT-09 08.00.23.560 AM 19-OCT-09 09.00.25.590 AM
       357 19-OCT-09 09.00.25.590 AM 19-OCT-09 10.00.28.084 AM
       358 19-OCT-09 10.00.28.084 AM 19-OCT-09 11.00.30.228 AM
       359 19-OCT-09 11.00.30.228 AM 19-OCT-09 12.00.32.243 PM
       360 19-OCT-09 12.00.32.243 PM 19-OCT-09 01.00.34.257 PM
       361 19-OCT-09 01.00.34.257 PM 19-OCT-09 02.00.36.291 PM
       362 19-OCT-09 02.00.36.291 PM 19-OCT-09 03.00.38.298 PM
       363 19-OCT-09 03.00.38.298 PM 19-OCT-09 04.00.40.320 PM
       364 19-OCT-09 04.00.40.320 PM 19-OCT-09 05.00.42.352 PM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       365 19-OCT-09 05.00.42.352 PM 19-OCT-09 06.00.44.372 PM
       366 19-OCT-09 06.00.44.372 PM 19-OCT-09 07.00.46.503 PM
       367 19-OCT-09 07.00.46.503 PM 19-OCT-09 08.00.48.501 PM
       368 19-OCT-09 08.00.48.501 PM 19-OCT-09 09.00.50.514 PM
       369 19-OCT-09 09.00.50.514 PM 19-OCT-09 10.00.52.540 PM
       370 19-OCT-09 10.00.52.540 PM 19-OCT-09 11.00.54.685 PM
       371 19-OCT-09 11.00.54.685 PM 20-OCT-09 12.00.56.805 AM
       372 20-OCT-09 12.00.56.805 AM 20-OCT-09 01.00.58.833 AM
       373 20-OCT-09 01.00.58.833 AM 20-OCT-09 02.01.00.862 AM
       374 20-OCT-09 02.01.00.862 AM 20-OCT-09 03.00.02.866 AM
       375 20-OCT-09 03.00.02.866 AM 20-OCT-09 04.00.04.893 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       376 20-OCT-09 04.00.04.893 AM 20-OCT-09 05.00.06.949 AM
       377 20-OCT-09 05.00.06.949 AM 20-OCT-09 06.00.08.990 AM
       378 20-OCT-09 06.00.08.990 AM 20-OCT-09 07.00.11.108 AM
       379 20-OCT-09 07.00.11.108 AM 20-OCT-09 08.00.13.152 AM
       380 20-OCT-09 08.00.13.152 AM 20-OCT-09 09.00.15.154 AM
       381 20-OCT-09 09.00.15.154 AM 20-OCT-09 10.00.17.156 AM
       382 20-OCT-09 10.00.17.156 AM 20-OCT-09 11.00.19.178 AM
       383 20-OCT-09 11.00.19.178 AM 20-OCT-09 12.00.21.175 PM
       384 20-OCT-09 12.00.21.175 PM 20-OCT-09 01.00.23.191 PM
       385 20-OCT-09 01.00.23.191 PM 20-OCT-09 02.00.25.219 PM
       386 20-OCT-09 02.00.25.219 PM 20-OCT-09 03.00.27.234 PM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       387 20-OCT-09 03.00.27.234 PM 20-OCT-09 04.00.29.280 PM
       388 20-OCT-09 04.00.29.280 PM 20-OCT-09 05.00.31.304 PM
       389 20-OCT-09 05.00.31.304 PM 20-OCT-09 06.00.33.346 PM
       390 20-OCT-09 06.00.33.346 PM 20-OCT-09 07.00.35.479 PM
       391 20-OCT-09 07.00.35.479 PM 20-OCT-09 08.00.37.504 PM
       392 20-OCT-09 08.00.37.504 PM 20-OCT-09 09.00.39.525 PM
       393 20-OCT-09 09.00.39.525 PM 20-OCT-09 10.00.41.523 PM
       394 20-OCT-09 10.00.41.523 PM 20-OCT-09 11.00.43.637 PM
       395 20-OCT-09 11.00.43.637 PM 21-OCT-09 12.00.45.650 AM
       396 21-OCT-09 12.00.45.650 AM 21-OCT-09 01.00.47.663 AM
       397 21-OCT-09 01.00.47.663 AM 21-OCT-09 02.00.49.651 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       398 21-OCT-09 02.00.49.651 AM 21-OCT-09 03.00.51.629 AM
       399 21-OCT-09 03.00.51.629 AM 21-OCT-09 04.00.53.658 AM
       400 21-OCT-09 04.00.53.658 AM 21-OCT-09 05.00.55.650 AM
       401 21-OCT-09 05.00.55.650 AM 21-OCT-09 06.00.57.647 AM
       402 21-OCT-09 06.00.57.647 AM 21-OCT-09 07.00.59.755 AM
       403 21-OCT-09 07.00.59.755 AM 21-OCT-09 08.01.01.750 AM
       404 21-OCT-09 08.01.01.750 AM 21-OCT-09 09.00.03.724 AM
       405 21-OCT-09 09.00.03.724 AM 21-OCT-09 10.00.05.718 AM
       406 21-OCT-09 10.00.05.718 AM 21-OCT-09 11.00.07.755 AM
       407 21-OCT-09 11.00.07.755 AM 21-OCT-09 12.00.09.768 PM
       408 21-OCT-09 12.00.09.768 PM 21-OCT-09 01.00.11.769 PM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       409 21-OCT-09 01.00.11.769 PM 21-OCT-09 02.00.13.854 PM
       410 21-OCT-09 02.00.13.854 PM 21-OCT-09 03.00.15.973 PM
       411 21-OCT-09 03.00.15.973 PM 21-OCT-09 04.00.18.013 PM
       412 21-OCT-09 04.00.18.013 PM 21-OCT-09 05.00.20.076 PM
       413 21-OCT-09 05.00.20.076 PM 21-OCT-09 06.00.22.145 PM
       414 21-OCT-09 06.00.22.145 PM 21-OCT-09 07.00.24.315 PM
       415 21-OCT-09 07.00.24.315 PM 21-OCT-09 08.00.26.388 PM
       416 21-OCT-09 08.00.26.388 PM 21-OCT-09 09.00.28.468 PM
       417 21-OCT-09 09.00.28.468 PM 21-OCT-09 10.00.30.506 PM
       418 21-OCT-09 10.00.30.506 PM 21-OCT-09 11.00.32.750 PM
       419 21-OCT-09 11.00.32.750 PM 22-OCT-09 12.00.34.810 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       420 22-OCT-09 12.00.34.810 AM 22-OCT-09 01.00.36.864 AM
       421 22-OCT-09 01.00.36.864 AM 22-OCT-09 02.00.38.906 AM
       422 22-OCT-09 02.00.38.906 AM 22-OCT-09 03.00.40.961 AM
       423 22-OCT-09 03.00.40.961 AM 22-OCT-09 04.00.43.008 AM
       424 22-OCT-09 04.00.43.008 AM 22-OCT-09 05.00.45.065 AM
       425 22-OCT-09 05.00.45.065 AM 22-OCT-09 06.00.47.122 AM
       426 22-OCT-09 06.00.47.122 AM 22-OCT-09 07.00.49.295 AM
       427 22-OCT-09 07.00.49.295 AM 22-OCT-09 08.00.51.354 AM
       428 22-OCT-09 08.00.51.354 AM 22-OCT-09 09.00.53.399 AM
       429 22-OCT-09 09.00.53.399 AM 22-OCT-09 10.00.55.450 AM
       430 22-OCT-09 10.00.55.450 AM 22-OCT-09 11.00.57.520 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       431 22-OCT-09 11.00.57.520 AM 22-OCT-09 12.00.59.605 PM
       432 22-OCT-09 12.00.59.605 PM 22-OCT-09 01.01.01.656 PM
       433 22-OCT-09 01.01.01.656 PM 22-OCT-09 02.00.03.691 PM
       434 22-OCT-09 02.00.03.691 PM 22-OCT-09 03.00.05.751 PM
       435 22-OCT-09 03.00.05.751 PM 22-OCT-09 04.00.07.820 PM
       436 22-OCT-09 04.00.07.820 PM 22-OCT-09 05.00.09.871 PM
       437 22-OCT-09 05.00.09.871 PM 22-OCT-09 06.00.11.919 PM
       438 22-OCT-09 06.00.11.919 PM 22-OCT-09 07.00.14.133 PM
       439 22-OCT-09 07.00.14.133 PM 22-OCT-09 08.00.16.290 PM
       440 22-OCT-09 08.00.16.290 PM 22-OCT-09 09.00.18.313 PM
       441 22-OCT-09 09.00.18.313 PM 22-OCT-09 10.00.20.355 PM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       442 22-OCT-09 10.00.20.355 PM 22-OCT-09 11.00.22.384 PM
       443 22-OCT-09 11.00.22.384 PM 23-OCT-09 12.00.24.405 AM
       444 23-OCT-09 12.00.24.405 AM 23-OCT-09 01.00.26.430 AM
       445 23-OCT-09 01.00.26.430 AM 23-OCT-09 02.00.28.412 AM
       446 23-OCT-09 02.00.28.412 AM 23-OCT-09 03.00.30.454 AM
       447 23-OCT-09 03.00.30.454 AM 23-OCT-09 04.00.32.480 AM
       448 23-OCT-09 04.00.32.480 AM 23-OCT-09 05.00.34.523 AM
       449 23-OCT-09 05.00.34.523 AM 23-OCT-09 06.00.36.545 AM
       450 23-OCT-09 06.00.36.545 AM 23-OCT-09 07.00.38.683 AM
       451 23-OCT-09 07.00.38.683 AM 23-OCT-09 08.00.40.737 AM
       452 23-OCT-09 08.00.40.737 AM 23-OCT-09 09.00.42.773 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       453 23-OCT-09 09.00.42.773 AM 23-OCT-09 10.00.44.821 AM
       454 23-OCT-09 10.00.44.821 AM 23-OCT-09 11.00.46.885 AM
       455 23-OCT-09 11.00.46.885 AM 23-OCT-09 12.00.48.927 PM
       456 23-OCT-09 12.00.48.927 PM 23-OCT-09 01.00.50.979 PM
       457 23-OCT-09 01.00.50.979 PM 23-OCT-09 02.00.53.031 PM
       458 23-OCT-09 02.00.53.031 PM 23-OCT-09 03.00.55.100 PM
       459 23-OCT-09 03.00.55.100 PM 23-OCT-09 04.00.57.154 PM
       460 23-OCT-09 04.00.57.154 PM 23-OCT-09 05.00.59.201 PM
       461 23-OCT-09 05.00.59.201 PM 23-OCT-09 06.01.01.262 PM
       462 23-OCT-09 06.01.01.262 PM 23-OCT-09 07.00.03.378 PM
       463 23-OCT-09 07.00.03.378 PM 23-OCT-09 08.00.05.582 PM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       464 23-OCT-09 08.00.05.582 PM 23-OCT-09 09.00.07.616 PM
       465 23-OCT-09 09.00.07.616 PM 23-OCT-09 10.00.09.651 PM
       466 23-OCT-09 10.00.09.651 PM 23-OCT-09 11.00.11.719 PM
       467 23-OCT-09 11.00.11.719 PM 24-OCT-09 12.00.13.800 AM
       468 24-OCT-09 12.00.13.800 AM 24-OCT-09 01.00.15.871 AM
       469 24-OCT-09 01.00.15.871 AM 24-OCT-09 02.00.17.931 AM
       470 24-OCT-09 02.00.17.931 AM 24-OCT-09 03.00.19.984 AM
       471 24-OCT-09 03.00.19.984 AM 24-OCT-09 04.00.22.046 AM
       472 24-OCT-09 04.00.22.046 AM 24-OCT-09 05.00.24.102 AM
       473 24-OCT-09 05.00.24.102 AM 24-OCT-09 06.00.26.130 AM
       474 24-OCT-09 06.00.26.130 AM 24-OCT-09 07.00.28.314 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       475 24-OCT-09 07.00.28.314 AM 24-OCT-09 08.00.30.408 AM
       476 24-OCT-09 08.00.30.408 AM 24-OCT-09 09.00.32.457 AM
       477 24-OCT-09 09.00.32.457 AM 24-OCT-09 10.00.34.502 AM
       478 24-OCT-09 10.00.34.502 AM 24-OCT-09 11.00.36.556 AM
       479 24-OCT-09 11.00.36.556 AM 24-OCT-09 12.00.38.736 PM
       480 24-OCT-09 12.00.38.736 PM 24-OCT-09 01.00.40.791 PM
       481 24-OCT-09 01.00.40.791 PM 24-OCT-09 02.00.42.854 PM
       482 24-OCT-09 02.00.42.854 PM 24-OCT-09 03.00.44.942 PM
       483 24-OCT-09 03.00.44.942 PM 24-OCT-09 04.00.46.977 PM
       484 24-OCT-09 04.00.46.977 PM 24-OCT-09 05.00.49.019 PM
       485 24-OCT-09 05.00.49.019 PM 24-OCT-09 06.00.51.085 PM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       486 24-OCT-09 06.00.51.085 PM 24-OCT-09 07.00.53.263 PM
       487 24-OCT-09 07.00.53.263 PM 24-OCT-09 08.00.55.468 PM
       488 24-OCT-09 08.00.55.468 PM 24-OCT-09 09.00.57.517 PM
       489 24-OCT-09 09.00.57.517 PM 24-OCT-09 10.00.59.566 PM
       490 24-OCT-09 10.00.59.566 PM 24-OCT-09 11.00.01.570 PM
       491 24-OCT-09 11.00.01.570 PM 25-OCT-09 12.00.03.611 AM
       492 25-OCT-09 12.00.03.611 AM 25-OCT-09 01.00.05.651 AM
       493 25-OCT-09 01.00.05.651 AM 25-OCT-09 02.00.07.705 AM
       494 25-OCT-09 02.00.07.705 AM 25-OCT-09 03.00.09.763 AM
       495 25-OCT-09 03.00.09.763 AM 25-OCT-09 04.00.11.835 AM
       496 25-OCT-09 04.00.11.835 AM 25-OCT-09 05.00.13.883 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       497 25-OCT-09 05.00.13.883 AM 25-OCT-09 06.00.15.937 AM
       498 25-OCT-09 06.00.15.937 AM 25-OCT-09 07.00.18.093 AM
       499 25-OCT-09 07.00.18.093 AM 25-OCT-09 08.00.20.144 AM
       500 25-OCT-09 08.00.20.144 AM 25-OCT-09 09.00.22.220 AM
       501 25-OCT-09 09.00.22.220 AM 25-OCT-09 10.00.24.286 AM
       502 25-OCT-09 10.00.24.286 AM 25-OCT-09 11.00.26.344 AM
       503 25-OCT-09 11.00.26.344 AM 25-OCT-09 12.00.28.402 PM
       504 25-OCT-09 12.00.28.402 PM 25-OCT-09 01.00.30.454 PM
       505 25-OCT-09 01.00.30.454 PM 25-OCT-09 02.00.32.504 PM
       506 25-OCT-09 02.00.32.504 PM 25-OCT-09 03.00.34.574 PM
       507 25-OCT-09 03.00.34.574 PM 25-OCT-09 04.00.36.642 PM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       508 25-OCT-09 04.00.36.642 PM 25-OCT-09 05.00.38.692 PM
       509 25-OCT-09 05.00.38.692 PM 25-OCT-09 06.00.40.758 PM
       510 25-OCT-09 06.00.40.758 PM 25-OCT-09 07.00.42.944 PM
       511 25-OCT-09 07.00.42.944 PM 25-OCT-09 08.00.45.224 PM
       512 25-OCT-09 08.00.45.224 PM 25-OCT-09 09.00.47.285 PM
       513 25-OCT-09 09.00.47.285 PM 25-OCT-09 10.00.49.323 PM
       514 25-OCT-09 10.00.49.323 PM 25-OCT-09 11.00.51.354 PM
       515 25-OCT-09 11.00.51.354 PM 26-OCT-09 12.00.53.376 AM
       516 26-OCT-09 12.00.53.376 AM 26-OCT-09 01.00.55.437 AM
       517 26-OCT-09 01.00.55.437 AM 26-OCT-09 02.00.57.450 AM
       518 26-OCT-09 02.00.57.450 AM 26-OCT-09 03.00.59.481 AM

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       519 26-OCT-09 03.00.59.481 AM 26-OCT-09 04.00.01.601 AM
       520 26-OCT-09 04.00.01.601 AM 26-OCT-09 05.00.03.633 AM
       521 26-OCT-09 05.00.03.633 AM 26-OCT-09 06.00.05.652 AM
       522 26-OCT-09 06.00.05.652 AM 26-OCT-09 07.00.07.815 AM
       523 26-OCT-09 07.00.07.815 AM 26-OCT-09 08.00.09.860 AM
       524 26-OCT-09 08.00.09.860 AM 26-OCT-09 09.00.11.882 AM
       525 26-OCT-09 09.00.11.882 AM 26-OCT-09 10.00.13.903 AM
       526 26-OCT-09 10.00.13.903 AM 26-OCT-09 11.00.15.946 AM
       527 26-OCT-09 11.00.15.946 AM 26-OCT-09 12.00.17.975 PM

185 rows selected.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Collecting manual snapshots (First Snapshot)

[oracle@atl46004][eng][~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 26 12:10:15 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Collecting manual snapshots (Second Snapshot) – after 15 minutes – PEAK Throughput

[oracle@atl46004][eng][~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 26 12:25:00 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
   40136498 ENG                 1 eng

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 40136498          1 ENG          eng          atl46004.atl
                                                dc.com
  40136498          1 ENG          clone        atl46004.atl
                                                dc.com

Using   40136498 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days:

Listing all Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
eng          ENG                343 18 Oct 2009 20:00      1
                                344 18 Oct 2009 21:01      1
                                345 18 Oct 2009 22:00      1
                                346 18 Oct 2009 23:00      1
                                347 19 Oct 2009 00:00      1
                                348 19 Oct 2009 01:00      1
                                349 19 Oct 2009 02:00      1
                                350 19 Oct 2009 03:00      1
                                351 19 Oct 2009 04:00      1
                                352 19 Oct 2009 05:00      1
                                353 19 Oct 2009 06:00      1
                                354 19 Oct 2009 07:00      1
                                355 19 Oct 2009 08:00      1
                                356 19 Oct 2009 09:00      1
                                357 19 Oct 2009 10:00      1
                                358 19 Oct 2009 11:00      1
                                359 19 Oct 2009 12:00      1
                                360 19 Oct 2009 13:00      1
                                361 19 Oct 2009 14:00      1
                                362 19 Oct 2009 15:00      1
                                363 19 Oct 2009 16:00      1
                                364 19 Oct 2009 17:00      1
                                365 19 Oct 2009 18:00      1
                                366 19 Oct 2009 19:00      1
                                367 19 Oct 2009 20:00      1
                                368 19 Oct 2009 21:00      1
                                369 19 Oct 2009 22:00      1
                                370 19 Oct 2009 23:00      1
                                371 20 Oct 2009 00:00      1
                                372 20 Oct 2009 01:00      1
                                373 20 Oct 2009 02:01      1
                                374 20 Oct 2009 03:00      1
                                375 20 Oct 2009 04:00      1
                                376 20 Oct 2009 05:00      1
                                377 20 Oct 2009 06:00      1
                                378 20 Oct 2009 07:00      1
                                379 20 Oct 2009 08:00      1
                                380 20 Oct 2009 09:00      1
                                381 20 Oct 2009 10:00      1
                                382 20 Oct 2009 11:00      1
                                383 20 Oct 2009 12:00      1
                                384 20 Oct 2009 13:00      1
                                385 20 Oct 2009 14:00      1
                                386 20 Oct 2009 15:00      1
                                387 20 Oct 2009 16:00      1
                                388 20 Oct 2009 17:00      1
                                389 20 Oct 2009 18:00      1
                                390 20 Oct 2009 19:00      1
                                391 20 Oct 2009 20:00      1
                                392 20 Oct 2009 21:00      1
                                393 20 Oct 2009 22:00      1
                                394 20 Oct 2009 23:00      1
                                395 21 Oct 2009 00:00      1
                                396 21 Oct 2009 01:00      1
                                397 21 Oct 2009 02:00      1
                                398 21 Oct 2009 03:00      1

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
eng          ENG                399 21 Oct 2009 04:00      1
                                400 21 Oct 2009 05:00      1
                                401 21 Oct 2009 06:00      1
                                402 21 Oct 2009 07:00      1
                                403 21 Oct 2009 08:01      1
                                404 21 Oct 2009 09:00      1
                                405 21 Oct 2009 10:00      1
                                406 21 Oct 2009 11:00      1
                                407 21 Oct 2009 12:00      1
                                408 21 Oct 2009 13:00      1
                                409 21 Oct 2009 14:00      1
                                410 21 Oct 2009 15:00      1
                                411 21 Oct 2009 16:00      1
                                412 21 Oct 2009 17:00      1
                                413 21 Oct 2009 18:00      1
                                414 21 Oct 2009 19:00      1
                                415 21 Oct 2009 20:00      1
                                416 21 Oct 2009 21:00      1
                                417 21 Oct 2009 22:00      1
                                418 21 Oct 2009 23:00      1
                                419 22 Oct 2009 00:00      1
                                420 22 Oct 2009 01:00      1
                                421 22 Oct 2009 02:00      1
                                422 22 Oct 2009 03:00      1
                                423 22 Oct 2009 04:00      1
                                424 22 Oct 2009 05:00      1
                                425 22 Oct 2009 06:00      1
                                426 22 Oct 2009 07:00      1
                                427 22 Oct 2009 08:00      1
                                428 22 Oct 2009 09:00      1
                                429 22 Oct 2009 10:00      1
                                430 22 Oct 2009 11:00      1
                                431 22 Oct 2009 12:00      1
                                432 22 Oct 2009 13:01      1
                                433 22 Oct 2009 14:00      1
                                434 22 Oct 2009 15:00      1
                                435 22 Oct 2009 16:00      1
                                436 22 Oct 2009 17:00      1
                                437 22 Oct 2009 18:00      1
                                438 22 Oct 2009 19:00      1
                                439 22 Oct 2009 20:00      1
                                440 22 Oct 2009 21:00      1
                                441 22 Oct 2009 22:00      1
                                442 22 Oct 2009 23:00      1
                                443 23 Oct 2009 00:00      1
                                444 23 Oct 2009 01:00      1
                                445 23 Oct 2009 02:00      1
                                446 23 Oct 2009 03:00      1
                                447 23 Oct 2009 04:00      1
                                448 23 Oct 2009 05:00      1
                                449 23 Oct 2009 06:00      1
                                450 23 Oct 2009 07:00      1
                                451 23 Oct 2009 08:00      1
                                452 23 Oct 2009 09:00      1
                                453 23 Oct 2009 10:00      1
                                454 23 Oct 2009 11:00      1

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
eng          ENG                455 23 Oct 2009 12:00      1
                                456 23 Oct 2009 13:00      1
                                457 23 Oct 2009 14:00      1
                                458 23 Oct 2009 15:00      1
                                459 23 Oct 2009 16:00      1
                                460 23 Oct 2009 17:00      1
                                461 23 Oct 2009 18:01      1
                                462 23 Oct 2009 19:00      1
                                463 23 Oct 2009 20:00      1
                                464 23 Oct 2009 21:00      1
                                465 23 Oct 2009 22:00      1
                                466 23 Oct 2009 23:00      1
                                467 24 Oct 2009 00:00      1
                                468 24 Oct 2009 01:00      1
                                469 24 Oct 2009 02:00      1
                                470 24 Oct 2009 03:00      1
                                471 24 Oct 2009 04:00      1
                                472 24 Oct 2009 05:00      1
                                473 24 Oct 2009 06:00      1
                                474 24 Oct 2009 07:00      1
                                475 24 Oct 2009 08:00      1
                                476 24 Oct 2009 09:00      1
                                477 24 Oct 2009 10:00      1
                                478 24 Oct 2009 11:00      1
                                479 24 Oct 2009 12:00      1
                                480 24 Oct 2009 13:00      1
                                481 24 Oct 2009 14:00      1
                                482 24 Oct 2009 15:00      1
                                483 24 Oct 2009 16:00      1
                                484 24 Oct 2009 17:00      1
                                485 24 Oct 2009 18:00      1
                                486 24 Oct 2009 19:00      1
                                487 24 Oct 2009 20:00      1
                                488 24 Oct 2009 21:00      1
                                489 24 Oct 2009 22:00      1
                                490 24 Oct 2009 23:00      1
                                491 25 Oct 2009 00:00      1
                                492 25 Oct 2009 01:00      1
                                493 25 Oct 2009 02:00      1
                                494 25 Oct 2009 03:00      1
                                495 25 Oct 2009 04:00      1
                                496 25 Oct 2009 05:00      1
                                497 25 Oct 2009 06:00      1
                                498 25 Oct 2009 07:00      1
                                499 25 Oct 2009 08:00      1
                                500 25 Oct 2009 09:00      1
                                501 25 Oct 2009 10:00      1
                                502 25 Oct 2009 11:00      1
                                503 25 Oct 2009 12:00      1
                                504 25 Oct 2009 13:00      1
                                505 25 Oct 2009 14:00      1
                                506 25 Oct 2009 15:00      1
                                507 25 Oct 2009 16:00      1
                                508 25 Oct 2009 17:00      1
                                509 25 Oct 2009 18:00      1
                                510 25 Oct 2009 19:00      1

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
eng          ENG                511 25 Oct 2009 20:00      1
                                512 25 Oct 2009 21:00      1
                                513 25 Oct 2009 22:00      1
                                514 25 Oct 2009 23:00      1
                                515 26 Oct 2009 00:00      1
                                516 26 Oct 2009 01:00      1
                                517 26 Oct 2009 02:00      1
                                518 26 Oct 2009 03:00      1
                                519 26 Oct 2009 04:00      1
                                520 26 Oct 2009 05:00      1
                                521 26 Oct 2009 06:00      1
                                522 26 Oct 2009 07:00      1
                                523 26 Oct 2009 08:00      1
                                524 26 Oct 2009 09:00      1
                                525 26 Oct 2009 10:00      1
                                526 26 Oct 2009 11:00      1
                                527 26 Oct 2009 12:00      1
                                528 26 Oct 2009 12:10      1
                                529 26 Oct 2009 12:25      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 528
Begin Snapshot Id specified: 528

Enter value for end_snap: 529
End   Snapshot Id specified: 529

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_528_529.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/sample_AWR_report.txt

Using the report name /home/oracle/sample_AWR_report.txt

Report written to /home/oracle/sample_AWR_report.txt

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Checking the report…

[oracle@atl46004][eng][~]$ head -13 /home/oracle/sample_AWR_report.txt

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ENG             40136498 eng                 1 10.2.0.4.0  NO  atl46004.atl

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       528 26-Oct-09 12:10:10        19       3.6
  End Snap:       529 26-Oct-09 12:25:20        19       3.5
   Elapsed:                15.10 (mins)
   DB Time:                33.43 (mins)

All the best,

neto

NetApp – I love this company!

Posted at 09:44 AM | | Comments (0)

October 21, 2009

Script to check ASM diskgroup size

Hi All,

This is neto from Brazil

How are you?

Here you have an useful script to check ASM diskgroup size

 

SET LINESIZE  200
SET PAGESIZE  9999
SET TRIMSPOOL on
SET VERIFY    off

COLUMN disk_group_name        FORMAT a10           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a30           HEAD 'Path'
COLUMN disk_file_name         FORMAT a10           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a10           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label ""              of total_mb used_mb on disk_group_name
compute sum label "Total: "       of total_mb used_mb on report

SELECT
  a.group_number,
  a.name,
  a.state
FROM
    v$asm_diskgroup a
ORDER BY
    a.group_number
;

SELECT
  b.group_number                                   disk_group_number,
  b.path                                           disk_file_path,
  b.name                                           disk_file_name,
  b.failgroup                                      disk_file_fail_group,
  b.total_mb                                       total_mb,
  (b.total_mb - b.free_mb)                         used_mb,
  decode(b.total_mb,0,0,(ROUND((1- (b.free_mb / b.total_mb))*100, 2)))      pct_used
FROM
    v$asm_disk b
WHERE b.header_status = 'MEMBER'
ORDER BY
  b.group_number
;

Sample Output :

image

All the best

neto

NetApp – My reason to live!

Posted at 08:21 AM | | Comments (0)

October 01, 2009

Oracle Open World 2009 – Going to 11: Managing Upgrades and Securing Data with Minimal Risk

Hi All,

This is neto from Brazil

How are you?

Please visit NetApp at Oracle Open World – NetApp booth number 901

Please we will be presenting a session about:

Going to 11: Managing Upgrades and Securing Data with Minimal Risk

Presenters: Peter Schay, NetApp Database Solutions Architect, Kannan Mani, NetApp Reference Architecture Specialist; Antonio Jose Rodrigues Neto, NetApp Consulting Systems Engineer

Learn how you can save time, improve storage utilization, reduce costs, and increase efficiencies when upgrading to Oracle Database 11g™. We’ll dive into all the details of how you can use proven NetApp techniques to efficiently clone and secure your existing Oracle Database and safely upgrade to 11g.  You’ll learn valuable lessons from real-world deployments by NetApp and Oracle customers.
We’ll also perform a live demo of a terabyte-sized Oracle RAC and ASM database and show you how to take advantage of NetApp storage technology and Oracle Database 11g’s Real Application Testing and Data Masking for secure, efficient, and non-disruptive upgrades.

SESSION SCHEDULE INFORMATION
ID#: S309086
Title: Going to 11: Managing Upgrades and Securing Data with Minimal Risk
Track: Database
Date: 13-OCT-09
Time: 16:00 - 17:00
Venue: Moscone South
Room: Room 307

See you at Oracle Open World 2009!

All the best

neto – Net(App)O(racle) – the perfect combination!

NetApp – I love this company!

Posted at 12:16 PM | | Comments (0)

September 24, 2009

Checking if Tablespaces are in backup mode or not…

Hi All,

This is neto from Brazil

How are you?

Checking if Tablespaces are in backup mode or not…

 

1) Tablespaces not in backup mode: NOT ACTIVE

[oracle@atl46004][eng][~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 23 09:01:19 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @backup.sql

NAME            FILE# STATUS
---------- ---------- ------------------
SYSTEM              1 NOT ACTIVE
UNDOTBS1            2 NOT ACTIVE
SYSAUX              3 NOT ACTIVE
USERS               4 NOT ACTIVE
TB1                 5 NOT ACTIVE
TB2                 6 NOT ACTIVE

6 rows selected.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2) Tablespaces in backup mode: ACTIVE

[oracle@atl46004][eng][~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 23 09:03:38 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database begin backup;

Database altered.

SQL> @backup.sql

NAME            FILE# STATUS
---------- ---------- ------------------
SYSTEM              1 ACTIVE
UNDOTBS1            2 ACTIVE
SYSAUX              3 ACTIVE
USERS               4 ACTIVE
TB1                 5 ACTIVE
TB2                 6 ACTIVE

6 rows selected.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

The backup.sql script is:

set lines 80
column name format a10

SELECT t.name, d.file# as, b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

 

After a begin backup, make sure that you will make a faster backup using NetApp Snapshot (seconds) and after … execute alter database end backup; 

All the best

neto

NetApp – I love this company!

Posted at 06:10 AM | | Comments (4)

Useful .bash_profile version 1.0

Hi All,

This is neto from Brazil

How are you?

Here you have an useful .bash_profile version 1.0 with some functions:

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

function sid() {
export ORACLE_SID=$1
}

function sql() {
export ORACLE_SID=$1
$ORACLE_HOME/bin/sqlplus "/ as sysdba"
}

function smolog() {
smocurrentlog=`ls -la /var/log/smo/smo*.log | grep -v cxf | tail -1 | awk '{ print $9 }'`; tail -f $smocurrentlog
}

function alert () {
export ORACLE_SID=$1
tail -f $ORACLE_BASE/admin/$1/bdump/alert_$1.log
}

export PATH
unset USERNAME
ORACLE_BASE=/oracle/app
#ORACLE_HOME=$ORACLE_BASE/product/11.1/db_1
ORACLE_HOME=$ORACLE_BASE/product/10.2/db_1
AGENT_HOME=$ORACLE_BASE/product/agent10g
ORACLE_SID=prod
PATH=/usr/sbin:/usr/bin:$PATH:$ORACLE_HOME/bin:$CRS_HOME/bin:$AGENT_HOME/bin:$OMS_HOME/bin:$HOME/bin:/bin:/usr/local/bin:/usr/X11R6/bin

export PATH LD_LIBRARY_PATH ORACLE_BASE ORACLE_HOME ORACLE_SID ORA_CRS_HOME
PS1='[\u@\h][${ORACLE_SID}][\w]\$ '

 

function sid – change your ORACLE_SID variable

function sql – connect on sqlplus using a parameter for ORACLE_SID

function smolog – tail –f on the latest smo log

function alert – tail –f on the database alert using a parameter for ORACLE_SID

PS1=’[\u@\h][${ORACLE_SID}][\w]\$’ – to have a prompt as:

[oracle@atl46003][prod][~]$

 

I will update the next posts with new versions. All the best

neto

NetApp – My reason to live!

Posted at 06:02 AM | | Comments (3)

August 26, 2009

Oracle - Manual Clone Procedure

Hi All

This is neto from Brazil

How are you?

Backup Controlfile to trace on source database

- ALTER DATABASE BACKUP CONTROLFILE TO TRACE as '/tmp/control.trc'

Copy the /tmp/control.trc into create_controlfile.sql removing the extra non sql pieces and duplication

Modify this create_controlfile.sql as follows

- The REUSE to SET

- The SOURCEDB to NEWDB (that is, the new name of the target database)

- The NORESETLOGS to RESETLOGS

- For each file listed under the DATAFILE clause, if you will be renaming the file as part of the cloning process, then simply update the filename to the new name in the CREATE CONTROLFILE command.

Put each tablespace or database (source) in hot backup mode

SQL>alter tablespace <name> begin backup; (for Oracle 8i and 9i)

SQL>alter database begin backup; (for Oracle 10g and later)

Take a NetApp Snapshot

Put each tablespace or database (source) out of hot backup mode

SQL>alter tablespace <name> end backup; (for Oracle 8i and 9i)

SQL>alter database end backup; (for Oracle 10g and later)

Take a NetApp FlexClone

Go the destination environment

Run the create controlfile command (create_controlfile.sql)

Issue "set autorecovery on"

Issue "recover database using backup controlfile until cancel"

Provide necessary archive log

CANCEL recovery

issue ALTER DATABASE OPEN RESETLOGS

SHUTDOWN and STARTUP database

Re-create or re-add any TEMPFILES used for temporary tablespaces on the target

Update DBID and GLOBAL_DBNAME

 

All the best

neto

NetApp - My LIFE!

Posted at 02:35 PM | | Comments (1)

Do you need more IOPS? spindles? Choose PAM II

Hi All

This is neto from Brazil

How are you?

Suppose that you have an OLTP application and you need to make a proper Storage sizing to support  a typical online transaction processing (OLTP) workload. Basically, OLTP workloads are a mix of reads and writes, random and sequential accesses. Because rotating disk drives perform poorly under random access workloads, the throughput of OLTP systems often becomes limited by the performance of even the fastest disk drives. Before the advent of inexpensive flash-based storage, the usual path to improved storage-system performance for such systems was to add more disk drives. For some systems and some circumstances, this might still be the correct choice. However... that is because I love this company!

clip_image001

 

Suppose an application needs 55k IOPs.

The customer has two alternatives to provide More IOPs:

1) Traditional method: Add Spindles

2) Innovative method: Add PAM II

Choosing the #2 alternative, the benefits are: Storage Efficient and Achieves cost savings, power, space with an improvement of response times.

Storage

To achieve 55k IOPs with 15k FC disk drives alone required 140 more disk drives added to the original 84 drives.  224 spindles total compared to 84 with PAM II.

Rack Space

Configuration with 224 x 15k FC disks uses 125% more (+30U) rack space than the PAM II configuration with FAS3160A and 84 disks.

Electricity

Configuration with 224 x 15k FC disks uses 130% more electricity than the PAM II configuration. 

Purchase Price

Adding 1TB of PAM II cache cost 55% less than adding 140 x 15k FC disks.

 

Below, here is an example about performance using more spindles and PAM:

image

Same performance compared with adding more spindles

Below, here the example about response time:

image

Better performance compared with adding more spindles.

 

This White Paper  "Using the Performance Accelerator Module II in Online Transaction Processing" is the complete reference to check about OLTP with PAM II.

 

All the best

neto

NetApp - I love this company! (with more Love using PAM)

Posted at 02:21 PM | | Comments (1)

July 27, 2009

ASM Multipathing - Linux - Double Check

Hi All

This is neto from Brazil

How are you?

Double checking ...

clip_image002

clip_image004

clip_image006

 

After create the ASM instance and Database...

Configuring Oracle ASM diskstring

clip_image002[4]

clip_image004[4]

 

Backup with SMO

clip_image002[6]

 

Net(App)O(racle) - the perfect combination!

All the best

neto

NetApp - I love this company!

Posted at 01:29 PM | | Comments (2)

The views expressed on this website/weblog are those of the author alone and are not deemed to be approved or endorsed by NetApp.
© NetApp, Inc.  |  "Safe Harbor" Statement  |  Privacy Policy