Wednesday, May 20, 2009

How many Archive files got generated today?

To find how many archive log files that got generated everyday on hourly basis, Here is the script:

/* archlogs.sql
find the number of archivelogs each hour of the day
*/
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1

The output would look like this,
LOGDATE MidN 1AM 2AM 3AM 4AM 5AM 6AM 7AM 8AM 9AM 10AM 11AM Noon 1PM 2PM 3PM 4PM 5PM 6PM 7PM 8PM 9PM 10PM 11PM
05/07/09 0 0 0 0 0 0 0 0 0 0 1 4 1 1 2 2 40 2 1 2 1 2 1 2
05/08/09 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 1 2 1 1 1 2 1 1 1
05/09/09 2 1 1 1 2 1 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1
05/10/09 1 1 2 1 1 1 2 1 1 2 2 1 2 1 1 1 2 1 1 1 2 1 1 1
05/11/09 1 2 1 1 1 2 1 1 1 2 1 1 2 1 1 2 1 1 1 2 1 1 1 2
05/12/09 1 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1
05/13/09 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 1 2 1
05/14/09 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 1 2
05/15/09 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 1 2 1 1 1
05/16/09 2 1 1 1 2 1 1 1 2 1 1 1 1 2 1 1 1 2 1 1 1 2 1 1
05/17/09 1 2 1 1 1 2 1 1 1 2 2 1 3 2 1 1 2 1 1 1 2 1 1 1
05/18/09 1 2 1 1 1 2 1 1 1 2 1 2 2 1 2 1 1 1 2 1 1 1 2 1
05/19/09 1 1 1 2 1 1 1 2 1 1 1 2 1 2 1 2 1 1 1 2 1 1 1 1
05/20/09 2 1 1 1 2 1 1 1 2 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Sunday, May 10, 2009

Installing Apps 11i on a Laptop for practicing

Hardware used.

--------------------------------------------------------------------------------
Windows XP professional SP2 laptop with 1 GB RAM
External hard drive with 250 GB space
-- DO NOT USE FAT32 FILE SYSTEM. USE ONLY NTFS.
-- installation files downloaded from edelivery.oracle.com is 25 GB.
-- staging area needs 19.7 GB.
-- base installation with Vision database needs about 110 GB.

Pre-requisite software requirements.
--------------------------------------------------------------------------------
1. Microsoft VC++ - http://msdn.microsoft.com/vstudio/express/visualc/download/
2. CYGWIN - http://cygwin.com/

NOTE - There is no need for perl or to run the staging script adautostg.pl. You can just use winzip to unzip the files, there is no difference.

Software installation files downloaded from edelivery.oracle.com.
--------------------------------------------------------------------------------
NOTE - There is no need to download all the files from edelivery.oracle.com. Just download the following files, rest are optional. Also, the forms, reports, discoverer, etc. tools are included in the following files (although there are separate files in the media pack set on edelivery.oracle.com).

1. Oracle® Applications 11i Release 11.5.10.2 Rapid Install Start Here - Disk 1
2. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install RDBMS - Disk 1
3. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install Databases - Disk 1
4. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install Databases - Disk 2
5. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install Databases - Disk 3
6. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install Tools - Disk 1
7. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install APPL_TOP - Disk 1

Installation steps.
--------------------------------------------------------------------------------
1. Add the following to your c:\windows\system32\drivers\etc\hosts file. You can change domainname.com to any name that you prefer. This should be used as the domain while installing Oracle Applications.

127.0.0.1 localhost
127.0.0.1 localhost.domainname.com

2. Go to Control Panel -> Systems -> Computer Name Tab -> Click on Change -> Click on More -> Enter Primary DNS Suffix as 'domainname.com'.

3. Go to your network connection, right click and select properties. Select Internet Connection TCP/IP (last in the list) and click properties. Click on Advanced button and go to DNS tab. Enter DNS suffix for this connection as
'domainname.com'.

4. Restart the PC.

5. Install Microsoft VC++
-- Remember to change the installation folder to C:\MSVC or something similar so that it does not have a space in the folder name. By default it takes Program Files, which will/may not work.

6. Restart the PC.

7. Download CYGWIN setup file and run it.
-- Install it in C:\cgwin folder, with no spaces in the folder name.
-- There are some important steps while installing CYGWIN detailed in metalink note 302859.1. If you do not have access to metalink, refer this link.
http://forums.oracle.com/forums/thread.jspa?threadID=435071

8. Restart the PC.

9. Unzip the files downloaded from edelivery.oracle.com to the folder E:\Stage11i, where E is the drive where you have sufficient space (about 120 GB for Vision sample DB and about 60GB -- not sure -- for without data) and Stage11i is a folder that you have to create.

10. Check that Stage11i has the following sub directories.
oraAppDB, oraApps, oraDB, oraiAS, startCD. The total space occupied by Stage11i folder is about 19.7 GB. This is when you do not unzip any optional files (see details above in the s/w installation files section).

11. Start rapidwiz by double clicking the file E:\Stage11i\startCD\Disk1\rapidwiz\RapidWiz.cmd.

12. Follow the wizard, remember to put the path for MKS Toolkit as C:\cygwin\bin and not just C:\cygwin. Select the path for VC++ as C:\MSVC (or where ever you installed it - remember NO SPACES in directory name). Select domain name as what you gave in your hosts file (domainname.com).

13. Refer the Oracle Apps installation guide for details on the wizard.

14. The installation (rapidwiz) takes about three hours.

15. Good luck.