Monday, October 15, 2012
Script to find out space saving with HCC
The following scrips loops through a list of tables and displays estimated compression ratio for all the tables for all types of HCC compression :
set serveroutput on
spool all.out
declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
type Str_Array is VARRAY(10) of VARCHAR2(50);
v_array Str_Array ;
begin
v_array:= Str_Array('lineitem','nation');
for i in v_array.first .. v_array.last loop
dbms_output.put_line('hello ' || v_array(i));
end loop;
for i in v_array.first .. v_array.last
loop
for j in 1..5
loop
dbms_compression.get_compression_ratio(
scratchtbsname => upper('scratch1'),
ownname => upper('tpch'),
tabname => upper(v_array(i)),
partname => NULL,
comptype => power(2,j),
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str,
subset_numrows => -1) ;
dbms_output.put_line('OUTPUT FOR ' || v_comptype_str ||' FOR TABLE ' || v_array(i)) ;
dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end LOOP;
end LOOP;
end;
/
;
Friday, October 12, 2012
Cannot open database due to corrupt file
If you cannot open database due to some non-critical datafile being corrupt or not found and you do not have the backup either, you can offline the corrupt file and restart the database ....
Starting the database gave the following error:
SQL> SQL> ORACLE instance started.
Total System Global Area 1.4008E+11 bytes
Fixed Size 2166320 bytes
Variable Size 5.3687E+10 bytes
Database Buffers 8.5899E+10 bytes
Redo Buffers 489947136 bytes
Database mounted.
ORA-01122: database file 166 failed verification check
ORA-01110: data file 166: '/test1/sf1000/scratch_ts'
ORA-01210: data file header is media corrupt
Login to the database and offline that particular datafile
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 14:38:51 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> alter database datafile '/test1/sf1000/scratch_ts' offline for drop
2 ;
Database altered.
SQL> alter database open;
Database altered.
Starting the database gave the following error:
SQL> SQL> ORACLE instance started.
Total System Global Area 1.4008E+11 bytes
Fixed Size 2166320 bytes
Variable Size 5.3687E+10 bytes
Database Buffers 8.5899E+10 bytes
Redo Buffers 489947136 bytes
Database mounted.
ORA-01122: database file 166 failed verification check
ORA-01110: data file 166: '/test1/sf1000/scratch_ts'
ORA-01210: data file header is media corrupt
Login to the database and offline that particular datafile
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 14:38:51 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> alter database datafile '/test1/sf1000/scratch_ts' offline for drop
2 ;
Database altered.
SQL> alter database open;
Database altered.
Tuesday, September 25, 2012
iSCSIADM: Cannot see iSCSI luns on server side
On the storage side , the LUNs are created. However you cannot see them on the server side. This post explains the steps needed to debug and fix the issue.
Issue list command to see if you can see the LUNs on the server side:
Issue list command to see if you can see the LUNs on the server side:
- iscsiadm list target -vS
If there are no LUNs listed, verify which discovery method is enabled
root@dat01:/# iscsiadm list discovery
Discovery:
Static: disabled
Send Targets: enabled
iSNS: disabled
Verify which address is enabled in send targets discovery
iscsiadm list discovery-address
Discovery Address: 10.129.195.209:3260
If the discovery-address is correct and you you cannot still see the luns, do the following:
Check /var/adm/messages for any error message
Restart the iscsi service on storage side
Readd the address to send targets discovery as under:
iscsiadm remove discovery-address 10.129.195.209
root@dat01:/var/adm# iscsiadm list discovery-address
root@dat01:/var/adm# iscsiadm add discovery-address 10.129.195.209
root@dat01:/var/adm# iscsiadm list discovery-address
Discovery Address: 10.129.195.209:3260
Monday, August 13, 2012
Installing Oracle Database on Solaris 11 : Issue resoution
While installing the Oracle 11gR2 (11.2.0.1) grid infrastructure software on Solaris 11 , I got the following errors:
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-10_06-23-31PM. Please wait ...oracle@len01:~/download/grid$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2011-11-10_06-23-31PM.log
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparcv9/server/libjsig.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/client/libjsig.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparcv9/libjdgaSUNWafb.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparcv9/server/libjsig_g.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/libjvm.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/libjdgaSUNWafb.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/javaws/javaws: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/server/libjsig_g.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/client/libjsig_g.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/server/libjsig.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/lib/libocci.so: No such file or directory
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/bin/lbuilder: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/lib/libagtsh.so: No such file or directory
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/lib/libodm11.so: No such file or directory
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/lib/libodm11.so: No such file or directory
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/JRE: No such file or directory
In the error message where it says invalid argument, the installer copies the file instead of creating a link. And for the error message that states No such file or directory, the corresponding file does not get copied over.
And then the ASM install fails with the error : ORACLE not available.
I got the similar message for Oracle database install as well.
It turns out 11.2.0.1 is not supported on Solaris11. 11.2.0.3 and beyond are supported on S11. Installing 11.2.0.3 resolved the issue.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-10_06-23-31PM. Please wait ...oracle@len01:~/download/grid$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2011-11-10_06-23-31PM.log
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparcv9/server/libjsig.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/client/libjsig.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparcv9/libjdgaSUNWafb.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparcv9/server/libjsig_g.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/libjvm.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/libjdgaSUNWafb.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/javaws/javaws: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/server/libjsig_g.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/client/libjsig_g.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/jdk/jre/lib/sparc/server/libjsig.so: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/lib/libocci.so: No such file or directory
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/bin/lbuilder: Invalid argument
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/lib/libagtsh.so: No such file or directory
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/lib/libodm11.so: No such file or directory
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/lib/libodm11.so: No such file or directory
Error in checking existance of the link, errno:11:/u01/app/oracle/product/11.2.0/grid/JRE: No such file or directory
In the error message where it says invalid argument, the installer copies the file instead of creating a link. And for the error message that states No such file or directory, the corresponding file does not get copied over.
And then the ASM install fails with the error : ORACLE not available.
I got the similar message for Oracle database install as well.
It turns out 11.2.0.1 is not supported on Solaris11. 11.2.0.3 and beyond are supported on S11. Installing 11.2.0.3 resolved the issue.
Installing Oracle database in Solaris Container: Issue resolution
I was trying to install Oracle clusterware on 11.2.0.2 on Solaris container. I got the following error:
Adding daemon to inittab
ACFS-9459: ADVM/ACFS is not supported on this OS version: 'Solaris Container: Solaris 10 update 10'
ACFS-9201: Not Supported
ACFS-9459: ADVM/ACFS is not supported on this OS version: 'Solaris Container: Solaris 10 update 10'
CRS-2672: Attempting to start 'ora.mdnsd' on 'dszone1'
CRS-2676: Start of 'ora.mdnsd' on 'dszone1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'dszone1'
CRS-2676: Start of 'ora.gpnpd' on 'dszone1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'dszone1'
CRS-2672: Attempting to start 'ora.gipcd' on 'dszone1'
CRS-2676: Start of 'ora.cssdmonitor' on 'dszone1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'dszone1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'dszone1'
CRS-2672: Attempting to start 'ora.diskmon' on 'dszone1'
CRS-2676: Start of 'ora.diskmon' on 'dszone1' succeeded
CRS-2674: Start of 'ora.cssd' on 'dszone1' failed
CRS-2679: Attempting to clean 'ora.cssd' on 'dszone1'
CRS-2681: Clean of 'ora.cssd' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'dszone1'
CRS-2677: Stop of 'ora.diskmon' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dszone1'
CRS-2677: Stop of 'ora.gipcd' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'dszone1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dszone1'
CRS-2677: Stop of 'ora.gpnpd' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dszone1'
CRS-2677: Stop of 'ora.mdnsd' on 'dszone1' succeeded
CRS-5804: Communication error with agent process
CRS-4000: Command Start failed, or completed with errors.
The exlusive mode cluster start failed, see Clusterware alert log for more information
Initial cluster configuration failed. See /u01/app/11.2.0/grid/cfgtoollogs/crsconfig
The alert log under /u01/app/11.2.0/grid/log/<nodename> has the following error:
[cssd(8886)]CRS-1656:The CSS daemon is terminating due to a fatal error; Details at (:CSSSC00011:) in /u01/app/11.2.0/grid/
log/dszone1/cssd/ocssd.log
And the ocssd.log has following error:
2012-08-07 06:06:57.599: [ CSSD][1]clssscSetPrivEnv: Setting priority to 4
2012-08-07 06:06:57.607: [ CSSD][1]clssscSetPrivEnv: unable to set priority to 4
2012-08-07 06:06:57.607: [ CSSD][1]SLOS: cat=-2, opn=scls_set_priority_realtime, dep=1, loc=setsched
unable to escalate to real time
2012-08-07 06:06:57.607: [ CSSD][1](:CSSSC00011:)clssscExit: A fatal error occurred during initialization
Restarting the container with following privileges fixed the issue:
set limitpriv="default,proc_priocntl,proc_clock_highres,sys_time"
Refer to metalink note ID 1340694.1 for detailed information.
Adding daemon to inittab
ACFS-9459: ADVM/ACFS is not supported on this OS version: 'Solaris Container: Solaris 10 update 10'
ACFS-9201: Not Supported
ACFS-9459: ADVM/ACFS is not supported on this OS version: 'Solaris Container: Solaris 10 update 10'
CRS-2672: Attempting to start 'ora.mdnsd' on 'dszone1'
CRS-2676: Start of 'ora.mdnsd' on 'dszone1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'dszone1'
CRS-2676: Start of 'ora.gpnpd' on 'dszone1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'dszone1'
CRS-2672: Attempting to start 'ora.gipcd' on 'dszone1'
CRS-2676: Start of 'ora.cssdmonitor' on 'dszone1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'dszone1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'dszone1'
CRS-2672: Attempting to start 'ora.diskmon' on 'dszone1'
CRS-2676: Start of 'ora.diskmon' on 'dszone1' succeeded
CRS-2674: Start of 'ora.cssd' on 'dszone1' failed
CRS-2679: Attempting to clean 'ora.cssd' on 'dszone1'
CRS-2681: Clean of 'ora.cssd' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'dszone1'
CRS-2677: Stop of 'ora.diskmon' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dszone1'
CRS-2677: Stop of 'ora.gipcd' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'dszone1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dszone1'
CRS-2677: Stop of 'ora.gpnpd' on 'dszone1' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dszone1'
CRS-2677: Stop of 'ora.mdnsd' on 'dszone1' succeeded
CRS-5804: Communication error with agent process
CRS-4000: Command Start failed, or completed with errors.
The exlusive mode cluster start failed, see Clusterware alert log for more information
Initial cluster configuration failed. See /u01/app/11.2.0/grid/cfgtoollogs/crsconfig
The alert log under /u01/app/11.2.0/grid/log/<nodename> has the following error:
[cssd(8886)]CRS-1656:The CSS daemon is terminating due to a fatal error; Details at (:CSSSC00011:) in /u01/app/11.2.0/grid/
log/dszone1/cssd/ocssd.log
And the ocssd.log has following error:
2012-08-07 06:06:57.599: [ CSSD][1]clssscSetPrivEnv: Setting priority to 4
2012-08-07 06:06:57.607: [ CSSD][1]clssscSetPrivEnv: unable to set priority to 4
2012-08-07 06:06:57.607: [ CSSD][1]SLOS: cat=-2, opn=scls_set_priority_realtime, dep=1, loc=setsched
unable to escalate to real time
2012-08-07 06:06:57.607: [ CSSD][1](:CSSSC00011:)clssscExit: A fatal error occurred during initialization
Restarting the container with following privileges fixed the issue:
set limitpriv="default,proc_priocntl,proc_clock_highres,sys_time"
Refer to metalink note ID 1340694.1 for detailed information.
Oracle Database Listener Service Registration
- I do not have listener.ora file. How is service registration working:
Service registration can happen dynamically or statically. By default, listener uses dynamic service registration before attempting static registration. For dynamic configuration, listener.ora file is not needed.
Dynamic service registration is configured in the database initialization file. It does not require any configuration in the listener.ora file. For dynamic registration to work, you should set service_names and instance_names in init.ora. or it defaults to default value (INSTANCE_NAME is set to ORACLE_SID and service_names is set to global database name: DB_NAME +DB_DOMAIN). If your local listener is listening on a non-default port, you need to set LOCAL_LISTENER parameter and remote_listener ,typically used in RAC, needs to set with remote_listener parameter.
The static configuration reads information from listener.ora file to configure the services. An instance with status UNKNOWN is statically registered. For additional info check out the following:
http://www.oaktable.net/content/little-things-worth-knowing-static-and-dynamic-listener-registration
- How do I know which naming method is being used to connect:
Tnsping utility can be used to determine whether the listener for a service on an Oracle net network can be reached.
If you can ping the server host from client, use tnsping to test if client can reach Oracle net service. Tnsping also states the naming method which was used to connect.
Additional info:
http://edstevensdba.wordpress.com/2011/03/19/ora-12514/ ( look for all post under TNS category)
Setting swap on Solaris 11
- After the system is installed, swap areas and swap files are listed in /etc/vfstab file
- Create swap
Increase the swap size to 40G
root@dat02:~# zfs get volsize rpool/swap
NAME PROPERTY VALUE SOURCE
rpool/swap volsize 4G local
root@dat02:~# zfs set volsize=40G rpool/swap
root@dat02:~# zfs get volsize rpool/swap
NAME PROPERTY VALUE SOURCE
rpool/swap volsize 40G local
Has the system previously swapped
Check if kthr:w has non-zero value in vmstat output. Refer to following blog for additional info:
http://ritukamboj.blogspot.com/2012/03/vmstat-shows-high-value-in-w-column.html
Is the system currently swapping
Check out the sr field of vmstat output
What is the total swap available
swap field of vmstat output as well swap -s displays the value of available swap
root@etchst8:~# vmstat 2
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr s3 s1 s1 s1 in sy cs us sy id
0 0 0 169977852 94409120 1192 930 341 0 0 0 1064 6 2 0 2 2793 6433 3146 0 1 99
0 0 0 175557112 93864204 29 44 0 0 0 0 0 0 0 0 1 2268 1540 1531 0 0 100
0 0 0 175556024 93863128 0 4 0 0 0 0 0 0 0 0 0 2450 1411 1620 0 0 100
0 0 0 175554632 93861728 0 1 0 0 0 0 0 1 0 0 0 2382 1385 1581 0 0 100
^Croot@etchst8:~# swap -s -h
total: 552M allocated + 217M reserved = 768M used, 167G available
Additional info:
http://www.solarisinternals.com/wiki/index.php/ZFS_Troubleshooting_Guide#Resizing_ZFS_Swap_and_Dump_Devices
https://blogs.oracle.com/observatory/entry/zone_swap_space
http://www.softpanorama.org/Solaris/Processes_and_memory/swap_space_management.shtml
Subscribe to:
Posts (Atom)