Free & Used Space in Tablespace from SQL

Run this sql statement SELECT /* + RULE */  df.tablespace_name "Tablespace",        df.bytes / (1024 * 1024) "Size (MB)",        SUM(fs.bytes) / (1024 * 1024) "Free (MB)",        Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",        Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"   FROM dba_free_space fs,        (SELECT tablespace_name,SUM(bytes) bytes           FROM dba_data_files          GROUP BY tablespace_name) df  WHERE fs.tablespace_name (+)  = df.tablespace_name  GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace,        fs.bytes / (1024 * 1024),        SUM(df.bytes_free) / (1024 * 1024),        Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),        Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)   FROM dba_temp_files fs,        (SELECT tablespace_name,bytes_free,bytes_used           FROM v$temp_space_header          GROUP BY tablespace_name,bytes_free,bytes_used) df  WHERE fs.tablespace_name (+)  = df.tablespace_name  GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used  ORDER BY 4 DESC;

Number to text conversion from SQL

--- conversion of salary into words with decimal values-----

select sal,
decode(floor(sal),
0,'ZERO',
to_char(to_date(floor(sal),'J'),'JSP')) dollars,
decode(sal - floor(sal),
0,'ZERO',
to_char(to_date(((sal - floor(sal)) * 100),'J'),'JSP')) cents
from emp

--- conversion of salary into words without decimal values-----


select ename,sal,to_char(to_date(sal,'j'),'jsp')from emp;

NTP Failed with Oracle 11gR2 RAC on Linux

NTP configuration issues with Oracle 11gR2 RAC setup During the Grid infrastructure setup for Oracle 11gR2 RAC, I ran into a weird error with ntp not working with the Grid infrastructure installer. edit ntp configuration on both cluster nodes and restart ntp services. To do so, logon to each node as root and edit  the /etc/sysconfig/ntpd configuration file to make change for options settings as shown below: [root@vxnode2 ntp]# service ntpd stop [root@vxnode2 ntp]# cat /etc/sysconfig/ntpd # Drop root to id 'ntp:ntp' by default. OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid" # Set to 'yes' to sync hw clock after successful ntpdate SYNC_HWCLOCK=no # Additional options for ntpdate NTPDATE_OPTIONS="" now add -x before -u  see below [root@vxnode2 ntp]# vi /etc/sysconfig/ntpd OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid" [root@vxnode2 ntp]# cat /etc/sysconfig/ntpd # Drop root to id 'ntp:ntp' by default. OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid" # Set to 'yes' to sync hw clock after successful ntpdate SYNC_HWCLOCK=no # Additional options for ntpdate NTPDATE_OPTIONS="" You will need to restart ntp services for this to take affect: root@vxnode2 ntp]# service ntpd stop Shutting down ntpd: [ OK ] [root@vxnode2 ntp]# service ntpd start ntpd: Synchronizing with time server: [ OK ] Starting ntpd: [ OK ] [root@vxnode2 ntp]# ps -ef|grep ntp root 7363 1 0 03:51 ? 00:00:00 ntpd -x -u ntp:ntp -p /var/run/ntpd.pid Now we can move forward in the 11gR2 RAC Grid Infrastructure setup.

Solution for ORA-16191 on Primary Database with Dataguard

Changing SYS password of PRIMARY database when STANDBY in place to avoid ORA-16191 Refrence DOC ID 806703.1 Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.1.0.7 - Release: 9.2 to 11.1 Information in this document applies to any platform. Goal What are the steps to change the SYS password in PRIMARY when STANDBY database in place to avoid ORA-16191 -Primary log shipping client not logged on standby Solution INSTRUCTIONS Its advisable to follow the steps below when you are planning to change the SYS password in PRIMARY Step 1: Defer the remote archival destination (log_archive_dest_state_n) parameter in primary. In case of RAC defer LOG_ARCHIVE_DEST_STATE_n in all nodes. Step 2: change the password in primary Step 3: Recreate or copy the password file in standby. In case of RAC shutdown all instance of standby before recreating the password file. Step 4: Enable the remote archival destination (log_archive_dest_state_n) parameter in primary If the password is already changed, and you are getting ORA-16191 during log shipping then follow the below action. Action: Copy the password file from PRIMARY to STANDBY (OR) recreate the password file in standby with the same password. For 9i database By default the redo is shipped via SYS user, Its not mandatory to have the SYS password to be identical in PRIMARY and STANDBY for 9i dataguard configuration. For 10g database a) Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed. b) REMOTE_LOGIN_PASSWORDFILE parameter setting should be either EXCLUSIVE or SHARED. For 11g database With 11.0, we are using strong password authentication by default, and so the password file has to be identical at primary and physical, so it needs to be copied - i.e orapwd using the same password for SYS does not work anymore, unless you choose to bypass the strong authentication framework. Turn off case sensitivity by changing the init.ora parameter SEC_CASE_SENSITIVE_LOGON=FALSE Create password files on both servers using the same password, and pass "ignorecase=Y" to orapwd utility. Also, starting from 11g The REDO_TRANSPORT_USER database initialization parameter can be used to select a different user password for redo transport authentication by setting this parameter to the name of any user who has been granted the SYSOPER privilege. The REDO_TRANSPORT_USER parameter be set to the same value on the redo source database and at each redo transport destination.

Calculate Dataguard required Bandwidth

The formula to calculate Bandwidth Let us assume the redo rate is a 500 KB/sec.(500*1024=512000) Required bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000 bandwidth in Mbps Required bandwidth = ((512000/0.7) * 8) /1,000,000 Required bandwidth = 4.85 Mbps In CASE WE HAVE RAC DATABASE then Required bandwidth = 4.85 Mbps * no of RAC nodes

Memory Structure of Oracle PPT

GSD Is Used Only if 9i RAC Database is Present

GSD Is Used Only if 9i RAC Database is Present [ID 429966.1] Applies to: Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.0 Information in this document applies to any platform. Purpose The purpose of this document is to clarify the functionality of GSD in 10g and above. Scope This article is informational and intended for any user. GSD Is Used Only if 9i RAC Database is Present The function of GSD (10g and above) is to service requests for 9i RAC management clients and therefore when there are no 9i databases present, there is nothing for GSD to do. Consequently, there will be no impact on a RAC cluster if GSD is offline and 9i is not used. If gsd fails to start due to whetever reasons then best thing is to work with Oracle support to analyze and fix the issue. Until that time, gsd can be temporarily disabled. In 11.2 GSD is disabled by default and the service will show as target:offline, status:offline. Disable GSD (pre 11.2) After confirming that there are no 9i databases being used you can disable GSD by adding 'exit 0' after the initial comments in the script $ORACLE_HOME/bin/gsdctl where $ORACLE_HOME is the home from which nodeapps are running (i.e. crs home). #case $ORACLE_HOME in  # "") echo "****ORACLE_HOME environment variable not set!"  # echo " ORACLE_HOME should be set to the main"  # echo " directory that contains Oracle products."  # echo " Set and export ORACLE_HOME, then re-run."  # exit 1;;  #esac  exit 0 ## Manually added as a temporary workaround  A backup of the original script should be made before making the above change. Disable GSD (11.2) You may want to disable GSD after you upgraded all your Oracle9i RAC databases. srvctl stop nodeapps srvctl disable nodeapps -g srvctl start nodeapps Enable GSD in 11.2 srvctl enable nodeapps -g srvctl start nodeapps