Attunity Oracle-CDC for SSIS

SQL 2008 Enterprise Edtion has a nice feature "change data capture"(CDC) which would reduce a lot of efforts when dealing with SCD2. However, your SQL 2005 may have to stay in production for a long while or you may not have 2008 Enterprise Edition, and you need to extract data from Oracle database. Here is a good news for you.

Attunity provide a simple solution for replicating and integrating Oracle data efficiently and in real-time using SSIS.

The following information is from Attunity:
"Attunity Oracle-CDC for SSIS addresses the key challenges that data integration teams face in addressing business and IT requirements. Using it, you can:

• Simplify and accelerate CDC implementation
• Deliver real-time information
• Design efficient data integration solutions
• Leverage existing SQL Server investments
• Reduce resources and costs

"

More info can be found in its website http://www.attunity.com/oracle_cdc_for_ssis


Although the tool is not free, I would like to try its trail version whenever I have an opportunity.

unable to update McAfee VirusScan Enterprise 8.5.0i

One day in the early April, I found my McAfee could not do an update.
Error message said McAfee Framework Service wasn't running. Actually it disappeared from Services list.

McAfee was reinstalled successfully with Framework Service, however it still was unable to connect the server to do an update.

After I compared a working copy of McAfee on another PC, I found out SiteMapList.xml didn't exist on my Windows XP - C:\Documents and Settings\All Users\Application Data\McAfee\Common Framework\.

My Vista had the same issue as well. The location is C:\ProgramData\McAfee\Common Framework

After SiteMapList.xml was copied over, it has been working well since then.

Issues to access SQL Server in Oracle SQL Developer

1. Failed when tried to connect to a SQL server.

Error Message: Status: Failure -I/O Error: SSo Failed: Native SSPI library not loaded. Check the java.library.path system property.

Fix: Copy \x86\SSO\ntlmauth.dll to \jdk\jre\bin

2. How to connect a named SQL server instance.

In SQLServer tab:

Hostname:
Port: /;instance=

Ex: 1433/EDW_SA;instance=edw_dev

Seems it(1.5.4) doesn't support schema.

SQL 2005 installation failure on XP SP3

SQL 2005 and management studio tool cannot be installed on XP with SP3 applied.

Problem: MSXML 6.0 sp2 backward compatibility issue.

Error: Product: MSXML 6 Service Pack 2 (KB954459) -- Configuration failed.

Workaround:

1. uninstall msxml 6.0 sp2. If cannot, remove its entry in Registry.
2. Before removing it from Registry, do a backup first.
3. SQL 2005 should be able to be installed successfully.
4. patch SQL to the latest SP.
5. install MSXML 6.0 SP2.

Also refer to the following post:
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/97c58e05-c61b-4f29-8507-f83f437c6f1d/

TDWI Search Plugin for Mozilla Firefox


<[[CDATA

]]>
Install TDWI Search Plugin to search the TDWI.org site.

"KILLED/ROLLBACK" SQL Server Process Never Ends

You may see a killed SQL server process marked as "KILLED/ROLLBACK", but did not disappear from SQL Sevrer forever. Seems lots of people by doing restart the server or SQL service to clean up this issue. If your case is similar to mine, you may not need to do that again.

My Case:

A job was calling an external program, ex. winzip. It could not be finished as winzip registration file was missing.

After killing SQL process by issuing kill Command.

The process was marked as "KILLED/ROLLBACK", however it could not exit after a day.

Check the status: kill 463 with statusonly

SPID 463: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

My Workaround:

In this case, I looked at the process in Windows level, I saw a winzip.exe was still running. Of course first I isolated no winzip was using. I killed winzip OS process, the hung SQL process exited from SQL Server automatically.

My Environment:

SQL Server 2000 SE SP4
Windows Server 2003 R2

DB document tool - SqlSpec

SqlSpec is from Elsasoft. It works well for SQL Server, better than SQL Doc, SQL Document Tool according to my evaluation.

However, you may not see information you are interested in Oracle, but still good in the schema level.

Here is the link. You can download an evaluation version for your trial.

just $149 for a single user, I think it is pretty worth.

http://www.elsasoft.org/

Examples of Connection String:

For SQL Server

server="serverName\instanceName"; database=dbName; user id=userName; pwd=yourPassword

For Oracle

Provider=OraOLEDB.Oracle;Data Source=tnsName;User Id=userName;Password=yourPassword

nmefwmi.exe issue in Oracle 11g on Vista

A window keeps pop-up saying "nmefwmi.exe has stopped working".

It is a known bug in Oracle, should be fixed in 11.1.0.7 release according to metalink.

Suggested Workaround prior to 11.1.0.7:

Check Compatibility in properties of setup.exe to "Run this program as a administrator".

Get User-defined Database Objects in MS SQL

I was trying to get all user-defined objects in SQL Server.
And tried to make it compatible with 2000,2005 and 2008.

Seems it wasn't easy as in Oracle, especially for stored procedures and functions. If you know a way to distinguish user-defined procedure and system procedure, please share.

select count(*) as 'UserTables'
from sysobjects
where type in ('U')

select count(*) as 'UserIndexes'
from sysindexes i
INNER JOIN sysobjects AS o ON i.id=o.id
where o.type='U'

select count(*) as 'UserTriggers'
from sysobjects o1
INNER JOIN sysobjects AS o2 ON o1.parent_obj=o2.id
where o1.type in ('TR', 'TA')
AND o2.TYPE='U'

select count(*) as 'UserViews'
from sysobjects
where type in ('V')
and name not like 'sys%'

select count(*) as 'StoredProcedures'
from sysobjects
where type in ('P','PC','X')

select count(*) as 'Functions'
from sysobjects
where type in ('FN','FS','FT','TF','AF','IF')


Reference: sysobjects.type

Common Object Types in SQL 2000, 2005 and 2008

C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
IF = SQL inline table-valued function
P = SQL stored procedure
PK = PRIMARY KEY constraint
S = System base table
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

Common Object Types in SQL 2005 and 2008

AF = Aggregate function (CLR)
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
IT = Internal table
PC = Assembly (CLR) stored procedure
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger

New Object Types in SQL 2008

PG = Plan guide

SQL Server 2000 Back Up falied to shared file system

Invalid Path when specifying BackUp location to a shared file system in SQL Server 2000.

Two things may need to be checked:

1. If SQL Server and Agent services are running under a domain service account.

I encountered this situation. After changing the services from local system to the designated domain account. The backup was successful.

2. Verify if domain account has the right permissions on the shared folder.

How Data Got Moved and Purged in MOM 2005 Database

1. Transfer data from OnePoint to SystemCenterReporting DB

1.1 Scheduled Task: SystemCenterDTSPackageTask
Schedule: 1am everyday

Check the task if successfully by looking at the field LastResult in Control Panel->Scheduled Tasks.

0x0 - successed
0xffff - failed

When failed, you can check Application Log in Control Panel->Administration Tools->Event Viewer.

If it failed with Expired Timeout, try to increase query timeout in MOM SQL Server to aovid this error.

1.2 Check the last completely run of DTS job in OnePoint database

select TimeDTSLastRan from OnePoint.dbo.reportingsettings

1.3 Check DataRetentionDays in OnePoint DB and see how many days data requires to be moved.

select * from OnePoint.dbo.groomingsettings

1.4 May need to add /latency to DTS job to speed up the large data moving if it has been failed a few days.

ex: DataRetentionDays =3 and TimeDTSLastRan= 3 days ago,
could set /latency:3

C:\Program Files\Microsoft System Center Reporting\Reporting\MOM.Datawarehousing.DTSPackageGenerator.exe" /silent /latency:3 /srcserver:mom_server_name /srcdb:OnePoint /dwserver:mom_dw_server_name /dwdb:SystemCenterReporting /product:"Microsoft Operations Manager

2. Purge the transferred data in OnePoint DB.

Run SQL Job "MOMX Partitioning And Grooming" to purge the transferred data in OnePoint DB.


3. Run SCDWGroomJob to purge Reporting DB.

3.1 Check Reporting Grooming Day

USE SystemCenterReporting
GO

SELECT CS.TableName AS TableName, WCS.GroomDays AS GroomDays
FROM SMC_Meta_WarehouseClassSchema WCS
JOIN SMC_Meta_ClassSchemas CS
ON CS.ClassID = WCS.ClassID
WHERE WCS.MustBeGroomed = 1
ORDER BY WCS.GroomDays DESC


3.2. Set Reporting Grooming day
--------------------------------------------
USE SystemCenterReporting
GO
Declare @Groomdays int
-- Retain data for 145 days
Select @Groomdays = 145
exec p_updateGroomDays 'SC_AlertFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertHistoryFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertToEventFact_Table', @Groomdays
exec p_updateGroomDays 'SC_EventFact_Table', @Groomdays
exec p_updateGroomDays 'SC_EventParameterFact_Table', @Groomdays
exec p_updateGroomDays 'SC_SampledNumericDataFact_Table', @Groomdays

3.3 Start the reporting grooming job (called SCDWGroomJob) in the SQL Agent jobs.

Or run the actual stored procedure:

EXECUTE dbo.p_GroomDatawarehouseTables

If reporting grooming job has been failed for a while, you may have to set Grooming Day to trim the data gradually out of the reporting database.

Usually this job is time-consuming, watch it out after resetting the grooming day in case of the job failure.

unexpected log grow in Simple Recovery Mode

Log file was growing unexpectedly in Simple Recovery Mode during huge data purging process.
It was strange. My SQL Server is 2000 SP4.

Solution:
1. Check Trans Log file
The log was keeping growing.

2. Check Active Transaction
USE [yourDB]
GO
DBCC OPENTRAN
GO

3. Manually reset DB to Simple Recovery Mode
SP_DBOPTION [yourDB]
SP_DBOPTION [yourDB], 'trunc. log on chkpt.', 'true'
SP_DBOPTION [yourDB]

4. Shrink Trans log to the target size.
USE [yourDB]
GO
DBCC SHRINKFILE ('yourDBLog' , TargetSize)
GO

5. Monitor Trans Log growth.
Inactive transaction log entries should be cleared out at every checkpoint.

Should leave the log to a certain size, does NOT make sense to shrink it again unless an unusual over-volume transactions happen.

Harden Oracle and SQL Server - excelleent documents

http://www.cisecurity.org/

http://csrc.nist.gov/checklists/repository/category.html

Fix: "Difference of two datetime columns caused overflow at runtime" in Performance Dashboard.

Error: Difference of two datetime columns caused overflow at runtime
Solution: to modify msdb.MS_PerfDashboard.usp_Main_GetSessionInfo

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to:

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

Fix login and user issues after moving SQL Server database

Unbelievably, this happens all the time after I restored the databases on the different server,

even I used the suggestion from http://support.microsoft.com/kb/246133.

I think I'd better put somewhere for the late on usage. Here it is.

1. Check the properties of login sa.
If find your database is not mapped to sa, run the following command in Query Window.

use [your DB Name]
go
exec sp_changedbowner 'sa'
go

2. Check other users under your database.
Run the following command, you'll see the users need to be fix.

use [your DB Name]
go
exec sp_change_users_login 'Report'
go

3. Fix users listed above.

use [your DB Name]
go
exec sp_change_users_login 'Auto_Fix','yourDBUser'
go

Oracle bids $6.66B for BEA Systems

 

Oracle is opening its mouth again.

 

Check this Oracle bids $6.66B for BEA Systems  in Star web site.

 

Who is going to be the next?

Identify your SQL Server version and SP

--
--For SQL 6.5 or later
--
select @@version
go
sp_server_info
go

--
--For SQL 2000 or later
--
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
go

EXP-00056: ORACLE error 904 encountered

Oracle Version: 9.2.0.8.0

Error Messsage when tried to export a schema:

EXP-00056: ORACLE error 904 encountered
ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier

Explanation from Metalink Note:358508.1:

After the patchset installation, the data dictionary components have not been upgraded to the patchset release (e.g. 10.1.0.4).

A patchset (e.g. 10.1.0.4) was applied to an existing ORACLE_HOME install but one or more of thepatchset steps were not performed per the patchset readme to bring the database components up to the same version as the patched software as shown in DBA_REGISTRY.

If the database components versions do not match the software and the running instance then the exp utility fails as it finds an earlier version of the exp packages in the database.

Check:
From the following I can see three components are still have the old version.
select COMP_ID,COMP_NAME,VERSION,STATUS,SCHEMA,PROCEDURE from DBA_REGISTRY
COMP_ID COMP_NAME VERSION STATUS SCHEMA PROCEDURE
-------- ------------------------------ ---------- ----------- -------- -----------------------------------
CATALOG Oracle9i Catalog Views 9.2.0.6.0 VALID SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG

CATPROC Oracle9i Packages and Types 9.2.0.6.0 VALID SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC

OWM Oracle Workspace Manager 9.2.0.1.0 VALID WMSYS OWM_VALIDATE

3 rows selected.

Suggested Solution:
Connect with SYS user with SYSDBA privileges
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
SQL> SHUTDOWN
SQL> STARTUP

10g Recycle Bin

Oracle 10g Recycle Bin is a virtual container to hold dropped objects until owner or DBA purges them out or when no more free space left in its tablespace which needs to save more data or a new object.

Default recyclebin is ON. After a table or index is dropped, you will see a new table or index with a long name like BIN$% under current schema. As some systems created and dropped tables very often "by design", in 10g this may bother some users to see many BIN$% tables which are not able to be dropped by using "drop table" command".

The following are something need to know about it:

1. Check recycle bin

SQL> show recyclebin;
SQL> select * from recyclebin;
SQL> select * from user_recyclebin;
SQL> select * from dba_recyclebin; --with DBA privilege

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
SEQ_TELE BIN$NEZqOOkqZdrgRAADung28g==$0 TABLE 2007-07-02:06:37:26
TORCMPLIST BIN$NH0oo3EvagvgRAADung28g==$0 TABLE 2007-07-04:23:56:05
TORCMPLIST BIN$NGnVEBsMIJ/gRAADung28g==$0 TABLE 2007-07-04:00:52:42
TORCMPLIST BIN$NFlS+mOZHurgRAADung28g==$0 TABLE 2007-07-03:05:11:00

you can see multiple versions of dropped table. Cool!

2. Check the content in a dropped table.

Dropped objects still occupy the space in the database until manually purged or automatically removed by the database itself.

SQL> select * from "BIN$NFlS+mOZHurgRAADung28g==$0"

3. Flashback dropped table from recycle bin

This gives you a chance to bring back the dropped table.

SQL> FLASHBACK TABLE SEQ_TELE TO BEFORE DROP RENAME TO SEQ_TELE_old;

--Flashback dropped table when it has mltiple versions
SQL> FLASHBACK TABLE "BIN$NFlS+mOZHurgRAADung28g==$0" TO BEFORE DROP RENAME TO TORCMPLIST_20070703051100;

4. Purge recycle bin

In order to avoid losing data by accident, by checking drop time of dropped objects, they can permanently and safely be removed from the database.

SQL> PURGE TABLE tablename;                   -- Remove specific table from recycle bin.
SQL> PURGE INDEX indexname;                   -- Remove specific index from recycle bin.
SQL> PURGE TABLESPACE ts_name;                -- Empty recycle bin for a specific tablespace under current user.
SQL> PURGE TABLESPACE ts_name USER username;  -- Empty recycle bin for a specific user under a specific tablespace.
SQL> PURGE RECYCLEBIN;                        -- Empty recycle bin for current user.
SQL> PURGE DBA_RECYCLEBIN;                    -- Empty the entire recycle bin in the database by DBA

5. Disable recycle bin feature

It is NOT recommneded as you lose the chance to easily restore the accidentally dropped table back. Think about how difficultly to do this prior to 10g.

SQL> alter seesion set recyclebin=off;
SQL> alter system set recyclebin=off;

[MySQL]Step by Step to Set up MySQL v4.x Replication

==
== Step by Step to Set up MySQL Replication on FC4
==
== MySQL Version: 4.1.11
== Fedora Core 4
==
== master: mysql1
== slave : mysql2
==
== It is a good practice, even with these old releases.
==
==============================================
== Configure Master Node
===============================================
--
--1. Update my.cnf
--
vi /etc/my.cnf

[mysqld]
log-bin ==> trun on binary log for replication
server-id=1 ==> unique against all slaves
binlog-do-db = mysql ==> database needs to generate binary log
binlog-do-db = test ==> reapet this for more databases

[mysqld_safe]
err-log=/var/log/mysqld.log ==> MySQL log file
pid-file=/var/run/mysqld/mysqld.pid ==> to save MySQL pid

--
--2. Restart master mysql
--
$ ps -efgrep mysqld
$ KILL -9 pid
$ /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid &

or
$ /etc/init.d/mysqld restart

--check if the log file is generated under datadir directory
$ ls -ltr datadir
or
mysql> show master logs;

--
--3. Create replcation account, which is used to access master from slave.
--
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'mysql2.domain' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> select * from mysql.user where user='repl'\G;

--
--4. Verfiy access in slave node
--
$ mysql -u repl -p -h mysql1

--
--5. Take database snapshot on master node
--
-- Closes all open tables and locks all tables for all databases with a read lock.
mysql> FLUSH TABLES WITH READ LOCK;

mysql> show master status;
+-------------------+----------+-------------------+------------------+
File Position Binlog_Do_DB Binlog_Ignore_DB
+-------------------+----------+-------------------+------------------+
mysql1-bin.000010 79 mysql,test
+-------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)

--
--tar database files for each database
--
$ cd datadir/mysql
$ tar -cvf /tmp/mysql-snapshot.tar .
$ cd datadir/test
$ tar -cvf /tmp/test-snapshot.tar .

--re-enable write activities
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


===========================================
== Configure Slave - mysql2
===========================================
--
--1. restore BACKUP SNAPSHOT to datadir
--
$ cd datadir/mysql
$ tar -xvf mysql-snapshot.tar
$ cd datadir/test
$ tar -xvf test-snapshot.tar

--
--2. Verfiy master database access from slave node
-- make sure access is successful before continuing
$ mysql -u repl -p -h mysql1

--Check mysql log if failed
$ tail -f /var/log/mysqld.log

--
--3. Configure my.cnf for replication
--
vi /etc/my.cnf

[mysqld]
server-id=2 ==> unique among master and slaves
replicate-do-db = mysql ==> database needs to be replicated
replicate-do-db = test ==>

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Note:
without setting replicate-do-db, I got the following error:
070703 15:26:29 [ERROR] Slave I/O thread: error connecting to master 'repl@mysql1:3306': Error: 'Can't connect to MySQL server on 'mysql1' (13)' errno: 2003 retry-time: 60 retries: 86400

--
--4. Start slave mysql
--
/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid &
or
$ /etc/init.d/mysqld start

--
--5. Set up master info for replication
--
$ mysql -u root -p mysql

--set master
--log file and position from above result of "show master status;"
--
CHANGE MASTER TO
MASTER_HOST='mysql1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql1-bin.000010',
MASTER_LOG_POS=79;

--
--6. start slave I/O and SQL Thread
-- stop slave
mysql> START SLAVE;

--
--7. Verify slave is running
--
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql1-bin.000013 ==> read by I/O Thread
Read_Master_Log_Pos: 139
Relay_Log_File: mysql2-relay-bin.000001 ==> read by SQL Thread
Relay_Log_Pos: 1054
Relay_Master_Log_File: mysql1-bin.000013
Slave_IO_Running: Yes ==> make sure I/O thread is running
Slave_SQL_Running: Yes ==> make sure SQL thread is running
Replicate_Do_DB: mysql,test ==> replicated databases
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 139
Relay_Log_Space: 1054
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

ERROR:
No query specified

--
--8. Check mysql log
--
$ tail -f /var/log/mysqld.log

what is #tmpDBCCinputbuffer?

I often see an active process in Activity Monitor in SSMS, its detail likes the following.
What's that?

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(72)')
select [Event Info] from #tmpDBCCinputbuffer

It is the process of Activity Monitor itself.

Windows Vista First Touch

My new desktop comes with Windows Vista. It's cool. I would like to try if things I need work or not.

1. Oracle 10g client
10.2.3.0 is cetified by Oracle. However, Vista still popped up incompatible warning. It works fine.

2. Oracle 9i client
I need Oracle 9i client to access some greatgrand-level databases(ex.8.1.6, as the new baby 11g is coming). I used 9.2.0.1.0, it got incompatible warning too, but it seems working fine.

3. PL/SQL Deveoper

When I run PL/SQL Developer 6, I got OCI error message. I thought of this issue occuring before. After granding full control permission of Oracle files to my domain user, it works.

4. SQL Server 2005

As I need to set up SQLH2, which requires SQL Server and Reporting service.
When I tried to install SQL Server 2005, I saw IIS warning message during system configuration checking. After searched in google, the workaround is found:

4.1. From Microsoft Support Site:
"You receive a warning message on the System Configuration Check page of the SQL Server 2005 Setup program on a computer that is running Windows Vista or a Server Core installation of Windows Server "Longhorn"" http://support.microsoft.com/kb/920201

4.2. Martin Poon's blog:
http://msmvps.com/blogs/martinpoon/archive/2006/12/08/installation-of-sql-server-2005-in-windows-vista-warning-message-on-iis-feature-requirement-during-system-configuration-check.aspx

After reconfigured and restarted IIS 7 on Vista, system config checking is all passed. SQL Server is installed successfully with Reporing Service.
For Vista, it required SQL Server 2005 SP2.

Other Notes:

To install IIS 7 or the IIS 7 components in Windows Vista, visit the following IIS Web site:
http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=957

To install IIS 7 or the IIS 7 components in a Server Core installation of Windows Server "Longhorn," visit the following IIS Web site:
http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=956

How to install SQL Server 2005 Reporting Services on a Windows Vista-based computer
http://support.microsoft.com/kb/934164

Running SQL Server on "Microsoft Windows Server Longhorn" or Microsoft Windows Vista
http://www.microsoft.com/sql/howtobuy/sqlonvista.mspx

Oracle Application Server Control 10g page not found

After installed Oracle application server 10g 10.1.3.1.0, ascontrol and applications were working properly. However after sysadmin rebooted the server, ascontrol got 404 error "The page cannot be found" .http://yourhost:7777/em/ Application got the error:

Fatal error
Webapplication did not initialize properly during startup - please try again later

--
--Fix:
--
1. in the file default-web-site.xml under OracleAS home ~/j2ee/home/config/
ohs-routing default is false, change it to true.

2. in the file server.xml under OracleAS home ~/j2ee/home/config/
change start attribute for application ascontrol to true. Default is false.

3. restart the servers

$ opmnctl status
$ opmnctl stopall
$ opmnctl startall
$ opmnctl status

TNS-12518: TNS:listener could not hand off client connection

OS: Red Hat Linux 2.6.9-34
Oracle 10gR2: 10.2.0.1.0

Error Message in listener.log:
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

Troubleshooting Steps:
1. check current listener connections and services
-bash-3.00$ lsnrctl ser

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 03-APR-2007 13:20:45
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "mydb" has 1 instance(s).
Instance "mydb", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:593 refused:23
LOCAL SERVER
Service "mydb" has 1 instance(s).
Instance "tedsdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:572 refused:0 state:ready
LOCAL SERVER
The command completed successfully

2. check current listener.ora

3. take SID_LIST out of listener.ora as PMON can automatically register DB service with listener(since 8i).

3. set up local_listener in the instance

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(host= mydbhost)(port=1521))" SCOPE=both;

4. restart listener.
lsnrctl stop
lsnrctl start

5. check listener service in a minute.

6. monitor listener connection usage.

7. turn on listener trace if cannot solve the problem.

Shutdown Immediate hang on 8.1.7.4.0 with ora_emn0

Yesterday one oracle database(8.1.7.4.0) was hung after I sent "shutdown immediate" command. I found a process ora_emn0 background process was started before shutting down. emn is Event Monitor Process for Advanced Queue Notification, which we don't use it on this instance. See Note 105067.1 to find What is the EMN0 process?

Why does it cause the hang of shutdown? After searching in metalink, I found one note describes this issue ( Database Shuts Down After the Message: Restarting dead background process EMN0). It is related to a known Bug 1800676 in this release.

--
--Solution
--
SQL> shutdown abort
SQL> startup
SQL> shutdown immediate
SQL> startup
SQL> alter trigger AURORA$SERVER$SHUTDOWN disable;
or
Set _system_trig_enabled=false in the init.ora and restart the database.

DMV: dm_db_file_space_usage

Select
SUM (user_object_reserved_page_count)*8/1024 as user_objects_mb,
SUM (internal_object_reserved_page_count)*8/1024 as internal_objects_mb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8/1024 as freespace_mb
From sys.dm_db_file_space_usage
where database_id=2

DMV: dm_os_memory_objects

select type, sum(pages_allocated_count) total_pages_allocated_count
from sys.dm_os_memory_objects
group by type
order by 2 desc

DMV: dm_os_memory_clerks

--
--Check Memory Usage
--
select top 15
type,
sum(single_pages_kb)/1024 single_pages_mb,
sum(multi_pages_kb)/1204 multi_pages_mb,
sum(awe_allocated_kb)/1024 awe_allocated_mb
from sys.dm_os_memory_clerks
group by type
order by 2 desc

Oracle Diagnostic Tools

RACDDT - a data collection tool designed and configured specifically for gathering diagnostic data related to Oracle's Real Application Cluster (RAC) technology.
See MetalinkNote:301138.1

RDA - Remote Diagnostic Agent - a set of command line diagnostic scripts that are used to gather detailed information about an Oracle environment.
See Metalink Note:314422.1
RDA 4 - Health Check / Validation Engine Guide
RDA 4 - RAC Cluster Guide

OSW - OS Watcher - a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing complex RAC issues as well as generic performance issues.
SeeMetalinkNote:301137.1

LTOM - The On-Board Monitor - a java program designed as a real-time diagnostic platform for deployment to a customer site. LTOM differs from other support tools, as it is proactive rather than reactive. LTOM provides real-time automatic problem detection and data collection.
See MetalinkNote:352363.1

Support Diagnostics Tools Catalog for E-Business Suite -
a test repository, execution engine, and user interface for customers, support analysts, and developers to plug-in diagnostic test tools/scripts
See MetalinkNote:179661.1

Oracle released Berkely DB 4.5

Yesterday Oracle released Berkely DB 4.5. It is open source(originially from SleepyCat), embeddede, non-relational, unattended DBMS.
Key Features in R4.5:

• Multi-Version Concurrency Control
• Non-stop Upgrades - coooool
  
• Replication Framework

Links:
http://www.sleepycat.com/
http://www.oracle.com/database/berkeley-db.html

SQL Trace (incl. 10g new tracing features)

--
-- sql trace
--
--related parameters
timed_statistics
max_dum_file_size
user_dump_dest

--to get session to be traced
select sid,serial# from v$session where ...
--or
select s.sid,s.serial#,s.username,s.osuser from v$session s,v$process p where s.paddr=p.addr;

use dbms_support to enable and disable the trace
($ORACLE_HOME/rdbms/admin/dbmssupp.sql)

--session level
alter session set sql_trace =true;
--instance level
alter system set sql_trace =true;

--trace other session
--to start tracing
--
--dbms_support(available in Oracle 7.2 and above)
exec dbms_support.start_trace_in_session(,);
exec dbms_support.start_trace_in_session(,,binds=true);
exec dbms_support.start_trace_in_session(,,waits=true,binds=true);
--
----dbms_system(undocumented since Oracle 7.3 to 10g R2)
see a guide at http://orafaq.com/papers/dbms_sys.doc
exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

--to check trace file under user_dum_dest
tkprof source output explain=user/pwd sys=no

--to stop tracing
--dbms_support
exec dbms_support.stop_trace_in_session(,);
--dbms_system
exec dbms_system.set_sql_trace_in_session(sid,serial#,false);

--Introduced in Oracle 10.1
DBMS_MONITOR
trcsess
DBA_ENABLED_TRACES
see http://julian.dyke.users.btopenworld.com/com/Presentations/OracleDiagnostics.ppt

Further Notes:
http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
http://www.nyoug.org/Presentations/SIG/DBA/hotsos_perf.PDF

Oracle Background Processes, incl. 10gR2

-------------------

--New in 10gR2

-------------------

PSP0 (new in 10gR2) - Process SPawner - to create and manage other Oracle processes.
NOTE: There is no documentation currently in the Oracle Documentation set on this process.

LNS1(new in 10gR2) - a network server process used in a Data Guard (primary) database.

Further explaination From "What's New in Oracle Data Guard?" in the Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)

"During asynchronous redo transmission, the network server (LNSn) process transmits redo data out of the online redo log
files on the primary database and no longer interacts directly with the log writer process. This change in behavior allows the log writer (LGWR) process to write redo data to the current online redo log file and continue processing the next request without waiting for inter-process communication or network I/O to complete."

-------------------

--New in 10gR1

-------------------

MMAN - Memory MANager - it serves as SGA Memory Broker and coordinates the sizing of the memory components, which keeps track of the sizes of the components and pending resize operations. Used by Automatic Shared Memory Management feature.

RVWR -Recovery Writer - which is responsible for writing flashback logs which stores pre-image(s) of data blocks. It is used by Flashback database feature in 10g, which provides a way to quickly revert an entire Oracle database to the state it was in at a past point in time.
- This is different from traditional point in time recovery.
- One can use Flashback Database to back out changes that:
- Have resulted in logical data corruptions.
- Are a result of user error.
- This feature is not applicable for recovering the database in case of media
failure.
- The time required for flashbacking a database to a specific time in past is
DIRECTLY PROPORTIONAL to the number of changes made and not on the size
of the database.


Jnnn - Job queue processes which are spawned as needed by CJQ0 to complete scheduled jobs. This is not a new process.


CTWR - Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.

MMNL - Memory Monitor Light process - which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

MMON - Memory MONitor (MMON) process - is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.

M000 - MMON background slave (m000) processes.

CJQn - Job Queue monitoring process - which is initiated with the job_queue_processes parameter. This is not new.

RBAL - It is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx - These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB - is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

Changes about Queue Monitor Processes

The QMON processes are optional background processes for Oracle Streams Advanced Queueing (AQ) which monitor and maintain all the system and user owned AQ objects. These optional processes, like the job_queue processes, does not cause the instance to fail on process failure. They provide the mechanism for message expiration, retry, and delay, maintain queue statistics, remove processed messages from the queue table and maintain the dequeue IOT.

QMNx - Pre-10g QMON Architecture

The number of queue monitor processes is controlled via the dynamic initialisation parameter AQ_TM_PROCESSES. If this parameter is set to a non-zero value X, Oracle creates that number of QMNX processes starting from ora_qmn0_ (where is the identifier of the database) up to ora_qmnX_ ; if the parameter is not specified or is set to 0, then QMON processes are not created. There can be a maximum of 10 QMON processes running on a single instance. For example the parameter can be set in the init.ora as follows

aq_tm_processes=1 or set dynamically via alter system set aq_tm_processes=1;

QMNC & Qnnn - 10g QMON Architecture

Beginning with release 10.1, the architecture of the QMON processes has been changed to an automatically controlled coordinator slave architecture. The Queue Monitor Coordinator, ora_qmnc_, dynamically spawns slaves named, ora_qXXX_, depending on the system load up to a maximum of 10 in total.

For version 10.01.XX.XX onwards it is no longer necessary to set AQ_TM_PROCESSES when Oracle Streams AQ or Streams is used. However, if you do specify a value, then that value is taken into account. However, the number of qXXX processes can be different from what was specified by AQ_TM_PROCESSES. If AQ_TM_PROCESSES is not specified in versions 10.1 and above, QMNC only runs when you have AQ objects in your database.

Oracle Background processes By Don Burleson

Comparison of Background Processes between 8i OPS and 9i RAC

20 Differences Between Oracle on NT and Oracle on Unix

ORA-1000 MAXIMUM NUMBER OF CURSORS EXCEEDED after Java application migrated

ORA-1000 MAXIMUM NUMBER OF CURSORS EXCEEDED
in Java application orcurred after application migrated for iPlanet to JBoss infrastructure.

It is a very very common error message. I remember I was asked this question in a job interview a couple of years ago. But I cannot remember what I explained. Anyway I got the offer. Ok, I'd like to give it a summary, even I still have a question left.

--
--Troubleshooting Steps:
--
1. Login database as DBA to check current setting of open_cursors
> show parameter open_cursors

2. Check who open too many cursors

set line 140
col username format a16
col machine format a26
SELECT vs.username, vs.sid,vs.serial#, vs.machine,Count(*) "Open_Cursors"
FROM v$open_cursor voc, v$session vs
WHERE voc.sid= vs.sid
GROUP BY vs.username, vs.sid,vs.serial# ,vs.machine
ORDER BY 5;

3. Get the entire queries

SELECT vst.HASH_VALUE, vst.SQL_TEXT, vst.PIECE, Count(vst.SQL_TEXT)
FROM v$open_cursor voc, v$sqltext vst, v$session vs
WHERE voc.sid= vs.sid
AND vs.username='CMGWAOE'
and voc.HASH_VALUE=vst.HASH_VALUE
and voc.ADDRESS=vst.ADDRESS
GROUP BY vst.HASH_VALUE,vst.SQL_TEXT ,vst.PIECE
/

4. Narrow down where these queries are called or from.

5.Review Java code with developer.

Make sure to close "ResultSet" objects before closing "PreparedStatement"/"CallableStement" objects.

if forgot to close the "ResultSet", some java application server(JBoss) may not have ability to close the (Oracle) cursors when close statement.(iPanet does)
If connection pooling is using, this issue may get even worse.

6. After enhance java code, problem went away.

But further question is, why child cursors still are open in shared-pool after parent cursor closed???

sync text index may not work in oracle9i(90130) sometimes

--
--Issue
--
CTX_DDL.SYNC_INDEX doesn't work on local partition of text index after new data is loaded.

CTXSYS.CTX_DDL.SYNC_INDEX('mytext_idx', '5M', 'partition2006');
(introduced in 816)
--
--diagnose
--
1.to check data in the base table

2.to check if data in text index
select text_clob from mytext_table where contains(text_clob,'test',1)>0;

3.to try to re-sync the partition index.
NOT WORK

--
--workaround
--
to rebuild local partition index
alter index mytext_idx rebuild partition partition2006;
--
--Advanced Diagnosis
--
1. to check all index partitions.
select IXP_TABLE_NAME,IXP_INDEX_NAME, IXP_INDEX_NAME,IXP_STATUS
from CTX_USER_INDEX_PARTITIONS;

2. to check which indexes are waiting to be indexed.
re-sync may remove records from this view.
select PND_INDEX_NAME,PND_PARTITION_NAME,COUNT(*) from ctx_user_pending group by PND_INDEX_NAME,PND_PARTITION_NAME;

3.to check the index error.
select * from CTX_USER_INDEX_ERRORS order by ERR_TIMESTAMP


--
--Reseach
--
Local Partitioned Index Maintenance in Technical Overview: Oracle Text version 9.0.1
SYNC OPTIMIZE

sync/optimize won't support in 10g.
alter index INDEXNAME rebuild parameters('sync') partition PARTITIONNAME;
alter index INDEXNAME rebuild parameters('optimize fast/full') partition PARTITIONNAME;

**
**It's not a good idea to use a tentative release in production.
**

oracle10gR2 supports SQL function LNNVL

actually it was undocumented since 7.2 or 7.3 according to the notes of Jonathan Lewis.

Oracle security guru Pete Finnigan's web site

http://www.petefinnigan.com/

Oracle Transaction Isolation

ORA-8117 error - cann't serialize access this transaction.

Oracle has three type of isolation levels:
read committed: it is default, I think it won't need to explain. Also this is how database to keep statement-level read consistency.
serializable: query result is repeatable during the transaction, it won't see the committed change by other transaction. if you try to update the data during the serializable transaction, which was changed by another concurrent user, this ORA-8117 error will happen. It's how database to keep transaction-level read consistency.
read-only: just read-only, no insert/update/delete is allowed.

Commands:
set transaction isolation level serializable;
set transaction isolation level read committed;
set transaction isolation level read only;

Also you can do it in session level, ex:
alter session set isolation level read only;

Oracle 10g DB and MS SQL Server 2005 Comparison by Edison Group

Edison Group just released a comparsing report: Comparative Management Cost Study of Oracle Database 10g Release 2 and Microsoft SQL Server 2005 (PDF).

It provides detailed execises performing on these two databases, it's the greatest report so far I have seen.Worthwhile to read it.

Ubuntu Linux

Ubuntu Linux
Ubuntu Blog
Installation Guide - Oracle 10G on Ubuntu Linux
and search "Ubuntu Oracle" in google.

List only directories

ls -d */
ls -l | grep "^d"

JasperReports - Open Source Reporting Tool

JasperReports is a powerful open source Java reporting tool that has the ability to deliver rich content onto the screen, to the printer or into PDF, HTML, XLS, CSV and XML files.

It is entirely written in Java and can be used in a variety of Java enabled applications, including J2EE or Web applications, to generate dynamic content.

Its main purpose is to help creating page oriented, ready to print documents in a simple and flexible manner.

About EnterpriseDB

About EnterpriseDB (From EnterpriseDB website)

EnterpriseDB produces the award-winning EnterpriseDB relational database management system (RDBMS). An enterprise-class RDBMS that is based on PostgreSQL, the world's most advanced open source database, EnterpriseDB runs many Oracle applications unchanged, performs better than native PostgreSQL and is priced to reflect its open source heritage.

EnterpriseDB was founded in March, 2004 to bring the benefits of open source to enterprise databases. The company launched its first product in May, 2005 and has quickly become one of the most talked-about open source companies. Headquartered in New Jersey, EnterpriseDB has engineering centers in New Jersey, Islamabad and Pune. EnterpriseDB has received venture capital financing from Charles River Ventures, Valhalla Partners and Sony Online Entertainment.

Export technology is changed in 10g

Export Dump 'expdp' is 2-time faster than previous 'exp', and support external tables.
Dump file and logs are located on server side under defined directory in the database.

PL/SQL API: DBMS_DATADUMP

--
--1. Create a directory
--
$ mkdir /test

CONNECT / AS SYSDBA
-- Create directory
create or replace directory TEST_DIR as '/test';

GRANT READ,WRITE ON DIRECTORY TEST_DIR TO scott;

--
--Drop Directory
--
DROP DIRECTORY TEST_DIR;

--
--2. Related Views
--
dba_directories, all_directories

--check privilieges
--
all_tab_privs, user_*,dba_*
all_tab_privs_made, user_tab_privs_made
all_tab_privs_recd, user_tab_privs_recd

--
--3.Estimate export dump before it gets underway
--
expdp mydbauser/mydbapwd ESTIMATE_ONLY=Y SCHEMA=myschema DIRECTORY=TEST_DIR

expdp mydbauser/mydbapwd ESTIMATE_ONLY=Y FULL=Y DIRECTORY=TEST_DIR

--
--Dump
--
expdp mydbauser/mydbapwd DIRECTORY=TEST_DIR SCHEMA=myschema DUMPFILE=schema_dmp LOGFILE=schema_log

expdp mydbauser/mydbapwd DIRECTORY=TEST_DIR FULL=Y DUMPFILE=fulldb_dmp LOGFILE=fulldb_log

--
--4.Monitor Export Dump job
--

select * from dba_datapump_jobs;

UNIX Commands

find ./ -name "*.arc" -atime +7 -exec ls {} \;
find ./ -type f -mtime +10 -exec ls {} \;
find ./ -type f -mtime +10 -exec rm {} \;

#
# Common Unix Command for DBA
# Oct 04,2002

$ORACLE_HOME/install/utl/unixenv

###########################
#OS setup
###########################
uname -X
uname -a
hostname
ulimit -a
swap -l
sar -r 1 1
vmstat
mount
df -k
env | sort
sar -v 1 1

ipcs -sa
/etc/sysdef | grep SEM
ipcs -ma
/etc/sysdef | grep SHM
/var/opt/oracle/oratab

#PhysicalMemory
prtconf|grep Memory

#CPU Info
psrinfo -v
mpstat 1 3

#OS Patches
showrev -p

#OS Packages
pkginfo -l

#system error log
dmesg

#National Language Support(NLS) Environment Settings
NLSPATH
NLS_LANG
ORA_NLS33
TZ

#profile
/etc/profile
$HOME/.profile
#for CDE login
$HOME/.dtprofile

/etc/*.conf

#Checking if domainname is set: domainname

/etc/hosts
/etc/groups
/etc/networks
/etc/nodename

#system configuration
/usr/sbin/sysdef|grep -v instance |grep -v drv |tail -77

#system uptime
uptime


##########################################
#Performance Overview
##########################################
=====================
#Process Overview
=====================
#Oracle:
ps -ef|grep oracle|wc -l

#Background:

#Listeners:

#Root:

#Number Users:

#Total Processes
ps -ef|wc -l

#Databases Running: (Note: the CPU Usage in Minutes)
/usr/ucb/ps aux|head -1
/usr/ucb/ps aux|grep oracle

#Who is logged on
who

#Current CPU HOGS:
top 15

#Current TOP 10 CPU by % CPU
/usr/ucb/ps aux|head -11

#root CPU Hogs

=====================
#Disk Related
=====================
#File System Free Space in KB
df -k

#throughput MB/sec, transfers/sec, avg service time milliseconds
iostat -M 10 3

#Current Disks (see iostat -E) run 7200rpm @@100 random I/O /sec or 500 sequential/sec
#100 I/O per sec imples seek time 10 ms since most access is seek time
#Disks on PC capable of sustaned 8-16 MBit/sec and Unix 32-40 Mbit/sec

#throughput KB/sec, transfers/sec, avg service time milliseconds
iostat -d 10 3

#extended disk statistics
#look for disks more than 30 % busy and service times of more than 50ms
#iostat -x.b > 30% && iostat -x.svc_t > 50ms == busy disk
#look for overloaded scsi bus
#iostat -x.w >= 5% == busy scsi bus
#striping filesytems and tablespaces over multiple disks and
#controllers can spread i/o over multiple spindles
iostat -xe 10 3

#disk error reports
iostat -e

#paging activities
sar -g 1 4|grep -v SunOS

#swap activities
swap -l
swap -s



=====================
#CPU Overview
=====================

#General CPU Information
psrinfo -v

#CPU per-processor statistics
mpstat 1 3 -v

#Sar CPU Usage
sar -u 1 4|grep -v SunOS

#Top Report Usage
top


#system calls
sar -c 1 4|grep -v SunOS

#Memory Statistics
vmstat -s

# message and semaphore
sar -m 1 4|grep -v SunOS


###################
#Network
###################
ifconfig -a

#tcpip settings
ndd /dev/tcp \?

#SQL*Net Config Files
sqlnet.ora
tnsnames.ora
listener.ora

#Intelligent Agent Config Files
$ORACLE_HOME/network/admin/snmp_ro.ora
$ORACLE_HOME/network/admin/snmp_rw.ora
$ORACLE_HOME/network/log/nmiconf.log
$ORACLE_HOME/network/agent/services.ora
#Configuration file for webServer discovery
$ORACLE_HOME/network/agent/config/nmxw.ora
#config file for discovering Oracle Applications
$ORACLE_HOME/network/agent/oapps.ora

#Heterogeneous Services Config Files
#HS parameters that are needed for an ODBC Agent.
$ORACLE_HOME/hs/admin/inithsodbc.ora

#Oracle Management Server Config Files
$ORACLE_HOME/sysman/config/omsconfig.properties

#Listener Status and Services

===============================
#Environment
===============================
#Checking to see if TNS_ADMIN is being used

#list of .ora files from /etc

#list of .ora files from /var/opt/oracle

#List of .ora files from $ORACLE_HOME/network/admin
sqlnet.ora
listener.ora
tnsnames.ora
snmp_rw.ora
snmp_ro.ora

#Checking for any all tnslsnr PROCESSES: (Look for CPU hogs)

#Checking for any TWO TASK PROCESSES:

#Is ORACLE_HOME / SID / TWO_TASK set:
ORACLE_BASE
ORACLE_SID
ORACLE_OWNER
OES_ORACLE_HOME
ORACLE_TERM
ORACLE_HOME

==========================
#Network Performance
==========================
ping -s -v -R 1280 10

Ping Report from sun3-lab.vialta-inc.com (this machine) to sun3-lab.vialta-inc.com (this machine)

Ping Report from sun3-lab.vialta-inc.com (this machine) to 10.0.43.88 (wan)

Ping Report from sun3-lab.vialta-inc.com (this machine) to sun3-lab.vialta-inc.com (rdbms)

Ping Report from sun3-lab.vialta-inc.com (this machine) to 10.0.44.101 (web)

Ping Report from sun3-lab.vialta-inc.com (this machine) to 10.0.43.88 (client)

#Are the tns processes running and for what listeners:
UID PID PPID C STIME TTY TIME CMD
oracle 8469 7662 0 21:53:15 pts/3 0:00 grep tnslsnr
oracle 7298 1 0 Aug 22 ? 4:27 /oracle/9.0.1/bin/tnslsnr LISTENER -inherit

#Unix netstat ESTABLISHED
netstat|grep ESTABLISHED|wc -l

#Unix netstat WAITING
netstat|grep WAITING|wc -l

netstat -a

#Ports Being Used
/etc/services

#Network Cards and setup
ifconfig -a:

#Network Performance
netstat -s

############################
#RDBMS Product Versions
############################
init.ora
V$Parameters
V$Option
SGA Information: v$SGA V$SGASTAT
V$License Information
V$Compatibility Information
NLS_Database_Parameters
NLS_Instance_Parameters
NLS_Session_Parameters
NLS Session TimeZone
NLS Database TimeZone

Java Information
Java Objects
Java Roles
Oracle Supplied Java Users

V$Controlfile Information
Log Information : V$Log V$Logfile
Undo Information: V$UndoStat DBA_Undo_Extents

V$System_Event
V$Session_Wait
Latch Information: V$Latch V$Latchholder

Tablespaces
Tablespace
Locally Managed Temporary Tablespaces
Database Files
Database Files
Temp files

Invalid Objects

All_Errors: dba_errors

Install logs
$ORACLE_HOME/install/make.log
$ORACLE_BASE/oraInventory/logs/installActions*.log

Last 7940 Lines of Alert Log
$ORACLE_BASE/admin/sid/bdump/alert_sid.log

WebIV

Last Trace File

Trace/Log Directory Listings
Background Dump Destination (from background_dump_dest setting)
Default Background Dump Destination ($ORACLE_HOME/rdbms/log)
Core Dump Destination (from core_dump_dest setting)
Default Core Dump Destination($ORACLE_HOME/dbs)
User Dump Destination

check the port is open or not --> telnet sun2-prod-101 1754

check ORACLE port is open or not -->tnsping "(ADDRESS=(PROTOCOL=TCP)(HOST=sun2-prod-101)(PORT=1748))"

check port is listened or not --> netstat -an|grep 1754

check DNS resolution --> nslookup

check history commands-->history / last

trace system calls and signals --> truss lsnrctl dbsnmp_start

truncate current used log file --> :>listener.log


Tool for Sun Performance and Tuning
====================================
SE Toolkit can be downloaded from www.setoolkit.com
sun2-noc:(need x-server)
/opt/RICHPse/bin/se ../examples/zoom.se&


$ export ORACLE_SID=orcl1; echo 'startup'|sqlplus "/ as sysdba"


DNS config file-->/etc/resolv.conf


Use of the Secure Copy (scp) Command
=====================================
General Syntax:

scp [-pr] [userid@][source host:][path to file] [userid@][destination host:][path to file]

The "-p" option is to preserve the time stamp of the file.
The "-r" option is to recursively copy files from the source directory specified.
Once you enter the scp command, you will prompted for access to the host

Examples:
From Remote Unix to Local Unix
-------------------------------
$ scp emao@10.0.23.102:/u01/oradata/8.1.7/dbs/* /opt/oracle/8.1.7/dbs/

From Local Unix to Remote Unix
-------------------------------
$ scp /opt/oracle/8.1.7/dbs/* emao@10.0.23.102:/u01/oradata/8.1.7/dbs/


check partition block size-->df -g

compare files-->cksum

replace a word in vi editor--> :%s/test/exam/g
:3,5s/test/exam/g

undo in vi editor--> u

help
: set all
display line number
: set number
ignore case-sensitve
: set ic

shift+w
shift+4
shift+0
shift+j

Track shell script--> sh -x dbora start

nohup sqlplus "/as sysdba" <<>/usr/proc/bin/pmap -x
Refer to Note:163763.1 Diagnosing Oracle memory on Sun Solaris using PMAP


ipcs -m

report per-processor statistics-->mpstat

check os bit-->isainfo -b

prompt hostname and SID(.profile)--> PS1="`hostname`*\${ORACLE_SID}$ "

see the whole process string-->/usr/ucb/ps -auxww|grep jre


zcat O8i_DG_R3_0_2_Sun_tar.Z | tar xvf -
|--> uncompress O8i_DG_R3_0_2_Sun_tar.Z
+--> tar -xvf O8i_DG_R3_0_2_Sun_tar


rsh without prompting password
-------------------------------
--> .rhosts under remote user default hhome
sun7-dev$ cat .rhosts
sun6-dev oracle
--> make sure hostname is the same as iin "who" on remote site
sun7-dve$ who
oracle pts/5 Feb 15 14:35 (sun6-dev)
Note: http://sunsolve.Sun.COM for the Document 12029 REMOTE LOGIN PSD/FAQ.

mail on unix
--------------------
send email/pager-->
echo 'test' | mailx -s 'Subject: Alter" emao@sun5-noc.vialta-inc.com
echo 'test' | mailx -s 'Subject: Alter" 5103766360@messaging.nextel.com

email auto-forward--> .forward (under user default home)
/etc/mail/alias


--to show 32-bit or 64 bit OS
--sparcv9-->64 bit
$ isalist
sparcv9+vis sparcv9 sparcv8plus+vis sparcv8plus sparcv8 sparcv8-fsmuld sparcv7 sparc