SQL server database backup - Important to check the backup policy

Recently , we have a sql server data corruption that needed the backup to be restored. We have two scheduled backup , 1 is to a backup disk and the other one is to the backup tape.

After checking the backups, found out that the only usable restoration is by using the backup tape. This is because the task to backup to tape reinitialized the transaction log , this caused the folder backup cannot be restored to point in time.

The point is, Make sure that if there are two backup policy ensure that both of them didn't clear the other one backup transaction log.

Apache Slow performance with NFS mount

Recently we have a slow performance with our web site after we redirect our OC4J Application logs to the NFS mount, which is using servlet to logs the application log.

Metalink :

Log Directories In NFS Mounted Device Environment Causes Performance Problem With Apache , Note: 180522.1

Bad performance of Apache when filesystem is NFS mounted, Note : 467315.1

Import DataPump : Error after importing PLS-00103 - compilation error

If you encounter error PLS-00103 while using import data pump to import data pump data into a new database with Oracle 10g but dont have problem if using the import and export utility . (imp/exp) .

Then most probably you encounter the bug in oracle 10g. If the source package/procedure... is wrapped and uses mulitbyte character set when using the import data pump, it add extra line behind the wrap.

To workaround this , 1)use the imp/exp tools. 2) manually edit the source code to removed the extra lines. 3) Find and get the bug fixed patch in the metalink.

Impdp Returns ORA-39082 When Importing Wrapped Procedures
Metalink Note :- 460267.1

Performance problem while starting up Opmnctl / Application for Oracle Application 10.1.2.0 in clustered

If you have performance problem while starting up Opmnctl / Application for Oracle Application 10.1.2.0 in clustered, this could be due to the bug in the OC4J.

As higlighted , in the bug fixed for the 10.2.2.2 ,

In a clustered environment the command 'dcmctl updateConfig ..' in one node copies the orion-ejb-jar.xml to other node and as a result unnecessary redeployment of the j2ee application occurs in other node.

To check, goto both server of the clustered and compared the files at $ORACLE_HOME/j2ee/APP/application-deployments/AppName/AppName.jar/orion-ejb-jar.xml

You will also notice in the log files, if you have set the OC4J options -out

09/02/23 20:34:16 Auto-deploying - AppEJB.jar (orion-ejb-jar.xml had been updated since the previous deployment)...

References:-
Metalink Doc ID: 398955.1

Error in Oracle Infrastructure - LDAP ora31203

If you encounter the below error, while installing your Oracle OID / Infrastructure , make sure that the /etc/hosts for both server contain your hostname and hostname.domain

LDAP error : ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.

Eror: ORA-31203 (ORA-31203)

Text: DBMS_LDAP: PL/SQL - Init Failed.

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

Cause: There has been an error in the DBMS_LDAP Init operation. Action: Please check the host name in the hosts file and the port number to see if it's been taken up, or report the error number and description to Oracle Support.

SQL server performance diagnostics tool

PSSDIAG data collection utility


PSSDIAG is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG can natively collect Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output.


http://support.microsoft.com/kb/830232





RML Utilities for SQL Server (x86)

Tools to help database administrators manage the performance of Microsoft SQL Server.

http://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

SQL Nexus Tool


Tool that helps you identify the root cause of SQL Server performance issues


http://www.codeplex.com/sqlnexus










DbDiff - DbScripting (without dmo,smo)


Compare MSSql database structures. (Sql 7,Sql 2000,Sql 2005)


http://www.codeplex.com/DbDiff

SQL Server data corruption - DBCC checkdb ( REPAIR_ALLOW_DATA_LOSS )

Recently one of our sql server has a RAID 5 hard disk failure, after the hard disk is changed the sql server report data error while running the dbcc checkdb.

If the DBCC checkdb , returns error such as below, that means that the clustered index (index ID 0) could have been corrupted and data loss is a possiblities.

Server: Msg 8928, Level 16, State 1, Line 1Object ID 1335232803, index ID 0: Page (1:14469854) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1Table error: Object ID 1335232803, index ID 0, page (1:14469854). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1Object ID 1335232803, index ID 0: Page (1:14469855) could not be processed. See other errors for details.Server: Msg 8928, Level 16, State 1, Line 1

If the Index ID is more than 1 , then only the index is spoilt.

After determining, the Page id we can get the raw data by using the below command. The 3 that's means formatted data and the 2 is the raw data.

DBCC TRACEON (3604);
GO
DBCC PAGE ('DB', 1, 14469854, 3);
GO

After this, we can find out the primary key of the tables . Based on this we can restored the data from the backup to here.

Another way is to restore the page data, this is only can be done minimumly in sql 2005

Links :-
SQL Server in Recovery by Paul S. Randal.
http://www.sqlskills.com/BLOGS/PAUL/category/Corruption.aspx#p31

Fixing damaged pages using page restore or manual inserts
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/01/18/fixing-damaged-pages-using-page-restore-or-manual-inserts.aspx