Friday, April 30, 2010

How To Verify Which Database Options are Used in Oracle

During installation one screen pops up saying “Warning, using these tools require additional license packs” and you have to click that you agree with the license to go on with the install. However, the functionality is installed, fully available and easily accessed. An argument could be made that only the person who actually does the install

In several cases Oracle has audited users and charged them heavy license fees for features they inadvertently (or deliberately) used such as AWR and SQL Analyzer, one thing to remember, accessing the AWR views (DBA_HIST), session history views (ASH) or running AWR reports constitutes use and requires a license.

So How you can check in the database which options or features are used. This is particular useful for licensing purposes or when you want to de-install a certain option. If you are not sure what features you or your users may have used, check out the DBA_FEATURE_USAGE_STATISTICS view





select name,version,detected_usages,currently_used,
first_usage_date,last_usage_date
from dba_feature_usage_statistics
where detected_usages >0
order by 1;
Note:also  dba_advisor_usage  record usage of  advisors

Related  Topics
How do I check How much Oracle licence needed for my database
How to find usage of Advisors in Oracle (DBA_ADVISOR_USAGE)

ORA-02273: this unique/primary key is referenced by some foreign keys

Problem occurs because you are trying to drop a primary constraints which has a dependent foreign key Example
  • Create two tables  which one  references to other
    create table departments
    (
    dept_id number,
    dept_name varchar2(100),
    constraint dept_id_pk primary key (dept_id)
    );

    create table employees
    (
    emp_id number,
    emp_name varchar2(100),
    dept_id number,
    constraint dept_id_fk foreign key(dept_id) references departments(dept_id)
    );
  • I will try to drop the primary key of departments which is referenced by employees but could not
    SQL>alter table departments drop constraint dept_id_pk;

    ERROR at line 1:
    ORA-02273: this unique/primary key is referenced by some foreign keys

  • I will find the foreign key which refer to the dept_id_pk

    SQL>select table_name,constraint_name
          from dba_constraints where r_constraint_name =upper('dept_id_pk');
    TABLE_NAME CONSTRAINT_NAME
    ------------------------------ ------------------------------
    EMPLOYEES DEPT_ID_FK
  • First I will drop the foreign key which refer to the dept_id_pk

    SQL>alter table employees drop constraint DEPT_ID_FK;
    Table altered.
  • Then I can drop the primary key of departments
    SQL>alter table departments drop constraint dept_id_pk;
    Table altered.

Wednesday, April 28, 2010

How to extract DDL for all Tablespaces

I  want  to  create  all  the  tablespaces  on development from the production so I  need  all tablespace  create script  then  I  will  modify  the  the  size  of datafiles
I need  a  script which  extract  all tablespace  create commands 



SQL>set pages 20000;
SQL>set long 100000;
SQL>set heading off;
SQL>set echo off;
SQL>spool create_ts.sql
SQL>select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

...

...
SQL>spool off

Now  I have all tablespace  create script is  create_ts.sql
Also we  can use  is  for only  one tablespace e.x TSDENEME

SQL>select dbms_metadata.get_ddl('TABLESPACE','TSDENEME') from dual;

How to enable/disable a scheduled job?

How  to check  the status  of the scheduled  jobs

SQL>select job_name,state from DBA_SCHEDULER_JOBS;
JOB_NAME STATE

------------------------------ ---------------
PURGE_LOG                                  SCHEDULED
FGR$AUTOPURGE_JOB             DISABLED
GATHER_STATS_JOB                 SCHEDULED
AUTO_SPACE_ADVISOR_JOB SCHEDULED
MGMT_CONFIG_JOB                   SCHEDULED
MGMT_STATS_CONFIG_JOB    SCHEDULED

How to disable  a scheduled  job  e.x   AUTO_SPACE_ADVISOR_JOB

SQL>Exec DBMS_SCHEDULER.DISABLE('AUTO_SPACE_ADVISOR_JOB');

How  to enable  a scheduled  job  e.x   AUTO_SPACE_ADVISOR_JOB

SQL>Exec DBMS_SCHEDULER.enable('AUTO_SPACE_ADVISOR_JOB');

Tuesday, April 27, 2010

How to setup RDA for RAC

  1. Visit  metalink for  lastest  version of RDA   and download it (example    p7563988_415_AIX64-5L.zip )
  2. Unzip it under  a  directory
    mkdir $HOME/RDA
    mv  p7563988_415_AIX64-5L.zip  $HOME/RDA
    cd $HOME/RDA
    unzip p7563988_415_AIX64-5L.zip
  3. Setup  it  for  RAC
    ./rda.sh -vX RDA::Remote setup_cluster
  4. You can  rda.sh  and  the  output  will be  under ./rda/setup/remote for  all  nodes (like  RDA_NOD001.zip,RDA_NOD002.zip... etc )

How to set and Check default tablespace

What is default tablespace

When we create a user and do not specify the default tablespace, the user is assigned the SYSTEM tablespace (If  we  have   not  specify a default  tablespace  for  database )

But  if  we  have specifed  a  default  tablespace  for  database  before  All users created without the DEFAULT TABLESPACE clause will have database default tablespace as their default.

How  to specify  default  tablespace

Default tablespace can be specified one of two ways at the database level:
  • During database creation via the CREATE DATABASE command .If the default tablespace is not specified during the database creation, it defaults to SYSTEM

  • After database creation via the ALTER DATABASE command. e.x

    SQL> alter database default tablespace TOOLS;
    Database altered.
Chaning the default tablespace

Changing the database default tablespace will change the default tablespace of the user even if the tablespace has been assigned during creation time.

 e.x  The  default tablespace  od  database is  tools   and  we  will  create 

SQL>CREATE USER TEST1 IDENTIFIED BY TEST1 ;

SQL>CREATE USER TEST2 IDENTIFIED BY TEST2 DEFAULT TABLESPACE TOOLS;

SQL>SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME IN ('TEST1','TEST2');


USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST1 TOOLS         ---User Default TS=tools  because  database default TS
TEST2 TOOLS         ---User Default TS=tools because create user ... default tablespace clause


SQL>alter database default tablespace  USERS ;
Database altered.

SQL>SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME IN ('TEST1','TEST2');

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST1 USERS
TEST2 USERS


Both users default  tablespace  changed  to  USERS

How  to check default tablespace

SQL>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES where PROPERTY_NAME in ('DEFAULT_PERMANENT_TABLESPACE');

PROPERTY_NAME PROPERTY_VALUE

------------------------------ --------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE TOOLS

How to  drop  default  tablespace

Once when default tablespace is set for the database it can’t be dropped until it’s changed to a different tablespace otherwise one will get ORA-12919 when trying to drop the tablespace


SQL> drop tablespace TOOLS  including contents and datafiles;
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

Sunday, April 25, 2010

NLS_LANG

NLS_LANG is used to let Oracle know what characterset you client's OS is USING so that Oracle can do (if needed) conversion from the client's characterset to the database characterset.

The characterset defined with the NLS_LANG parameter does NOT CHANGE your client's characterset, it is used to let Oracle *know* what characterset you are USING on the client side, so Oracle can do the proper conversion. You cannot change the characterset of your client by using a different NLS_LANG! To change the client characterset you need to change the OS configuration, not a Oracle parameters.

NLS_LANG consist of: NLS_LANG=_.

Each component of the NLS_LANG parameter controls the operation of a subset of globalization support features:

Language:Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names. Each supported language has a unique name; for example, AMERICAN, FRENCH, or GERMAN. The language argument specifies default values for the territory and character set arguments. If the language is not specified, then the value defaults to AMERICAN.


Territory:Specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA, FRANCE, or CANADA. If the territory is not specified, then the value is derived from the language value.

Charset:Specifies the character set used by the client application (normally the Oracle character set that corresponds to the user's terminal character set or the OS character set). Each supported character set has a unique acronym, for example, US7ASCII, WE8ISO8859P1, WE8DEC, WE8MSWIN1252, or JA16EUC. Each language has a default character set associated with it.

All components of the NLS_LANG definition are optional; any item that is not specified uses its default value. If you specify territory or character set, then you must include the preceding delimiter [underscore (_) for territory, period (.) for character set]. Otherwise, the value is parsed as a language name.


For example, to set only the territory portion of NLS_LANG, use the following format: NLS_LANG=_JAPAN

Wednesday, April 21, 2010

How to Change Interconnect/Public Interface IP Subnet in a 10g Cluster


This  document explains  how to change or update the cluster_interconnect and/or public interface IP information that is stored in the OCR

If  subnet or interface is  not  changed  only IP  is changed  then you can follow 

Modifying the VIP ,VIP Hostname of RAC Clusterware Node

else  if subnet is changed  then  you can change the interface information for a cluster using the following as an example
  1. Use the getif option to show the currently configured interfaces
    $CRS_HOME/bin/oifcfg getif
    eth0 10.2.156.0 global public
    eth1 192.168.0.0 global cluster_interconnect
  2. If the public interface IP needs to be changed, there is not a 'modify' option - you will need to delete the interface and add it back with the correct subnet
    $CRS_HOME/bin/oifcfg delif -global eth0
    $CRS_HOME/bin/oifcfg setif –global eth0/10.2.166.0:public
  3. The same step could be use for the private interface:
    $CRS_HOME/bin/oifcfg delif –global eth1
    $CRS_HOME/bin/oifcfg setif –global eth1/192.168.1.0:cluster_interconnect
  4. Verify the correct interface subnet is in use by re-running oifcfg with the 'getif' option
    $CRS_HOME/bin/oifcfg getif
    eth0 10.2.166.0 global public
    eth1 192.168.1.0 global cluster_interconnect

Modifying the VIP ,VIP Hostname of RAC Clusterware Node

To change the VIP IP address or Hostname or other parameters associated with the VIP
(especially wrong  virtual IP or wrong  enternet)

Changing the VIP involves modification of the nodeapps, which includes the Virtual IP address, the GSD, the Listener, and Oracle Notification Services (ONS). The VIP can be modified while the nodeapps are running, however changes will not take effect until the VIP, and hence the nodeapps, are restarted.  So we  will  stop and  start  the  nodeapps

Verifying Current VIP configuration

pandora@cbdst:/cbdsthome/cbdst$ srvctl config nodeapps -n pandora -a


VIP exists.: /cbdst2-vip/10.200.20.230/255.255.255.0/en5:en6:en7



The VIP Hostname is 'cbdst2-vip'
The VIP IP address is '10.200.20.230'
The VIP subnet mask is '255.255.255.0'
The Interface Name(s) used by the VIP is 'en5:en6:en7'

Now  We will change  the interface to only en7
Stop Resources
  • Stop database  (Where  database  name  =NGBSTEST and  instance_name =NGBSTEST1 )
      srvctl stop instance -d NGBSTESTRAC  -i NGBSTEST1
  • Stop asm if exists  on node  node  (pandora )
      srvctl stop asm -n pandora
  • Stop the nodeapps on  node (pandora )
      srvctl stop nodeapps -n pandora
  • Verify  that  VIP is no longer  exist  on node with ifconfig  -a
Note:Stopping the nodeapps may cause other resources to be stopped - therefore, this change should be made during a scheduled outage.

Make  the  changes
  
  • Make  the  changes
      srvctl modify nodeapps -n pandora -A 10.200.20.230/255.255.255.0/en7
  • start the node (By root)
     srvctl start nodeapps -n pandora
  • check  it
     pandora@cbdst:/cbdsthome/cbdst$ srvctl config nodeapps -n pandora -a
    VIP exists.: /cbdst2-vip/10.200.20.230/255.255.255.0/en7
  • Also  you can  check it  with  ifconfig 
We can  make  the same changes on other  node
Note:we did  not  change  the  interface stored in OCR 
pandora@cbdst:/cbdsthome/cbdst$ oifcfg getif

en7 10.200.20.0 global public
en6 7.0.0.0 global cluster_interconnect

if the interface stored in OCR   is changed then we must follow the following doc

How to Change Interconnect/Public Interface IP Subnet in a 10g Cluster

Tuesday, April 20, 2010

Encountered SCN That Is Not Greater Than The Highest SCN Already Processed

Encountered SCN That Is Not Greater Than The Highest SCN Already Processed  error  while  Golden Gate  replication .

Issue:

Extract ERROR 180 encountered commit SCN that is not greater than the highest SCN already processed
Solution Overview:

This error occurs shortly after a transaction is written to the idle node but does not yet appear in the redo log when the current transaction is processed. This transaction will have a higher SCN than the previous transaction
Solution Details:

In an Oracle RAC environment, Extract has a coordinator thread. This thread assembles the operations that are generated on each of the RAC nodes into SCN order. Before starting to assemble the transactions, Extract waits for a short time after Oracle writes the transaction to the redo log. If a node has been idle with no transactions, Extract waits the number of milliseconds specified in the Extract parameter THREADOPTIONS MAXCOMMITPROPAGATIONDELAY (default 550 ms).  Waiting ensures that there are no new transactions on the idle node before writing the current transactions to the trail.
Possible Reasons for error:

  • One of the threads is slower than the other.
  • The redo logs are not flushed on time due to latency on Log writes.
  • Network issues between Extract and one of the RAC nodes, if Extract is running on a system separate from RAC nodes.
  • Long log write times due to a standby configuration, if any.
  • Log file I/Os are taking unusually long times to complete.
  • Time imperfections between the cluster nodes. All nodes in the RAC cluster must have synchronized system clocks. If Extract is running on a system other than any of the RAC nodes, that system clock must be in sync
Recommendation
Avoid  to encounter again
  • The MAXCOMMITPROPAGATIONDELAY parameter can be used to set the delay time well above the max_commit_propogation_delay setting in the database, plus the default extra padding that Extract adds (2000 milliseconds).
  • In Oracle RAC, the max_commit_propogation_delay specifies the maximum amount of time allowed before the system change number (SCN) held in the SGA of an instance is refreshed by the log writer process (LGWR). Units are in hundredths of seconds.

    To check Oracle's value:
    SQL> show parameter max_commit
  • To set MAXCOMMITPROPAGATIONDELAY : The value of MAXCOMMITPROPAGATIONDELAY must be greater than zero and less than 90000 milliseconds e.x
    THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 2700
  • Starting from GGS Version 9.x and above, an additional parameter IOLATENCY can be used if Extract abends with 'encountered SCN XXXXX' too often. IOLATENCY adjusts the delta between the database-configured max commit propogation delay and the internal value that Extract uses.

    By default IOLATENCY is set to 1.5 seconds .Valid values for IOLATENCY are between 0 and 180000 milliseconds (3 minutes).

    The combined parameters should look like this
    THREADOPTIONS MAXCOMMITPROPAGATIONDELAY IOLATENCY
  • If the problem happens too often, you can start with high values for IOLATENCY and MAXCOMMITPROPAGATIONDELAY parameters. Once the error stops happening, you can gradually decrease the values to see the SCN number where this error starts appearing again. This would give you an idea of the boundary values specific to that environment.
See also:How to recover from Extract ERROR 180 encountered commit SCN that is not greater than the highest SCN already processed

Friday, April 16, 2010

when the database has started (DBA_HIST_DATABASE_INSTANCE)

DBA_HIST_DATABASE_INSTANCE displays the databases and instances in the Workload Repository at the same time It has history on information such as instance startup time, last ASH sample id.especially startup time is very useful

DBID NUMBER NOT NULL Database ID

INSTANCE_NUMBER NUMBER NOT NULL Instance number
STARTUP_TIME TIMESTAMP(3) NOT NULL Startup time of the instance
PARALLEL VARCHAR2(3) NOT NULL Indicates whether the instance is running in a RAC environment (YES) or not (NO)
VERSION VARCHAR2(17) NOT NULL Database version
DB_NAME VARCHAR2(9) Name of the database
INSTANCE_NAME VARCHAR2(16) Name of the instance
HOST_NAME                     VARCHAR2(64) Name of the host
LAST_ASH_SAMPLE_ID NUMBER NOT NULL Last sample ID for the active session history

SELECT startup_time, last_ash_sample_id FROM dba_hist_database_instance order by startup_time;

High 'enq: US - contention' waits (Contention with auto-tuned undo retention)

This is a  bug (Bug 7291739 )  confirmed as being affected (10.2.0.4) and fixed in 10.2.0.5
Symptoms
Waits for "latch: row cache objects"

Waits for "enq: US - contention"

Description
High 'latch: row cache objects' contention may be seen on dc_rollback_segments together with high 'enq: US - contention' waits when using system managed undo with an auto tuned undo retention period.

This fix allows users to specify a high threshold for undo retention via  the "_highthreshold_undoretention" parameter. The tuned value of undo retention  will then not exceed the set value. This underscore parameter is a dynamic  parameter and its value can be changed with

For 11.2
alter system set "_highthreshold_undoretention"=new value;
ALTER SYSTEM SET "_FIRST_SPARE_PARAMETER"=new value;

Workaround
Disable auto tuning by setting "_undo_autotune" = false
alter system set "_undo_autotune" = false

Oracle 10g Rac Installation on AIX system (cookbook)

This installation refer to  Oracle 10.2.0.4  +AIX 6.1  Rac  installation.We have used GPFS 3.2  but this document  does not cover  GPFS installation
  1. Take  2 virtiual IP and DNS (This IP 's will be attaced to the machines during the CRS installation do not attach this IP by yourself )

    10.200.20.229 cbdst1-vip
    10.200.20.230 cbdst2-vip
  2. Check Hardware
    Check hardware bitmode The expected output is the value 64.
    /usr/bin/getconf HARDWARE_BITMODE
    Check hostname Before setting the default hostname of each server, make sure to match it with the RAC Public node
    hostname check memory (Minumum 512m but for me  For test > 4g for prod > 8g )
    lsattr -El sys0 -a realmem



    Check Software installation internal disk for oracle code It must be >12g (CRS_HOME, ASM_HOME,ORACLE_HOME) also plan the other procdures written by DBA

    check paging Paging space = 2 x RAM
    lsps -a
    Ckeck /tmp It must be >400m 
    df -k /tmp

    check oslevel and compabilty.It must be the  same  for  both side  5.2,5.3  and  6.1  is  certificated  with 6.1  (To have the latest information please refer to Metalink Note 282036.1 )
    oslevel -s
    check Filesets

    lslpp -l bos.adt.base
    lslpp -l bos.adt.lib
    lslpp -l bos.adt.libm
    lslpp -l bos.perf.libperfstat
    lslpp -l bos.perf.perfstat
    lslpp -l bos.perf.proctools
    lslpp -l rsct.basic.rte
    lslpp -l rsct.compat.clients.rte
    lslpp -l xlC.aix61.rte   #This  can be xlC.aix50.rte for 5.3  and  version must 7.0.0.4 or 8.xxx
    lslpp -l xlC.rte

    check fixes


    For 5.3

    /usr/sbin/instfix -i -k "IY68989 IY68874 IY70031 IY76140 IY89080"
    For 6.1
    /usr/sbin/instfix -i -k "IZ10223"
  3. Check software user and group on both nodes
    id  cbdst  uid=109(cbdst) gid=109(dbat) groups=1(staff)

    cat /etc/group|grep cbdst
  4. Configure  kernel  parameters

    Configure Shell Limits for cbdst and root user
    ulimit -a
    time(seconds)            unlimited
    file(blocks)                  unlimited
    data(kbytes)               unlimited
    stack(kbytes)              4194304
    memory(kbytes)         unlimited
    coredump(blocks)      unlimited
    nofiles(descriptors)    unlimited
    threads(per process) unlimited
    processes(per user)  unlimited

    Configure System Configuration Parameters
    lsattr -El sys0 -a maxuproc                    #Cheking It must be 4096
    chdev -l sys0 -a maxuproc='4096'        #Setting

    Verify that the lru_file_repage parameter is set to 0 (Run it with root user )
    vmo -L lru_file_repage                                           #check
    vmo -p -o lru_file_repage=0                                  #set

    Setting Asynchronous I/O (from smitty aio for AIX 5.3  for AIX 6.1  could not be set)
    lsattr -El aio0         #check for AIX 5.3  nothing  for AIX 6.1

    Configure Network Tuning Parameters
    for i in ipqmaxlen rfc1323 sb_max tcp_recvspace tcp_sendspace udp_recvspace udp_sendspace
    do
        no -a |grep $i
    done

    ipqmaxlen = 512

    rfc1323 = 1
    sb_max = 1310720
    tcp_recvspace = 65536
    tcp_sendspace = 65536
    udp_recvspace = 655360
    udp_sendspace = 65536
  5. Network Identfication

    Ping nodes from each node
    ping santaro     10.200.20.29

    ping pandora   10.200.20.30

    ifconfig -l       #check for each node
    ifconfig -a    

    check for each node  and  similiar  IP must be defined  the same cards e.x for pandora
    en7:Public network 
    en6:Private   network
    en5:Backup network 

    en5: flags=1e080863,480
    inet 10.200.96.30 netmask 0xffffff00 broadcast 10.200.96.255
    tcp_sendspace 262144 tcp_recvspace 262144 rfc1323 1

    en6: flags=1e080863,480
    inet 7.0.0.2 netmask 0xffffff00 broadcast 7.0.0.255
    tcp_sendspace 262144 tcp_recvspace 262144 rfc1323 1

    en7: flags=1e080863,480
    inet 10.200.20.30 netmask 0xffffff00 broadcast 10.200.20.255
    tcp_sendspace 262144 tcp_recvspace 262144 rfc1323 1
    lo0: flags=e08084b
    inet 127.0.0.1 netmask 0xff000000 broadcast 127.255.255.255
    inet6 ::1/0
    tcp_sendspace 131072 tcp_recvspace 131072 rfc1323 1

    for i in `ifconfig -l`
    do
       echo $i
       for attribut in netaddr netmask broadcast state
       do
           lsattr -El $i -a $attribut
      done
    done
  6. Plan the IP 's ( Virtual,GPFS ,ODM IP's ) modify in /etc/hosts
    #Public network for Oracle RAC
    10.200.20.29 santaro
    10.200.20.30 pandora

    #Virtual IP addresses for Oracle RAC
    10.200.20.229 cbdst1-vip
    10.200.20.230 cbdst2-vip

    #Private(Intercennect) for Oracle RAC
    7.0.0.1 santaro-priv
    7.0.0.2 pandora-priv

    #Interconnect for GPFS
    7.0.0.1 santaro-rac
    7.0.0.2 pandora-rac

    #IP for ODM network IP the same
    10.200.96.29 santaro-bkp
    10.200.96.29 pandora-bkp
  7. Check Default gateway on public interface
    netstat -r |grep default 
  8. User Equivalence Setup (It can rsh or ssh implementation we will implemant ssh ) ssh-keygen may be installted on diffrent  path

    on first node run it set by step continue with enter (santaro)
    mkdir ~/.ssh
    chmod 700 ~/.ssh
    /usr/local/bin/ssh-keygen -t rsa
    /usr/local/bin/ssh-keygen -t dsa
    touch ~/.ssh/authorized_keys
    cd ~/.ssh
    cat $HOME/.ssh/id_rsa.pub>>$HOME/.ssh/authorized_keys
    cat $HOME/.ssh/id_dsa.pub>>$HOME/.ssh/authorized_keys


    on second node run it set by step and continue with enter (pandora)
    mkdir ~/.ssh
    chmod 700 ~/.ssh
    /usr/local/bin/ssh-keygen -t rsa
    /usr/local/bin/ssh-keygen -t dsa
    touch ~/.ssh/authorized_keys
    cd ~/.ssh
    cat $HOME/.ssh/id_rsa.pub>>$HOME/.ssh/authorized_keys
    cat $HOME/.ssh/id_dsa.pub>>$HOME/.ssh/authorized_keys

    copy the authorized_keys from firts node (on pandora )
    rcp cbdst@santaro:$HOME/.ssh/authorized_keys $HOME/.ssh/authorized_keys.node1

    Join authorized_keys files from both node (on santaro )
    cat $HOME/.ssh/authorized_keys.node1 >>$HOME/.ssh/authorized_keys
    rm $HOME/.ssh/authorized_keys.node1

    Copy the joined file to other node1 (on pandora)
    rcp $HOME/.ssh/authorized_keys cbdst@santaro:$HOME/.ssh/authorized_keys

    run this command on both node
    chmod 600 ~/.ssh/authorized_keys

    Run all this command 2 times on both node as root and software user(cbdst)  it will ask ask question for first run

    ssh santaro date
    ssh pandora date
  9. Preventing Oracle Clusterware Installation Errors Caused by stty Commands put in .profile as root and software user

    if [ -t 0 ]; then

    stty intr ^C
    fi
  10. edit .profile  and  rerun (. .profile )
    if [ -t 0 ]; then

    stty intr ^C
    fi

    export ORACLE_BASE=/cbdsthome/oracle/app/oracle
    export ORA_CRS_HOME=/cbdsthome/crshome
    export CRS_HOME=$ORA_CRS_HOME
    export ORACLE_HOME=/cbdsthome/oracle/app/oracle/product/10g
    export ORA_NLS10=$ORACLE_HOME/nls/data
    export ORACLE_OWNER=cbdst
    export ORACLE_SID=NGBSTEST1   #This  will be NGBSTEST2  for  other node


    export LD_LIBRARY_PATH=PATH=$ORACLE_HOME/lib:$CRS_HOME/lib:$ORACLE_HOME/lib32:$CRS_HOME/lib32:/usr/ccs/lib:/usr/ucblib:/usr/java/lib:$LD_LIBRARY_PATH
    export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/usr/sbin:/usr/bin:/bin:/usr/ccs/bin:/usr/ucb:/usr/local/bin:/opt/gnu/bin:.:/usr/bin/X11:$ORACLE_HOME/OPatch
    export AIXTHREAD_SCOPE=S
    export LIBPATH=$LD_LIBRARY_PATH

    export ORA_SQLDBA_MODE=line
    export NLS_LANG=Turkish_Turkey.WE8ISO8859P9
    export NLS_NUMERIC_CHARACTERS='.,'


    export TERM=vt100
    export EDITOR=vi
    set -o vi
    stty erase ^? 1>/dev/null 2>&1
    umask 022

    export TEMP=/tmp
    export TMP=/tmp
    export TMPDIR=/tmp
  11. Check all disks

    for l in `lspv |awk {'print $1'}` do
      lscfg -vl $l |grep hdisk
    done
  12. Check if the same disks corresponds the same (This is for 8300)
    node1='santaro'
    node2='pandora'
    for l in `lspv |grep -v rootvg|awk {'print $1'}`
    do
     node1_lun_id=`ssh $node1 lscfg -vl $l|grep "Serial Number"|sed 's/ Serial Number//g'|sed 's/\.//g'`
    node2_lun_id=`ssh $node2 lscfg -vl $l|grep "Serial Number"|sed 's/ Serial Number//g'|sed 's/\.//g'`
    if [ $node1_lun_id == $node2_lun_id ]
    then
      echo "$l node1_lun_id=$node1_lun_id node2_lun_id=$node2_lun_id Equallll"
    else
      echo "$l node1_lun_id=$node1_lun_id node2_lun_id=$node2_lun_id  not Equallll"
    fi
    done
  13. Check ocr and voting disks They must correspond the same disk and reserve_policy must be no_reserve
  14. Control ocr and vote disks from both node  that they  correspends  the same  disks (e.x /dev/ocrdisk1)

    On node1 (santaro)
    santaro@cbdst:/cbdsthome/cbdst$ ls -lrt /dev/ocrdisk1
    crw-r----- 1 root dbat 19, 40 May 06 11:57 /dev/ocrdisk1

    santaro@cbdst:/cbdsthome/cbdst$ ls -lrt /dev/|grep "19, 40"
    crw------- 1 root system 19, 40 Apr 12 15:56 rhdisk4
    brw------- 1 root system 19, 40 Apr 12 15:56 hdisk4
    crw-r----- 1 root dbat 19, 40 May 06 11:57 ocrdisk1

    On node2(pandora)

    pandora@cbdst:/cbdsthome/cbdst$ ls -lrt /dev/ocrdisk1

    crw-r----- 1 root dbat 20, 42 Jun 09 09:41 /dev/ocrdisk1

    pandora@cbdst:/cbdsthome/cbdst$ ls -lrt /dev/|grep "20, 42"
    crw------- 1 root system 20, 42 Apr 12 15:56 rhdisk4
    brw------- 1 root system 20, 42 Apr 12 15:56 hdisk4
    crw-r----- 1 root dbat 20, 42 Jun 09 09:41 ocrdisk1

    We can see  that  on both node  /dev/ocrdisk1 correspands  the same (hdisk4) disk.Then check  all ocr and  voting  disks


    ls -lrt /dev/ocrdisk1
    ls -lrt /dev/ocrdisk2


    ls -lrt /dev/votedisk1
    ls -lrt /dev/votedisk2
    ls -lrt /dev/votedisk3
  15. Change ownership and permision of ocr and vote disks on both node as root

    chown cbdst:dbat /dev/votedisk1
    chown cbdst:dbat /dev/votedisk2
    chown cbdst:dbat /dev/votedisk3
    chown cbdst:dbat /dev/ocrdisk1
    chown cbdst:dbat /dev/ocrdisk2



    chmod 660 /dev/ocrdisk1
    chmod 660 /dev/ocrdisk2
    chmod 660 /dev/votedisk1
    chmod 660 /dev/votedisk2
    chmod 660 /dev/votedisk3
  16. Format OCR and Vote Disks as root on both node at the same time

    for i in 1 2
    do
      dd if=/dev/zero of=/dev/ocrdisk$i bs=8192 count=25000
    done

    for i in 1 2 3
    do
      dd if=/dev/zero of=/dev/votedisk$i bs=8192 count=25000
    done

     
  17. CRS Installation preparation
    1. Run root.pre.sh on both node as ROOT
      cd
      cd ./clusterware/rootpre
      ./rootpre.sh

      For AIX 6.1  download 6613550 (Because of bug ) and run under it
      cd <6613550>
      ./rootpre.sh
    2. check unzip on both node with the oracle software user
      which unzip 
    3. Run runcluvfy.sh as oracle software user and check cluvf.txt with the cookbook (only one node )
      cd
      cd ./clusterware/cluvfy
      ./runcluvfy.sh stage -pre crsinst -n santaro,pandora -verbose >/tmp/cluvf.txt

      On AIX 6.1  there is another  bug  so  result of runcluvfy.sh may be  uncompleted .
    4. Create a symbolic link from /usr/sbin/lsattr to /etc/lsattr on both node as ROOT
      ln -s /usr/sbin/lsattr /etc/lsattr
    5. OS Capabilities for CRS User to run Oracle Clusterware software on both node as ROOT

      /usr/sbin/lsuser -a capabilities cbdst
      /usr/bin/chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE,CAP_NUMA_ATTACH cbdst
      /usr/sbin/lsuser -a capabilities cbdst 

  18.  CRS installation  (Restarting both nodes can be usefull before installation)
    1. Run slibclean on both node as ROOT
      /usr/sbin/slibclean
    2. Start installer  on one node as  oracle  user  (software  is  not compatibale  with  aix 6.1  so  we  can  continue  if prerequieste reports  errors
      cd

      cd ./clusterware
      ./runInstaller
    3. Specify oraInventory,CRS_HOME and prereqiuset check  DO NOT install CRS_HOME in ORACLE_HOME  
    4. specify the cluster configration example
      specify the cluster name (cbdstcrs)

      edit and add nodes as

      Public Node Name:santaro
      Private Node Name:santaro-privs
      Virtual Host Name:cbdst1-vip


      Public Node Name:pandora
      Private Node Name:pandora-priv
      Virtual Host Name:cbdst2-vip
    5. Specify the network Interfaces example
      10.200.20.0 Public

      10.200.96.0 Do not use
      7.0.0.0 Private
    6. Specify OCR locations
      /dev/ocrdisk1

      /dev/ocrdisk2
    7. Specify Voting disk locations
      /dev/votedisk1

      /dev/votedisk2
      /dev/votedisk3
    8. Wait Until root scripts dialog box appears. (Bug 4437469 $ENTSTAT -d $_IF

      $GREP -iEq ".*lan state:.*operational.*") DO NOT run root.sh before THIS STEP on NOTH NODES
      vi $CRS_HOME/bin/racgvip   #ON BOTH NODES  ROOT

      $ENTSTAT -d $_IF |$GREP -iEq ".*link.*status.*:.*up.*"
      replace
      $ENTSTAT -d $_IF |$GREP -iEq '.*lan.*state.*:.*operational.*|.*link.*status.*:.*up.*|.*port.*operational.*state.*:.*up.*'
    9. After root.sh on node2 It will give "The given interface(s), "en7" is not public. Public interfaces should be used to configure virtual IPs." run vipca AS ROOT ON FIRST NODE1 and fix it then click  ok

      connect to one node  as root  run vipca  under  $CRS_HOME/bin and  fix vip ip's  and  public interface  (in this example  en7,cbdst1-vip cbdst2-vip)
    10. Installation  is completed  then check th results 

      ifconfig -a #Check on both nodes virtual IP are taken by machines

      crsctl check crs
      crsctl query crs activeversion
      crs_stat -t #GSD,ONS,VIP applications must be online
      olsnodes #This should return all the nodes of the cluster
      oifcfg getif #check public and private networks
      ocrcheck #check ocr
      crsctl query css votedisk #check voting disk
      ocrconfig -export /cbdstdump/ocrdump.dmp -s online # Export Oracle Cluster Registry content as root
      ocrconfig -showbackup #check automatic backup period .First It can be null
    11. Change MISSCOUNT DEFINITION AND DEFAULT VALUES and restart nodes
      Keep only one node up and running, stop the others.close NODE1

      srvctl stop nodeapps -n santaro #as ROOT on santaro (node1)
      crsctl get css misscount #as ROOT on pandora (node2)
      crsctl set css misscount 30 #as ROOT on pandora(node2)
      srvctl start  nodeapps -n santaro #as ROOT on santaro (node1)
    12. Set racdiagwait (also we  can  follow Diagnosing Oracle Clusterware Node evictions (Diagwait))

      crsctl stop crs                                #as ROOT on both node
      $CRS_HOME/bin/oprocd stop  #as ROOT on both node
      ps -ef |egrep "crsd.bin|ocssd.bin|evmd.bin|oprocd"   #as ROOT on both node
      crsctl set css diagwait 13 -force                 #as ROOT on santaro(node1)
      crsctl get css diagwait                                #as ROOT on both node
      crsctl start crs                                              #as ROOT on both node
      crsctl check crs                                           #as ROOT on both node

  19.  CRS patch installation (e.x 10.2.0.4)
    1. Stop nodes by root  (e.x nodes  are santaro ,pandora )
      srvctl stop nodeapps -n santaro

      srvctl stop nodeapps -n pandora
    2. Stop crs on both node by root
      crsctl stop crs
    3. Take the backup of CRS_HOME and  OraInventory  on both node  as software user  with tar  command
    4. Execute following commands on both node by root (Beacuse of bug 6910119)
      chown -R cbdst $CRS_HOME/inventory/Templates/*
      chgrp -R dbat $CRS_HOME/inventory/Templates/*
    5. execute preupdate.sh script from $CRS_HOME/install on both node by root
      cd $CRS_HOME/install
      ./preupdate.sh -crshome $CRS_HOME -crsuser cbdst
    6. Run this on both node by root
      ln -s /usr/sbin/sync /usr/bin/sync
    7. Run slibclean on both node
      /usr/sbin/slibclean
    8. CD PATCH/Disk1 direcotory and runInstaller with software user
      ./runInstaller -ignoreSysPrereqs
    9. Choose CRS_HOME to patch and both nodes to patch
    10. You will have copy error you can ignore it if opatch shows okey after installation (Becasu of bug 6475472 )
    11. After running 2 scripts on both node (you may have VIP problems but they will be okey after bundle patch )
  20. Oracle Home  Install
    1. run /usr/sbin/slibclean ROOT on ALL NODES
    2. run install database
      cd
      cd ./database
      ./runInstaller
    3. specify ORACLE_HOME and path
      Select all nodes to install (not local installation )

      prereqiuset check may give error we can continue (If failed component is only bos.cifs_fs.rte,5.3.0.1) and it will give error for aix 6.1
      select "install database only "
  21. Oracle home  products  install  (like  companion ,client .etc)
  22. Oracle Home  patch  (e.x 10.2.04) install 
    1. run /usr/sbin/slibclean ROOT on ALL NODES
    2. run runInstaller
      cd
      ./runInstaller
  23. Find  the lastest opatch version and  apply it 
    cp p6880880_102000_AIX64-5L.zip $ORACLE_HOME

    cd $ORACLE_HOME
    mv OPatch OPatch_OLD
    unzip p6880880_102000_AIX64-5L.zip
  24. Read Note 405820.1 and find the latest Bundle Now CRS_HOME and  DATABASE_HOME  and  apply them  explained  as   their  readme
    9294403- CRS BUNDLE #4
    9352164 10.2.0.4 database patchset
  25. Now we can  create   database  by dbca

Thursday, April 15, 2010

DB 10gR2 Installation on AIX 6.1 Error: OUI-18001: 'AIX Version 6100.01' is not supported

Following error while installing the Oracle Database 10gR2 (10.2.0.1.0) on AIX 6.1


OUI-18001: The operating system 'AIX Version 6100.01' is not supported.


Oracle Database 10gR2 (10.2.0.1.0) installation kit does not include AIX 6.1 as certified OS version. Because IBM's AIX 6.1 released after Oracle's Database 10gR2 (10.2.0.1.0) software is released. And hence AIX 6.1 certification details not included in already released Database 10gR2 installation kit.


Oracle has completed the certification of AIX6.1 to install/run Oracle Database 10gR2 (10.2.0.1.0). To install 10gR2 (10.2.0.1.0), please invoke the OUI (runInstaller) using "-ignoreSysPrereqs" option.

$ cd Disk1

$ ./runInstaller -ignoreSysPrereqs

Wednesday, April 14, 2010

How to find whether the one-off Patches will conflict or not ?

 It is difficult to predict whether or not the Patches will conflict without actually applying them, but we can use the OPatch tool to check the same without actually applying the Patches to the Oracle Home
  1. Make sure that the latest opatch version is being used  (You should download the latest OPatch via Metalink Patch 6880880 ) and  unzip it  under  $ORACLE_HOME
  2. After confirming that you have the latest version of OPatch, proceed to download all the patches that you intend to install and unzip them into the same directory  (e.x $HOME/patches)
  3. unzip them under  patch directory  (e.x $HOME/patches)
  4. Run the following command to check whether there are any conflicts among the patches to be applied (e.x patch directory  is  $HOME/patches )

     opatch prereq CheckConflictAmongPatchesWithDetail -phBaseDir $HOME/patches
  5. Run the following command to check whether there are any conflicts between the patches to be applied and the patches already applied in the Oracle Home (e.x patch directory is $HOME/patches )

    opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir  $HOME/patches

NetCA and DBCA Fail To Launch Due to TNS-4414 Error

Symptoms


Net Configuration Assistant throws the following error stack when launched:
Oracle Net Services Configuration:

ProfileException: Could not create Profile: TNS-04414: File error

Cause

The sqlnet.ora file had been manually edited. Unable to discern exact character or syntax issue

Solution
Use Net Manager to create and edit the sqlnet.ora file.

Do not edit the sqlnet.ora file manually

Tuesday, April 13, 2010

DBMS_STATS versus ANALYZE

The ANALYZE command and the DBMS_STATS package offer two different ways to collect statistics about the objects in your database. Oracle’s query optimizer uses these statistics when determining the most efficient way to perform a query


important differences, advantages with DBMS_STATS over ANALYZE are
  1. The DBMS_STATS package was introduced in Oracle 8i, and for the last few years Oracle Corporation has been strongly advising customers to use DBMS_STATS instead of ANALYZE so dbms_stats is the stated, preferred method of collecting statisttics
  2. DBMS_STATS has parallel statistics collection
  3. We can import/export/set statistics directly with dbms_stats
  4. Dbms_stats can analyze external tables, analyze cannot.
  5. The DBMS_STATS package can gather global statistics at multiple levels as specified by the granularity parameter.(partition-level and subpartition-level). In contrast, the legacy ANALYZE command collects statistics only at the lowest level and derives higher level statistics by aggregation. These aggregated statistics are sometimes less accurate than the global statistics since it is not possible to precisely determine the overlap of values across partitions. Statistics for the number of distinct values and density are particularly susceptible to inaccuracy.

    The optimizer uses global statistics as the basis of access path generation unless query predicates narrow the query to a single partition. Since most queries are unlikely to be this restrictive, it is important to have accurate global statistics. Gathering global statistics with the DBMS_STATS package is highly recommended.
  6. dbms_stats (in 9i) can gather system stats 
  7. DBMS_STATS gathering statistics only when existing statistics are stale
  8. it is easier to automate with dbms_stats (it is procedural, analyze is just a command)
  9. DBMS_STATS gathers statistics only for cost-based optimization; it does not gather other statistics. For example, the table statistics gathered by DBMS_STATS include the number of rows, number of blocks currently containing data, and average row length but not the number of chained rows, average free space, or number of unused data blocks.
  10. Most importantly, in the future, ANALYZE will not collect statistics needed by the cost-based optimizer.  

Friday, April 9, 2010

How to force log switch on every XXXX minutes (ARCHIVE_LAG_TARGET)

For performance reason It is preferred that redo switches should not be less than 15 minutes .So we must resize our redo logs optimally (for deatil look Redo logs sizing advisory article


When we plan our redo logs for peak hours.while off peak-hours the redo switch does not occur for hours .This sitiuation has some risk


  1. In a case of catastrophic hardware failure, we may lose our current redo log having a enormous long redo data.There is no way to recover transactions that are sitting in the current redo log .

  2. Standby and Primary database may not be close synchronous If you useMAXIMUM AVAILABILITY

so It will be better to switch log for each specified interval.ARCHIVE_LAG_TARGET parameter simply forces a log switch at a specified interval, and that can be very useful.


In order to set 15 minutes (900 seconds )


ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 900 SCOPE=BOTH;


Thursday, April 8, 2010

ORA-02064: Distributed Operation not Supported

ORA-02064 may occur may occur when A commit(also rollback) or autocommit(like DDL) operation is executed within a procedure or function called remotely via database link.Examples are follows

Example-1 : commit issued on remote database

On database A:
create table serdar.bbb ( n number);

create or replace FUNCTION serdar.func_distributed_commit RETURN NUMBER IS
BEGIN
insert into bbb values (1);
commit;
RETURN 1;
END;
/

grant execute on serdar.func_distributed_commit to linkuser ;

Then, we call it from Database B:

DECLARE
rc NUMBER;
BEGIN
rc := serdar.func_distributed_commit@link.databaseA;
END;
/

ORA-02064: distributed operation not supported
ORA-06512: at "SERDAR.FUNC_DISTRIBUTED_COMMIT", line 4
ORA-06512: at line 4


Example-2 : DML issued on remote database

On database A:
CREATE OR REPLACE FUNCTION serdar.func_distributed RETURN NUMBER IS
BEGIN
EXECUTE IMMEDIATE 'create table serdar.bbb as select * from dba_tables';
RETURN 1;
END;
/

grant execute on serdar.func_distributed to linkuser;


Then, we call it from Database B:

DECLARE
rc NUMBER;
BEGIN
rc := serdar.func_distributed@link.databaseA;
END;
/
ORA-02064: distributed operation not supported
ORA-06512: at "SERDAR.FUNC_DISTRIBUTED", line 3
ORA-06512: at line 4


Other causes and reference note
Other causes:
ORA-02064:distributed operation not supported
Cause:
1.One of the following unsupported operations was attempted:1. array execute of a remote update with a subquery that references a dblink, or
2. an update of a long column with bind variable and an update of a second column with a subquery that both references a dblink and a bind variable, or
3. a commit is issued in a coordinated session from an RPC procedure call with OUT parameters or function call.
Action:simplify remote update statement

Workaround:
As a workaround Pragma AUTONOMOUS_TRANSACTION can be used in the called function or procedure in the remote site package.

REDO LOGS SIZING ADVISORY

Why redo log size is important



  • The size of the redo log files can influence performance, Too small, and the frequent log switches tie-up the LGWR, ARCH and DBWR background processes.Undersized log files increase checkpoint activity and increase CPU usage.

How to Tune Checkpoint

  • Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter.There is an detail articale about tunning checkpoint (http://serdarturgut.blogspot.com/2007/09/tune-checkpoint.html)

  • you can check it
    show parameter fast_start_mttr_target


How to check redo size and redo switch

  • Prior to 10g, it was not possible to provide a specific size recommendation for redo log files. Generally it is recommended to size your redo log file in a way that Oracle performs a log switch every 15 to 30 minutes.

  • You can check log switches from v$log_history in a day or per hour etc.
    select * from v$log_history where first_time >trunc(sysdate);

  • You can check the log size from v$log

    select GROUP# , BYTES,archived,status from v$log;
    GROUP# BYTES ARC STATUS
    --------- ------------------------ --- ----------------
    1 10,485,760 YES ACTIVE
    2 10,485,760 NO CURRENT
    3 10,485,760 YES ACTIVE
    4 10,485,760 YES ACTIVE

  • Note that ARCHIVE_LAG_TARGET can also be used to force a log switch after the specified amount of time elapses.
    show parameter archive_lag_target

How to set and Check the reso size advisory

  • The redo logfile sizing advisory is a new feature in 10g where in the redo logfile size can tuned as per recommendation specified by column optimal_logfile_size of v$instance_recovery.This feature require setting the parameter "fast_start_mttr_target" for the advisory to take effect and populate the column optimal_logfile_size.
    select ACTUAL_REDO_BLKS ,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR, OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES
    from v$instance_recovery;
    ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE CKPT_BLOCK_WRITES
    ---------------- ---------------- ----------- -------------- -------------------- -----------------
    35190 55260 31 24 140 4144049

  • The value for optimal_logfile_size is expressed in megabytes (For this example optimal size is 140m ) and it changes frequently, based on the DML load on your database.