RAC Management tools

• olsnodes
• olsnodes command provides the list , their VIP and numbers of nodes in the cluster. It is found at $ORA_CRS_HOME/bin directory
• Oifcfg
• Oracle Interface configuration tool (oifcfg) helps to define and administer network interfaces
1. oifcig iflist
2. oifcfg setif
3. oifcfg getif
4. oifcfg delif
• crsctl
• crsctl commands perform the following tasks on Oracle clusterware:
• Starting and stopping Oracle clusterware resources
•  crsctl start cluster
• crsctl stop cluster
• crsctl check cluster
• Enabling and disabling Oracle clusterware daemons
• Checking the health of the cluster
• crsctl check cluster
• crsctl check crs
• crsctl check resource
• crsctl check ctss
•  Managing thrid party application resources
• Integrating Intelligent Platform mangement interface (IPMI) with Oracle clusterware
• Debugging Oracle clusterware components

RAC Preinstallation Check

• Memory requirement
• Every node should have minimum of 1 GB
• prtconf | grep Memory
• Swap requirement
• Swap space should be set to twice the amount of RAM for systems with 2GB of RAM or less. For systems with 2GB to 8GB, use swap space equal to RAM. For systems over 8GB , use .75 times of the size of RAM
• Verify the swap is set to .75 times the size of RAM
• swap -s
• tmp space
• Atleast 400MB of disk space is required in /tmp
• df -h /tmp
• Maximum Open File descriptors
• To check ulimit -n
• To set ulimit -n <new value>
• Network requirements
• You should have minimum of 2 network interfaces per node
• dladm show-link
• You should have three network address for each node
• Public IP address
• ping <public-node-name>
• Virtual IP address : Used by applications for failover in case of node failure
• Do not plump Virtual ip address. Pinging virutal address should result in failure
• The virtual IP address is on the same subnet as your public interface
• Private IP address: Used by Oracle clusterware for internode communication
• It should be on the same subnet reserved for private networks such as 10.0.0.0 or 192.168.0.0
• It should use dedicated switches or physically separate private network, reachable only by the cluster member nodes, prefably using high-speed NICs
• It cannot be registered on the same subnet that is registered to a public IP address
• ping <private-node-name>
• The /etc/hosts should have following entries for each node
• Your public node name,public node name.domainname
• Your private node name, private nodename.domainname
• Your vip node name, vip nodename.domainname
• About interfaces on all nodes
• The public interface names associated with the network adapters for each network must be the same on all nodes and the private interface names assoicated with the network adaptors should be the same For example: With a two-node cluster, you cannot configure network adapters on node1 with eth0 as the public interface, but on node2 have eth1 as the public interface. Public interface names must be the same, so you must configure eth0 as public on both nodes. You should configure the private interfaces on the same network adapters as well. If eth1 is the private interface for node1, then eth1 should be the private interface for node2.
• SSH connectivity
• Passwordless SSH connectivity should be establed between all cluster nodes.  OUI can automatically configure password SSH connectivity. For that to happen, make sure there are no stty commands in oracle user probile. By default OUI searches for public keys in /usr/local/etc directory and it searches for ssh-keygen binaries in /usr/local/bin directory. However, in Solaris public keys are found under /etc/ssh and ssh-keygen binaries are under /usr/bin. So the following softlinks needs to be created prior to starting OUI
• ln -s /etc/ssh /usr/local/etc
• ln -s /usr/bin /usr/local/bin
• Create the links as mentioned above and invoke sshsetup.sh script in staging area. Verify you can ssh without password
• ssh <node1> date
• ssh <node2> date
• More info
• http://dbastreet.com/docs/ChecklistSolaris11g.pdf
• Additional notes
• Verifying the existance of public IPs and VIPs
• Use ypwhich, ypcat hosts    
• Network setup : Refer to IP services guide for details
• Issue dladm show-link command to find out installed interfaces   
• Issue ifconfig -a command to determine which interface is plumped 
• To configure and plump an interface named el000g1
• ifconfig e1000g1 plump up      
•  ifconfig e1000g1 <address> netmask+  
• Verify that interface is up : ifconfig -a    
• To make interface e1000g1 plumping persistent across reboots
• Create a file /etc/hostname.e1000g1      
• Add the address of the interface to this file
• vi /etc/hostname.e1000g1    
• Add entries for the new interface into /etc/inet/ipnodes     
• Perform a reconfiguration reboot
• reboot -- -r       
• Verfiy that interface is up : ifconfig -a      
• Solaris supports two types of interfaces
• Legacy interfaces
• They are DLPI and GLDv2 interfaces. Some legacy types are eri, qge,and ce  
• Non-VLAN interfaces
• These interfaces are GLDv3 interfaces.    
•  bge,xge and e1000g are non-VLAN interfaces      

ASM does not start after system reboot

Metalink note if ASM is not coming up
https://support.oracle.com/CSP/main/article?cmd=show&id=947520.1&type=NOT


inf01% crsctl check css
CRS-4529: Cluster Synchronization Services is online
inf01% crsctl check has
CRS-4638: Oracle High Availability Services is online
inf01%


if has is not online : crsctl start has


Also, start the listener from $ASM_HOME. Also verify that ORACLE_HOME and ORACLE_SID are set correctly.

About tablespace

execute the following query to find out datafile-tablespace association:

col file_name format a50
col tablespace_name format a10

 
SELECT file_name, tablespace_name, (bytes/1024 * 1024) MB
FROM dba_data_files
ORDER BY 1;
 
Set autoextend on for the datafile associated with required tbsp:

alter database datafile
   '/u01/app/oracle/ts_32k.dbf'
autoextend on;
More info about tbspc: http://psoug.org/snippet/TABLESPACE--List-tablespaces-files-allocated-and-free-space_852.htm

About Schema

To calculate the schema (user) size :

• The schema is owned by the user. To find out which user has how big of the DB:

SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB FROM dba_segments s GROUP BY s.owner;

• If you are logged in as the schema user, you can issue the following command to get the size of the database files:

select sum(bytes)/1024/1024/1024 data_size from dba_data_files ;

• You can find the size of the individual tables in that schema using the following query:

select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner='<schema owner username>'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by MB desc -- Biggest first.
;

• To list all the objects owner by the schema user :

SELECT tablespace_name, owner, segment_type "Object Type"
FROM   sys.dba_segments WHERE  owner='TKCSOWNER'  ;

select object_name, object_type from user_objects where object_type='TABLE' ;
select object_name, object_type from user_objects where object_type='INDEX' ;


Here is the script that does that :


spool  sch.out replace
column object_name format a30
select object_name, object_type from user_objects where object_type='TABLE' ;
column owner format a30
SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB FROM dba_segments s GROUP BY s.owner;
select sum(bytes)/1024/1024/1024 data_size from dba_data_files ;
column owner format a30
column table_name format a30
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner='tpcc'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by MB desc -- Biggest first.
;
spool off ;

• To find info about columns and indexes on all tables

spool /tmp/123.lst replace
column table_name format a20
select  table_name, count(*) from all_tab_columns where owner='TKCSOWNER' and table_name in (select object_name from user_objects where object_type='TABLE') group by table_name;

column uniqueness format a15
column index_name format a15
column table_name format a15
column column_name format a15
select
b.uniqueness, a.index_name, a.table_name, a.column_name
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.index_owner='TKCSOWNER'
and a.table_name in (select object_name from user_objects where object_type='TABLE')
order by a.table_name, a.index_name, a.column_position;

spool off;

To find out if any non standard block size is used:
select unique block_size from V$buffer_pool ;

Undo Space Mangement

Undo space management:

You can look at V$UNDOSTAT view to estimate the amount of undo space required for current workload.
Oracle uses this view to tune undo usage in the system. This view returns NULL if the system is in manual undo managment mode.

Each row in the view keeps statistics collected in the instance for a 10 minute interval. The rows are in descending order; so the first row  has the info about the current time period. The view contains a total of 576 rows max, spanning a 4 day cycle.

It contains following important statistics:

undotsn: the tablespace id of the active undo tablespace
txncount : total number of transactions in 10 minute cycle
maxquerylen: how much time in seconds did the longest query take

Find out HBA port WWN on Solaris

• Issue the following command on the host to find out how many HBAs are connected to the host:

• luxadm -e port 

• The output will be as under:
• /devices/pci@0/pci@0/pci@8/pci@0/pci@a/SUNW,qlc@0/fp@0,0:devctl    NOT CONNECTED
• /devices/pci@0/pci@0/pci@8/pci@0/pci@a/SUNW,qlc@0,1/fp@0,0:devctl  CONNECTED
• The output shows that one HBA card is connected to the host

• Issue the following command to get the port numbers and associated WWN for each HBA connected to the host
• luxadm -e dump_map /devices/pci@0/pci@0/pci@8/pci@0/pci@a/SUNW,qlc@0,1/fp@0,0:devctl 

•  The sample output is as under:

Pos  Port_ID Hard_Addr Port WWN         Node WWN         Type 0    170900  0         20030003baccc902 10000003baccc902 0x0  (Disk device) 1    1c0300  0         10000000c9722f33 20000000c9722f33 0x1f (Unknown Type) 2    1f0200  0         20030003ba13e6a1 10000003ba13e6a1 0x0  (Disk device) 3    140300  0         2101001b322f4f21 2001001b322f4f21 0x1f (Unknown Type,Host Bus Adapter)

• The above output shows that the QLC HBA has two ports  namely 0 and 2 with port  wwn 20030003baccc902 and 20030003ba13e6a1 which can be mapped to the luns on the storage