Oct 23, 2007

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

Oct 12, 2007

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?

Aug 3, 2007

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

Aug 2, 2007

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

Jul 5, 2007

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;

Jul 4, 2007

[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

Jun 18, 2007

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.

Jun 7, 2007

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

Apr 3, 2007

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.

Mar 2, 2007

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.