DBA

If you try to drop a user that owns a schema, you will receive the following error message: The database principal owns a schema in the database, and cannot be dropped. In order to drop the user, you need to find the schemas they are assigned, then transfer the ownership to another user or role SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('hydepark') -- now use the names you find from the above query below in place of the SchemaName below ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

when you type backspace on unix and it shows ^? and does not work, try this: stty erase

ORA-4031 During Startup Nomount using RMAN without parameter file (PFILE) [ID 1176443.1] -------------------------------------------------------------------------------- Modified 06-DEC-2010 Type PROBLEM Status PUBLISHED In this Document Symptoms Cause Solution -------------------------------------------------------------------------------- Applies to: Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later [Release: 11.2 and later ] Information in this document applies to any platform. Symptoms RMAN startup nomount failed with ORA-4031 Customer was testing RMAN backup/restore in Exadata. Customer firstly backup the database to tape and then remove all the datafiles, spfile, controlfiles for testing. Then during the recover, customer connected RMAN with nocatalog and try to "startup nomount", then ORA-4031 occured. ==================== Log ======================== oracle@hkfop011db01:/home/oracle $ export ORACLE_SID=TEST oracle@test011db01:/home/

when restoring oracle spfile, I get the following error. RMAN> set dbid=1170383141 executing command: SET DBID database name is "PROD" and DBID is 1170383141 RMAN> run 2> { 3> allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_rman_p7_ibms_prod.opt)'; 4> restore spfile; 5> restore controlfile; 6> } allocated channel: t1 channel t1: sid=27 devtype=SBT_TAPE channel t1: Data Protection for Oracle: version 5.4.1.0 Starting restore at 24-OCT-2011:15:37:55 released channel: t1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/24/2011 15:37:55 RMAN-06758: DB_UNIQUE_NAME is not unique in the recovery catalog query the rman catalog database to verify that there are multiple db_unique_name sh

assume that we lost all the files of oracle database but we do have rman backup, when trying to bring up a dummy database before restore start, I get this error. RMAN> startup nomount force; WARNING: cannot translate ORA_RMAN_SGA_TARGET value startup failed: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/PROD/spfilePROD.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/PROD/spfilePROD.ora ORA-15056: additional error message ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/prod/spfileprod.ora ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod' ORA-06512: at line 4 starting Oracle instance without parameter file for retrival of spfile RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =================================

if you want to bring back the previous command, use below command set -o vi than press ESC key & press k for privious command

#* FileName: ServerInventory.ps1 #*============================================================================= #* Script Name: [ServerInventory] #* Created: [12/14/07] #* Author: Jesse Hamrick #* Company: PowerShell Pro! #* Email: #* Web: http://www.powershellpro.com # Modified by : jiulu sun # Modified at : Oct 28th, 2010 # usage : CTV IT managed sqlservers # How to use : run powershell, type $PathOf\filename.ps1>outputfilePath and file, it will loop all servers in file d:cvb\Computers.txt # you need to change executionpolicy first if its at restricted mode. or you will get an error # "File C:\vb\sqlserver_info1.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_sig # ning" for more details." # this is an example # PS C:\vb> Get-ExecutionPolicy # Restricted # PS C:\vb> set-executionpolicy remotesigned # PS C:\vb> Get-ExecutionPolicy # RemoteSigned #

Adventure Works DW

sometimes BPC server has some dead loop mdx query that run a few hours, make the server cpu 90% busy, and active thread is more than 9. to identify those dead loop mdx sessions, run the following mdx query. select session_connection_id,session_user_name, session_start_time ,session_last_command_start_time as cmd_start_time,session_last_command_end_time as cmd_end_time, session_last_command_elapsed_time_ms as cmd_elapsed_time_ms,session_idle_time_ms,session_last_command FROM $SYSTEM.DISCOVER_SESSIONS pay attention to those that have 0 session_idle_time_ms and big cmd_elapsed_time_ms, pick up there connection_id and kill them. to kill the sesion, run (Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine") (ConnectionID) replace_with_id_number (/ConnectionID) (/Cancel)

Question Can you type more than one command at one command prompt? Answer Yes, use the pipe or the ampersand to separate your commands. The shell and version of Windows you are using decides what character to use. Below are some additional examples for each version of Windows and shell. In both of these examples the command would first get to the root of the current drive and then run the dir command to list the current contents of the root. Microsoft Windows 95, Windows 98 and Windows ME users cd\ | dir Microsoft Windows 2000 and Windows XP users using the command shell Instead of using the pipe Microsoft decided to change it to the ampersand (&) for reasons unknown by us. Therefore you would need to type the below command to have the same results as earlier versions of Windows. cd\ & dir

I need to map a network drive to backup mssql database, I used to use windows 2003 resource kit to run autoexnt service, but I found the following method is also working for me on windows 2008 standard edition. here you go. http://www.tutorial5.com/content/view/157/47/ Now if you want to integrate any kind of script to automatically run when Windows starts or shuts down (user logon/logoff), you must follow these steps: 1. Click Start - Run and type mmc (note that you must be logged in with an administrator account for this) 2. On the Management console that starts, click File -> Add/Remove Snap-in. 3. On the window that opens, search for Group Policy Object editor on the left panel, click Add and in the properties window that pops out leave the settings that are selected (Local computer) and click ok. The result should be like in the picture bellow Click "OK" 4. Navigate through the left panel by opening each branch like this: Console Root -> Computer configuration ->

Recently my windows PC take 5 minutes to reboot and the logon take another 5 minutes, a friend showed me how to disable background application when booting up windows, that helps me to reduce rebooting time dramatically. what you need to do is to run msconfig from command and get a GUI window, click up "startup" tab and disable things that you do not need, and reboot.

user run a select statement on a local table but get "ora-01591:lock held by in-doubt distribted transaction 3.1.116925" error, somehow a developer update a record through database link to sybase but forgot to commit on oracle side. here is how to troubleshoot and resolve it. 1. find out the pending transactions. in oracle 10g: sqlplus > select * from dba_2pc_pending; sqlplus > select * from DBA_2PC_NEIGHBORS; record local_tran_id (in oracle 9i sqlplus > select * from sys.pending_trans$; the query result shows the local_tran_id (3.1.116925), os_user (a developer name shows here) and a os_terminal (the developers pc name), fail time (recent) and state (prepared). ) 2. rollback the pending transaction, query the state again, the state is changed from prepared to "forced rollback" sqlplus > rollback force '3.1.116925'; 3. clear the records. execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.1.116925') commit; now query the sys.pending_trans$

######################################################## Description : BIDTST database is accidently dropped before it's RMAN database backups on tapes are removed. so we need to remove the backups without having BIDTST db mounted. The doc retains the error messages which help troubleshooting... Date : June 1, 2011 DBA : Jiulu Sun & Kevin Ma ######################################################## $ rman catalog rman/password@catalog target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 2 15:26:52 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: DUMMY (not mounted) connected to recovery catalog database RMAN> @generic_config.rman RMAN> configure channel device type sbt parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_rman_agcoux043.opt)'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMA

the following query will show the last full/incremental/archivelog/controlfile backup. select name, full, inc, log, ctl from rman.rc_database d, (select db_id, max(START_TIME) full from rman.rc_backup_set s where s.backup_type in ('D') and controlfile_included = 'NONE' group by db_id) f, (select db_id, max(START_TIME) inc from rman.rc_backup_set s where s.backup_type in ('I') group by db_id) i, (select db_id, max(START_TIME) log from rman.rc_backup_set s where s.backup_type in ('L') group by db_id) l, (select db_key, max(completion_TIME) ctl from rman.rc_backup_controlfile s group by db_key) c where f.db_id(+) = d.dbid and i.db_id(+) = d.dbid and l.db_id(+) = d.dbid and c.db_key(+) = d.db_key order by 1 sample output shown below. NAME FULL INC LOG CTL -------- ----------------- ----------------- ----------------- ----------------- datab1 17-feb-2011:01:09 datab2 29-may-2011:18:30 31-may-2011:18:10 31-may-2

http://www.mssqltips.com/tip.asp?tip=1722 Problem Quite frequently I find myself in situation where I need to get detailed information on performance monitor counters. For example I need to determine which processes are consuming all CPU at certain times. I find it handy to push the performance monitor counters into SQL Server where I can query it or perhaps display it in Reporting Services. Solution The following will explain how to select counters, how to collect data, how to load the data to SQL Server and how to query the data that has been saved. Select Counters I first need to collect my data. I use TypePerf to display a list of performance monitor counters on the machine I wish to collect from. TypePerf is installed by default on all machines after Windows 2000. I use the following query to get a list of all the counters that can be used on the machine I am monitoring. Although the counters are generally the same they may be different from machine to machine.. This is run in a

http://www.mssqltips.com/tip.asp?tip=2188 This process involves three steps, each described separately below. Step One Create the Registered Server Group for the SQL Instances you intend to push this solution. I tend to do this step in three phases: Two test instances (one default instance and one named instance – you’ll see why shortly when we look at the results that are returned for queries against sys.dm_os_performance_counters for named instances.) A group of remaining non-production SQL instances since I intend to push out the solution and monitor its behavior for a couple days before I push to production servers. The production instances of SQL Server that I intend to run this process for monitoring. It should be noted that since we’re dealing with Dynamic Management Views that this solution will only work for SQL Server 2005 and newer. Only for the purposes of this article have I created the MSSQLTips registered servers group. It contains two instances: the default instance an

SQL Server 2000 Code Snippet -- Variable Declarations DECLARE @PreviousDate datetime DECLARE @Year VARCHAR(4) DECLARE @Month VARCHAR(2) DECLARE @MonthPre VARCHAR(2) DECLARE @Day VARCHAR(2) DECLARE @DayPre VARCHAR(2) DECLARE @FinalDate INT -- Initialize Variables SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days SET @Year = DATEPART(yyyy, @PreviousDate) SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) SET @FinalDate = CAST(@Year + @Month + @Day AS INT) -- Final Logic SELECT j.[name], s.step_name, h.step_id, h.step_name, h.run_date, h.run_time, h.sql_severity, h.message, h.server FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id

TYPEPERF.EXE is a command line tool included with the Windows operating system that writes performance data to the command window or to a file. To get a list of counters with instances enter the following command: TYPEPERF -qx "SQLServer:Databases" | FIND "tempdb" You will see output similar to the following: \SQLServer:Databases(tempdb)\Data File(s) Size (KB) \SQLServer:Databases(tempdb)\Log File(s) Size (KB) ... C:\Documents and Settings\automatingdba>TYPEPERF -q "SQLServer:Databases" \SQLServer:Databases(*)\Data File(s) Size (KB) \SQLServer:Databases(*)\Log File(s) Size (KB) ... TYPEPERF -f CSV -o MyCounters.csv -si 15 -cf MyCounters.txt -sc 60 The above example writes the counter values to MyCounters.csv every 15 seconds. It stops after writing out the counters 60 times (i.e. 15 minutes). The list of counters in MyCounters.txt is: \SQLServer:Databases(_Total)\DBCC Logical Scan Bytes/sec \SQLServer:Databases(tempdb)\Percent Log Used ... the followi

@echo off Echo. > c:\dir\sstate.txt Echo Stopping service on all servers sc \\servername stop servicename> nul Echo Waiting 10 seconds CHOICE /N /C YN /T 10 /D Y Echo Starting service on all servers sc start service > nul sc \\servername start servicename > nul Echo Waiting 10 seconds CHOICE /N /C YN /T 10 /D Y Echo servername >> c:\dir\sstate.txt sc \\servername interrogate servicename >> c:\dir\sstate.txt if defined batch goto :EOF notepad c:\dir\sstate.txt

###this script run a query to generate an output file and ftp to remote site. export ORACLE_SID=PROD ORAENV_ASK=NO . oraenv FTP="ip.ip.ip.ip" LOGIN="ftpuser" PASS="password" sqlplus -s " / as sysdba" < set verify off set pagesize 0 set linesize 500 set head on --set markup html on set feedback off set timing off --set pagesize 0 set colsep , col channel_name format a29 col programme_name format a49 col house_media format a9 spool /folder/filename.csv SELECT statement; spool off exit EOF ftp -nv $FTP < /folder/log.log echo "############# Starting FTP to $FTP ###################" user $LOGIN $PASS type ascii lcd /folder del filename.csv put filename.csv dir filename.csv quit EOFLOG echo finished ftp

#!/bin/ksh ###################################################### # alert_log_mail.ksh # Purpose: email error msg in alert log to DBA # to use it, run thisscript.ksh instancename or ALL ################################################################################ if [ $# -eq 0 ] then echo "Usage: `basename $0` | ALL " exit else OPTION=$* fi if [ "$OPTION" = "ALL" -o "$OPTION" = "all" ] then DB_LIST=`cat /etc/oratab|grep -v ^#|grep -v agent|grep -v Apach|grep -v ^$|awk -F: '{print $1}'` else DB_LIST=$OPTION fi export DBLOG=/home/oracle/dba/logs get_msg(){ FILENAME=$1 MSG=$2 if [ -s ${DBLOG}/$(basename ${FILENAME}).mail ] then CKPOINT=`tail -1 ${DBLOG}/$(basename ${FILENAME}).mail|awk -F: '{print $1}'` else CKPOINT=0 fi FILESIZE=`cat $FILENAME|wc -l` if [ $FILESIZE -lt $CKPOINT ]; then CKPOINT=0; fi STARTPOINT=`expr ${CKPOINT} + 1 ` tail +${STARTPOINT} ${FILENAME}|hea

Recently our BPC nightly job failed intermittently, a few database mirror servers report timeout as well, I need to prove to network team that it's not an application issue, it's a network issue, so I implement the a windows scheduled task to run pathping among those servers every 1 minute. it did prove that network timeout. here is the script and sample of logs. pingnetwork.bat : echo %date% %time% pathping server1 echo %date% %time% pathping server2 echo %date% %time% pathping server3 echo %date% %time% pathping server4 pingnetwork.bat >> e:\dropit\d.txt the log d.txt looks like this " Thu 05/19/2011 3:20:15.19 E:\dba>pathping agenwi034 Tracing route to agenwi034.corp.ctv.ca [10.1.38.134] over a maximum of 30 hops: 0 agends030.corp.ctv.ca [10.1.38.130] 1 * agenwi034.corp.ctv.ca [10.1.38.134] Computing statistics for 25 seconds... Source to Here This Node/Link Hop RTT Lost/Sent = Pct Lost/Sent = Pct Address 0

The witness sqlserver sometime get disconnected, reboot it sometime fix the problem, if not, reset witness is required, here is how. to find out how many databases are using this witness, run query on witness: SELECT principal_server_name, mirror_server_name, database_name, safety_level_desc FROM sys.database_mirroring_witnesses to find out which server is disconnected, run this sql on the principle server: select mirroring_partner_name,mirroring_partner_name,mirroring_witness_name, mirroring_state_desc,mirroring_witness_state_desc, * from sys.database_mirroring if the mirroring_witness_state_desc = disconnected, then remove the witness by running the following script. let's refer this list as $DBLIST. on witness: select 'alter database '+database_name+' set witness off' FROM sys.database_mirroring_witnesses pickup the output that matching $DBLIST and save the output as o1.sql. select 'alter database '+database_name+' set witness ='+'''

goal: create database after creating control file using "alter database create controlfile to trace as 'filename';", I sometime need to replace the path of datafiles, the two awk command below helps me doing so. awk -F '/' '{ if (NR>10 && NR < 107 && ($NF ~ /dbf/ ||$NF ~ /log/) ) {print "\x27/bossdb/bnwrms/"$NF;} else if (NF==0) {} else {print $0;}}' control.sql awk –v q=“’” -F '/' '{ if (NR>10 && NR < 107 && ($NF ~ /dbf/ ||$NF ~ /log/) ) {print q "/bossdb/bnwrms/"$NF;} else if (NF==0) {} else {print $0;}}' control.sql