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