Monday, January 31, 2011

How to resize/add/drop redo log files in RAC database?

In my 2-nodes 11.2.0.2 Oracle RAC on OEL 5.5 platform, I need to resize/add/drop redo log files in order to increase redo log file size from 200M to 1G, here are the steps I took to achieve this purpose: 
1 Run commands below to check redo log info:


col member format a60
select * from v$log;
select bytes/1024/1024 from v$log;
select * from v$logfile;
SELECT a.group#, a.member, b.bytes/1024/1024 MB FROM v$logfile a, v$log b WHERE a.group# = b.group#;


2 Add new redo log groups to the database:




 alter database add logfile thread 1 group 5 ('/u09/oradata/rac/redo05.log','/u10/oradata/rac/redo05b') size 1g;
 alter database add logfile thread 1 group 6 ('/u09/oradata/rac/redo06.log','/u10/oradata/rac/redo06b') size 1g;
alter database add logfile thread 2 group 7 ('/u09/oradata/rac/redo07.log','/u10/oradata/rac/redo07b') size 1g;
 alter database add logfile thread 2 group 8 ('/u09/oradata/rac/redo08.log','/u10/oradata/rac/redo08b') size 1g;

3 Drop "Inactive" redo log groups:
select * from v$log;
alter database drop logfile group 1; (assuming group 1 is inactive)

Repeating the above command to drop all old redo log files with small file size. 

4 If the targeted redo log groups to be dropped are not inactive, issue following commands to change its status to "Inactive":
1) Running below command in all instances!!!
alter system switch logfile;

2) alter system checkpoint global;
Note: in non-RAC database, no need to specify "global" keyword. 


Related links:




Labels: ,

posted by linyi @ 12:41 PM   0 Comments

Friday, January 28, 2011

10g & 11g :Configuration of TAF(Transparent Application Failover) and Load Balancing [ID 453293.1]


Script started on Thu 01 Mar 2012 10:43:38 AM EST
[oracle@dbrac1 scripts]$ srvctl add service -d dbrac -s dbrac_taf -r "dbrac1,dbrac2" -P BASIC
[oracle@dbrac1 scripts]$ srvctl start service -d dbrac -s dbrac_taf
[oracle@dbrac1 scripts]$ srvctl config service -d dbrac
Service name: dbrac_taf
Service is enabled
Server pool: dbrac_dbrac_taf
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: dbrac1,dbrac2
Available instances:
[oracle@dbrac1 scripts]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 1 10:45:20 2012


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> select name, service_id from dba_services where name='dbrac_taf';


NAME                                                             SERVICE_ID
---------------------------------------------------------------- ----------
dbrac_taf                                                                 3


SQL> col name format a15
col failover_method format a11 heading 'METHOD'
col failover_type format a10 heading 'TYPE'
col failover_retries format 9999999 heading 'RETRIES'
col goal format a10
col clb_goal format a8
col AQ_HA_NOTIFICATIONS format a5 headiSQL> SQL> SQL> SQL> SQL> SQL> ng 'AQNOT'
SQL>
SQL>
SQL>
SQL> select name,failover_method, failover_type, failover_retries,goal, clb_goal, aq_ha_notifications from dba_services where service_id=3;


NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
dbrac_taf       NONE        NONE              0 NONE       LONG     NO


Note: no need to do following step in SQL, in 11gR2, it can be done by using "srvctl" command
SQL> execute dbms_service.modify_service (service_name => 'dbrac_taf' -
> , aq_ha_notifications => true -
> , failover_method => dbms_service.failover_method_basic -
> , failover_type => dbms_service.failover_type_select -
> , failover_retries => 180 -
> , failover_delay => 5 -
> , clb_goal => dbms_service.clb_goal_long);


PL/SQL procedure successfully completed.


SQL> select name,failover_method, failover_type, failover_retries,goal, clb_goal, aq_ha_notifications from dba_services where service_id=3;


NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
dbrac_taf       BASIC       SELECT          180 NONE       LONG     YES


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options




[oracle@dbrac1 scripts]$ lsnrctl services


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-MAR-2012 10:49:38


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "dbrac" has 1 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "dbracXDB" has 1 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=dbrac1.wealthengine.com)(PORT=25774))
Service "dbrac_taf" has 1 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
[oracle@dbrac1 scripts]$ exit
Script done on Thu 01 Mar 2012 11:01:13 AM EST



Note: =============================
I don't know why when I re-queired the "dba_service" table later after a few re-start of instance 2 at node 2, it shows me following:

[oracle@dbrac1 admin]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 1 14:36:18 2012


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> col name format a15
col failover_method format a11 heading 'METHOD'
col failover_type format a10 heading 'TYPE'
col failover_retries format 9999999 heading 'RETRIES'
col goal format a10
col clb_goal format a8
col AQ_HA_NOTIFICATIONS format a5 headiSQL> SQL> SQL> SQL> SQL> SQL> ng 'AQNOT'
SQL> select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications from dba_services where service_id =3;


NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
dbrac_taf       NONE        NONE              0 NONE       LONG     NO


SQL> exit


====================================================


That's why I modified the service settings with "srvctl" command rather than the pre-11gR2  method by running "execute dbms_service.modify_service ..."



[oracle@dbrac1 scripts]$ srvctl modify service -d dbrac -s dbrac_taf -r "dbrac1,dbrac2" -P basic -e select -m basic -z 10 -w 5 -B throughput
PRKO-2002 : Invalid command line option: dbrac1,dbrac2
[oracle@dbrac1 scripts]$ srvctl modify service -d dbrac -s dbrac_taf -P basic -e select -m basic -z 10 -w 5 -B throughput
[oracle@dbrac1 scripts]$
[oracle@dbrac1 scripts]$
[oracle@dbrac1 scripts]$ srvctl config service -d dbrac -s dbrac_taf
Service name: dbrac_taf
Service is enabled
Server pool: dbrac_dbrac_taf
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 10
TAF failover delay: 5
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: THROUGHPUT
TAF policy specification: BASIC
Edition:
Preferred instances: dbrac1,dbrac2
Available instances:
[oracle@dbrac1 scripts]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 1 14:52:46 2012


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> col name format a15
col failover_method format a11 heading 'METHOD'
col failover_type format a10 heading 'TYPE'
col failover_retries format 9999999 heading 'RETRIES'
col goal format a10
col clb_goal format a8
col AQ_HA_NOTIFICATIONS format a5 headiSQL> SQL> SQL> SQL> SQL> SQL> ng 'AQNOT'
SQL> select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id =  2  3
  3  /


NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
dbrac_taf       BASIC       SELECT           10 THROUGHPUT LONG     NO


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options






Oracle Metalink Notes:


Oracle Books:

  • Pro Oracle Database 11g RAC - page 572-577 (Balancing the Workload)

Labels: ,

posted by linyi @ 4:30 PM   0 Comments

What I have learned from Tom Kyte - 2 

How to calculate the actual size of a table?  
    I was looking at one of your previous listing about
    vsize and that may be one way of doing column by column
    but if there is a tool to do at once that will be a great
    help.

and we said...

see 

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079
for a discussion of the use of ANALYZE and DBMS_SPACE to this end.  Basically -- you can 
find out how many blocks are allocated to the table (whether used or NOT), how many 
blocks have never been used (subtract them from above) and on the blocks that are used -- 
the average free space.

For example, in the example from above that I linked to -- we see:

ops$tkyte@DEV8I.WORLD> create table t ( x int, 
                                 y char(2000) default '*' )
  2  storage ( initial 40k next 40k minextents 5 )
  3  tablespace system;

Table created.

A table that will create ~2k rows for each row inserted.  makes it easy to do the 
math


ops$tkyte@DEV8I.WORLD> insert into t (x) values ( 1 );
1 row created.

ops$tkyte@DEV8I.WORLD> analyze table t compute statistics;
Table analyzed.

ops$tkyte@DEV8I.WORLD> compute sum of blocks on report
ops$tkyte@DEV8I.WORLD> break on report
ops$tkyte@DEV8I.WORLD> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      81920         10
         3     122880         15
         4     163840         20
         0      40960          5
         1      40960          5
                      ----------
sum                           55

ops$tkyte@DEV8I.WORLD> clear breaks
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3   from user_tables
  4  where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
         1           53       6091                   1


Ok, the above shows us:

o we have 55 blocks allocated to the table
o 53 blocks are totally empty (above the HWM)
o 1 block contains data (the other block is used by the system)
o we have an average of about 6k free on each block used.

Therefore, our table 

o consumes 1 block
o of which  1block * 8k blocksize - 1 block * 6k free = 2k is used for our data.


Now, lets put more stuff in there...


ops$tkyte@DEV8I.WORLD> insert into t (x)
  2  select rownum
  3    from all_users
  4   where rownum < 50
  5  /
49 rows created.

ops$tkyte@DEV8I.WORLD> analyze table t compute statistics;
Table analyzed.

ops$tkyte@DEV8I.WORLD> compute sum of blocks on report
ops$tkyte@DEV8I.WORLD> break on report
ops$tkyte@DEV8I.WORLD> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      81920         10
         3     122880         15
         4     163840         20
         0      40960          5
         1      40960          5
                      ----------
sum                           55

ops$tkyte@DEV8I.WORLD> clear breaks
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3    from user_tables
  4   where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
        19           35       2810                   3

Ok, the above shows us:

o we have 55 blocks allocated to the table (still)
o 35 blocks are totally empty (above the HWM)
o 19 blocks contains data (the other block is used by the system)
o we have an average of about 2.8k free on each block used.

Therefore, our table 

o consumes 19 blocks of storage in total.
o of which  19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data. 

Given our rowsize, this is exactly what we expected.

Labels:

posted by linyi @ 3:53 PM   0 Comments

What I have learned from Tom Kyte - 1

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1150834842942
"create or replace only if the code actually changed and we need to replace it package"


What´s the meaning of the TIMESTAMP column in the DBA_OBJECTS view ? It´s confusing. The reference manual says it the time of the "specification" for the object. 
What does that mean ? Besides, which of the columns in DBA_OBJECTS reflects the time of a REPLACE action (like in CREATE OR REPLACE PROCEDURE) ?  


The timestamp is used in remote dependency checking.  It is the time of the last update 
that changed the specification of the object.  If the object is a PACKAGE -- the 
"specification" is clear.  If the object is a VIEW, the specification is perhaps not as 
clear.  The specification of a view is the number, names, and types of columns it 
returns.  Same with other objects -- their specification is their "external interface".  
Anytime their external interface has changed -- their timestamp will change.


We use this timestamp to manage dependencies between remote objects (eg: in a distributed 
system where we have local procedures that call remote procedures -- we have to 
invalidate local procedures that depend on remote procedures whose timestamps have 
changed).  


CREATED = date of creation of the object


LAST_DDL_TIME = last ddl on object, would include CREATE OR REPLACE (example below)


TIMESTAMP = last time the external "view" or "specification" of the object changed -- 
will be between created and last_ddl_time.


here is an example with a view:


tkyte@TKYTE816> create view v
  2  as
  3  select * from dual;


View created.


tkyte@TKYTE816>
tkyte@TKYTE816> select timestamp, to_char(created,'hh24:mi:ss'),
  2         to_char(last_ddl_time,'hh24:mi:ss')
  3    from dba_objects
  4   where object_name = 'V'
  5     and owner = USER
  6  /


TIMESTAMP           TO_CHAR( TO_CHAR(
------------------- -------- --------
2000-12-13:13:11:26 13:11:26 13:11:26


it starts with the timestamp = created = last_ddl_time.  Now, lets alter the view 
(doesn't change its EXTERNAL interface):


tkyte@TKYTE816>
tkyte@TKYTE816> exec dbms_lock.sleep(2)


PL/SQL procedure successfully completed.


tkyte@TKYTE816>
tkyte@TKYTE816> alter view v compile
  2  /


View altered.


tkyte@TKYTE816> select timestamp, to_char(created,'hh24:mi:ss'),
  2         to_char(last_ddl_time,'hh24:mi:ss')
  3    from dba_objects
  4   where object_name = 'V'
  5     and owner = USER
  6  /


TIMESTAMP           TO_CHAR( TO_CHAR(
------------------- -------- --------
2000-12-13:13:11:26 13:11:26 13:11:28


Now, the timestamp did not change -- it is still the same as the created.  The 
last_ddl_time did however change as we just did some DDL on the view.  We can in fact do 
some DDL on the view you might think would change the timestamp:




tkyte@TKYTE816>
tkyte@TKYTE816> exec dbms_lock.sleep(2)


PL/SQL procedure successfully completed.


tkyte@TKYTE816> create or replace view v
  2  as
  3  select * from dual;


View created.


tkyte@TKYTE816>
tkyte@TKYTE816> select timestamp, to_char(created,'hh24:mi:ss'),
  2         to_char(last_ddl_time,'hh24:mi:ss')
  3    from dba_objects
  4   where object_name = 'V'
  5     and owner = USER
  6  /


TIMESTAMP           TO_CHAR( TO_CHAR(
------------------- -------- --------
2000-12-13:13:11:26 13:11:26 13:11:30


But it does not -- the "signature" or external interface of the view did not change.  
If we do a create or replace that changes the interface of the view:


tkyte@TKYTE816>
tkyte@TKYTE816> exec dbms_lock.sleep(2)


PL/SQL procedure successfully completed.


tkyte@TKYTE816> create or replace view v
  2  as
  3  select dummy x from dual;


View created.


tkyte@TKYTE816>
tkyte@TKYTE816> select timestamp, to_char(created,'hh24:mi:ss'),
  2         to_char(last_ddl_time,'hh24:mi:ss')
  3    from dba_objects
  4   where object_name = 'V'
  5     and owner = USER
  6  /


TIMESTAMP           TO_CHAR( TO_CHAR(
------------------- -------- --------
2000-12-13:13:11:32 13:11:26 13:11:32


tkyte@TKYTE816>


And so now the timestamp is different since the "specification" of this view is 
different. 

Labels:

posted by linyi @ 3:40 PM   0 Comments

Linux tricks I learned today

I learned some Linux related tricks today:
1 Tutorial: Conditions in bash scripting (if statements)
This tutorial probably is one of the best and thorough explanation of IF conditions syntaxes I can find so far.

2 Tutorial: The best tips & tricks for bash, explained

3 Manipulating Strings
I was looking for ways to replace strings in a bash script, and found out this article is really helpful.


Substring Replacement
${string/substring/replacement}
Replace first match of $substring with $replacement.
${string//substring/replacement}
Replace all matches of $substring with $replacement. 
   1 stringZ=abcABC123ABCabc
   2 
   3 echo ${stringZ/abc/xyz}           # xyzABC123ABCabc
   4                                   # Replaces first match of 'abc' with 'xyz'.
   5 
   6 echo ${stringZ//abc/xyz}          # xyzABC123ABCxyz
   7                                   # Replaces all matches of 'abc' with # 'xyz'.
${string/#substring/replacement}
If $substring matches front end of $string, substitute $replacement for $substring.
${string/%substring/replacement}
If $substring matches back end of $string, substitute $replacement for $substring. 
   1 stringZ=abcABC123ABCabc
   2 
   3 echo ${stringZ/#abc/XYZ}          # XYZABC123ABCabc
   4                                   # Replaces front-end match of 'abc' with 'XYZ'.
   5 
   6 echo ${stringZ/%abc/XYZ}          # abcABC123ABCXYZ
   7                                   # Replaces back-end match of 'abc' with 'XYZ'.

4 Advanced Bash-Scripting Guide

5 Highlight all search pattern matches
To turn off search pattern matches highlight, do ":set hls!" in vi command mode.

6 Turn on or off color syntax highlighting in vi or vim\

7 Arithmetic Expressions in BASH
"

In bash version 3.2 and later you can (and should) use $(( )) or let for integer arithmetic expressions. The idea of ((...)) construct is similar to [[...]] construct introduced in ksh88 -- provide built in capabilities for arithmetic instead of calling external functions. The (( ))compound command evaluates an arithmetic expression and sets the exit status to 1 if the expression evaluates to 0, or to 0 if the expression evaluates to a non-zero value.  All arithmetical operations are performed  on integers. For example:
 i=$(( i + 1 ))
 let i+=1
or
 i=$(( i++))
 let i++
You must not have spaces around the equals sign, as with any bash variable assignment.
"


8 Bash For Loop Examples
   Three-expression bash for loops syntax
   Conditional exit with break

   Early continuation with continue statement

9 Best of Vim Tips

Labels: , , , ,

posted by linyi @ 2:49 PM   0 Comments

Exadata Related Oracle Notes

Oracle Sun Database Machine Setup/Configuration Best Practices [ID 1067527.1]




Exadata V2 Starter Kit [ID 1244344.1]

Contents
1.     Executive Summary. 1
1.     Exadata Specific Best Practices. 3
A.        Exadata Oracle Technology Network References  3
B.        Database Machine and Exadata Storage Server Patching. 3
C.        Performance Best Practices. 3
D.     High Availability Best Practices. 3
E.        Diagnosability and Troubleshooting Best Practices. 3
F.        Migration Best Practices. 3
G.        Manageability Best Practices. 3
H.        Security Best Practices. 3
I.        Backup Best Practices. 3
J.        Enterprise Manager Best Practices. 4
K.     Best Practices for Data Warehousing on the Oracle Database Machine. 4
L.        Exadata Setup / Configuration  4
2.     Oracle Database General Best Practices. 5
A.        General 5
B.        Machine and OS Configuration  5
C.     OS Network Configuration  5
D.        Cluster Configuration  6
E.        General Cluster Information  6
F.        Extended/Stretch Cluster 7
G.        Database Network Configuration  7
H.        Database Configuration  8
I.      Grid Infrastructure/ASM, RDBMS, and Storage Cell Patching / Maintenance Requirements  10
J.        Database Configuration Parameter File  10
3.     RAC Platform Generic Highlighted Recommendations, Best Practices, and References. 12
A.        Highlighted Recommendations. 12
B.     RAC Platform Generic Best Practices. 12
1.        Hardware  12
2.        Operating System.. 15
3.        Oracle  15
4.     IT Best Practices  20
C.        Maximum Availability Architecture  21
D.     RAC Technology References  22
1.        Oracle Product Documentation  22
2.        My Oracle Support, and Oracle Technology Network References  22
4.     RAC on Linux Highlighted Recommendations, Best Practices, and References. 25
A.     RAC on Linux Best Practices. 25
1.        Hardware  25
2.        Operating System.. 25
B.     RAC on Linux Platform Specific References  27
6.  RAC Performance Tuning Recommendations. 28
7.  RAC Related E-Business Recommendations & References. 31
A.  RAC Related E- Business Recommendations. 31
B.  RAC Related E-Business References. 31
8.  Appendix A - Storage. 32

Labels: ,

posted by linyi @ 11:38 AM   0 Comments