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.

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');

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

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

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

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

• If you have encountered  this problem .You must  follow  the following articale  in order to  make extract   running  (How to recover from Extract ERROR 180 encountered commit SCN that is not greater than the highest SCN already processed )

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

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

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

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

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
3. DBMS_STATS has parallel statistics collection
4. We can import/export/set statistics directly with dbms_stats
5. Dbms_stats can analyze external tables, analyze cannot.
6. 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.
   
7. dbms_stats (in 9i) can gather system stats 
8. DBMS_STATS gathering statistics only when existing statistics are stale
9. it is easier to automate with dbms_stats (it is procedural, analyze is just a command)
10. 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.
11. Most importantly, in the future, ANALYZE will not collect statistics needed by the cost-based optimizer.  

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;

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.