Thursday 16 December 2010

Confluence : FIx space permissions via SQL

--General query
select s.spacename,sp.*,s.*
from SPACEPERMISSIONS sp,
spaces s
where 1=1
and s.spaceid = sp.spaceid
order by 1,2

--Look up the exact space name
select * from spaces

--Check what permid range is available
select * from SPACEPERMISSIONS
where spaceid in (select spaceid from spaces where spacename = 'IT')
order by 1

commit

-- Add full permissions to the confluence-administrators group for a single space
-- * Set the offest to a range which allows 14 rows to be added
-- * Use either perm_groupname OR perm_username but not both
-- * Replace anotheruser as appropriate
insert into SPACEPERMISSIONS
with target as (
select
3620 offset,
'confluence-administrators' perm_groupname,
NULL perm_username,
'IT' spacename,
'anotheruser' audit_username
from dual),
ptypes as (
SELECT 'COMMENT' permtype from dual union all
SELECT 'CREATEATTACHMENT' from dual union all
SELECT 'EDITBLOG' from dual union all
SELECT 'EDITSPACE' from dual union all
SELECT 'EXPORTPAGE' from dual union all
SELECT 'EXPORTSPACE' from dual union all
SELECT 'REMOVEATTACHMENT' from dual union all
SELECT 'REMOVEBLOG' from dual union all
SELECT 'REMOVECOMMENT' from dual union all
SELECT 'REMOVEMAIL' from dual union all
SELECT 'REMOVEPAGE' from dual union all
SELECT 'SETPAGEPERMISSIONS' from dual union all
SELECT 'SETSPACEPERMISSIONS' from dual union all
SELECT 'VIEWSPACE' from dual)
select
t.offset+rownum permid,
s.spaceid,
p.permtype,
t.groupname permgroupname,
t.perm_username permusername,
t.audit_username creator,
sysdate creationdate,
t.audit_username lastmodifier,
sysdate lastmoddate
from target t,
ptypes p,
spaces s
where t.spacename = s.spacename (+)

Friday 19 November 2010

Keeping an SSH tunnel (passworded) running perniciously

* Download and compile sshpass and place in DIR
http://sshpass.sourceforge.net/

* Create a script like this
DIR=/?
LOG=$DIR/ssh.log
LOCAL_PORT=15000
REMOTE_SERVER=192.168.69.69

REMOTE_PORT=1521
echo "`date` - Restarting " >> $LOG

export SSHPASS='PASSWORD'
$DIR/sshpass -e ssh  $USERNAME@ $REMOTE_SERVER -L${LOCAL_PORT}:localhost:${REMOTE_PORT} -N -o ServerAliveInterval=30 -o TCPKeepAlive=yes -o ServerAliveCountMax=172800
echo "`date` - Ending " >> $LOG

* Add the following to /etc/inittab - replace xxxx with a unique id, USER with the user to login as and and script.sh accordingly with full path
xxxx:35:respawn:/bin/su - USER "/?/script.sh"

* Get the init process to reload (as root)
kill -HUP 1

* Test

Thursday 18 November 2010

Keeping Your SSH Sessions Alive Through Firewalls

To send a keep alive every 4 minutes either change ~/.ssh/config or /etc/ssh/ssh_config to add
Host *   
     ServerAliveInterval 240


Wednesday 13 October 2010

Windows : More RDP-related commands

Connect to a share to get an admin connections
* net use /user:targetdomain\administrator \\targetmachine

List Current Users on a machine
* quser /server:targetmachine (shows session id - better as also shows login and idle time)
* qwinsta /server:serverName
Log a user off a machine

* logoff sessionId /server:targetmachine<br />

Tuesday 5 October 2010

Sharepoint - Where is the data hidden


SELECT TOP 1000 [tp_ID]
      ,tp_author
      ,tp_version
      ,[datetime1]+8 x1
      ,[datetime2]+7 x2
      ,[datetime1]
      ,[datetime2]
      ,[nvarchar1]
      ,[nvarchar2]
      ,[nvarchar3]
      ,[nvarchar4]
      ,[nvarchar5]
      ,[nvarchar6]
      ,[nvarchar7]
      ,[datetime3]
      ,[datetime4]
      ,[datetime5]
      ,[int1]
      ,[int2]
      ,[int3]
      ,[int4]
      ,[int5]
      ,[int6]
  FROM [WSS_Content].[dbo].[AllUserData]
  where 1=1
  --and tp_ListId = 'DA7307B0-4D77-4DEE-A347-D183BE27CCC3'
  and tp_DirName = 'hr/holidays/Lists/Absences'
  and nvarchar5 = 'IT Development'
  and tp_author = 51
  and tp_ID = 709
  order by datetime1 desc

Thursday 9 September 2010

Perl : One liner to find files less than an hour old in a dir

 perl -e 'for ( glob "./*" ) { system "ls -l $_"  if ( -M $_ <= (1/24) ) };'

Tuesday 7 September 2010

iTunes Error 9808

Itunes error -9808 logging into the iTunes Music Store

In Internet Explorer, go up to Tools > Internet Options.
Then go to the last tab, Advanced, and scroll down to the Security
section. It is the fifth box down and make sure "Check for server
certificate revocation (requires restart)" is unchecked. Then access the
iTunes Store.

Wednesday 25 August 2010

Windows 7 : Show File types




  1. Open Folder Options by clicking the Start button Picture of the Start button, clicking Control Panel, clicking Appearance
    and Personalization
    , and then clicking Folder
    Options
    .



  2. Click the View tab, and
    then, under Advanced settings, do one of the
    following:



    • To hide file extensions, select the Hide extensions for known file types check box, and
      then click OK.



    • To display file extensions, clear the Hide extensions for known file types

Thursday 19 August 2010

Oracle : Auditing object access in 9i

  • Created new audit tablespace AUDIT

     CREATE TABLESPACE "AUDIT" DATAFILE 
    '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_01.dbf' SIZE 4096M REUSE ,
    '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_02.dbf' SIZE 4096M REUSE
    NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

  • Moved audit table - nb. original audit table is left in place

    create table audx tablespace "AUDIT" storage (initial 50k next 50k<br />pctincrease 0) as select * from aud$ where 1 = 2<br />rename AUD$ to AUD$$;<br />rename audx to aud$;<br />create index i_aud2;<br />on aud$(sessionid, ses$tid)<br />tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0)<br />

  • Turned on audit of all procedural code

    AUDIT EXECUTE PROCEDURE BY SESSION;<br />-- To remove<br />--NOAUDIT EXECUTE PROCEDURE;<br />

  • Turned on audit of all table access

    AUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION;<br />-- To remove<br />--NOAUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION;<br />

  • Review space usage carefully





















Peter Lawes
added a comment - 19/Aug/10
11:40 AM

  • Audit query

    with base_data as <br />(<br />select  <br />nvl(rtrim(decode(substr(ses_actions, 1,1),'S','ALTER/')<br />||decode(substr(ses_actions, 2,1),'S','AUDIT/')<br />||decode(substr(ses_actions, 3,1),'S','COMMENT/')<br />||decode(substr(ses_actions, 4,1),'S','D/')<br />||decode(substr(ses_actions, 5,1),'S','GRANT/')<br />||decode(substr(ses_actions, 6,1),'S','INDEX/')<br />||decode(substr(ses_actions, 7,1),'S','I/')<br />||decode(substr(ses_actions, 8,1),'S','LOCK/')<br />||decode(substr(ses_actions, 9,1),'S','RENAME/')<br />||decode(substr(ses_actions,10,1),'S','S/')<br />||decode(substr(ses_actions,11,1),'S','U/')<br />||decode(substr(ses_actions,12,1),'S','REFERENCES/')<br />||decode(substr(ses_actions,13,1),'S','X/') <br />,'/'),'?') action<br />,a.*<br />from DBA_AUDIT_OBJECT a<br />)<br />select <br />username,os_username,timestamp,owner,obj_name,<br />b.* <br />from base_data b<br />where 1=1<br />and   (owner                not in ('SYS','ODL_DBADMIN_DBO','XDB')<br />and   username             not in ('SYS','DBSNMP')<br />and   owner||'.'||obj_name not in ('SYSTEM.SQLPLUS_PRODUCT_PROFILE','SYSTEM.PRODUCT_PRIVS'))<br />or action like '%?%'
  • <br />order by b.timestamp desc<br />







Monday 2 August 2010

Excel : Remove all hyperlinks

In the spreadsheet
* Press Alt-F11 to open the VB macro
editor

* Select the required sheet

* Enter the following macro code

Sub ZapHyperlinks()

    Cells.Hyperlinks.Delete

End Sub




* Run the code - Green triangle or Menu->Run->Run Macro or F5
(possibly)
* This should remove all hyperlinks in the entire sheet


Monday 26 July 2010

Oracle : Impdp - objects that can be included/excluded

-- for database level export/import: 
SELECT named, object_path, comments 

  FROM database_export_objects 
 WHERE object_path NOT LIKE '%/%'; 


-- for table schema export/import: 
SELECT named, object_path, comments 

  FROM schema_export_objects 
 WHERE object_path NOT LIKE '%/%'; 


-- for table level export/import: 
SELECT named, object_path, comments 

  FROM table_export_objects 
 WHERE object_path NOT LIKE '%/%'; 

Thursday 22 July 2010

Oracle : Drop a temp tablespace temp file

 alter database tempfile '/u03/volnewdev7data/oradata/NEWDEV3/temp_02.dbf' drop including datafiles;

Wednesday 21 July 2010

Oracle : Password file

At command line
orapwd file=./PWDFILE password=password entries=10

In sqlplus:
select * from <a href="http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html#pwfile_users">v$pwfile_users</a>;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
<a href="http://www.adp-gmbh.ch/ora/misc/sys_system_internal.html">SYS</a> TRUE TRUE

<a href="http://www.adp-gmbh.ch/ora/sql/grant.html">grant</a> <a href="http://www.adp-gmbh.ch/ora/admin/sysdba.html">SYSDBA</a> to USERNAME;
<a href="http://www.adp-gmbh.ch/ora/sql/grant.html">grant</a> <a href="http://www.adp-gmbh.ch/ora/admin/sysdba.html">SYSOPER</a> to USERNAME;


Thursday 15 July 2010

Oracle : Remove 11.1 RAC cluster

Use cluster remove tool found :

http://download.oracle.com/otndocs/products/clustering/deinstall/clusterdeconfig.zip

This is a command line tool - unzip on node and de-install database home then crs home

./clusterdeconfig -home /u01/app/crs/11.1.0/crs -checkonly
./clusterdeconfig -home /u01/app/crs/11.1.0/crs

 ./clusterdeconfig -home /u01/app/oracle/product/11.1.0/db_1 -checkonly
 ./clusterdeconfig -home /u01/app/oracle/product/11.1.0/db_1


Need to run a 3 root scripts on each node

Wednesday 14 July 2010

Thursday 1 July 2010

CVS : Pre-tag check failure

Very annoying and a completely useless error message

This may be caused by a validation rule being set up on tags - eg. disallowing them to be moved/deleted
Look at ../CVS/CVSROOT/taginfo which matches items being tagged to an executable to validate the action
eg a line like
DEFAULT /usr/local/bin/stop_tag_hack.sh
will redirect to the shell script which can contain something like the following to stop deletes or changes :
TAG_OP=$2
if [ "$TAG_OP" != "add" ]; then
   exit -1
   # exit 0
fi

Monday 28 June 2010

Mysql : Reset root password

service mysql stop
mysqld_safe --skip-grant-tables &
mysql -uroot mysql
UPDATE user SET password=PASSWORD("abcd") WHERE user="root";
FLUSH PRIVILEGES;
mysql -uroot -pabcd mysql

Windows : Remove IE advanced security

* This is done by uninstalling the windows component Control Panel->Add or Remove Programs->Add/Remove Windows Components->Stuff

Monday 14 June 2010

RMAN : Simple restore of archive log to default location


$ORACLE_HOME/bin/rman nocatalog
connect target dba_cron/password

run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
restore archivelog sequence 214629;
restore archivelog sequence 214630;
}

Wednesday 26 May 2010

Windows : DesktopOk - desktop icon organizer that supports Windows 7 and 64bit

Seems good so far

http://www.softwareok.com/?Download=DesktopOK&goto=../Download/DesktopOK_x64.zip


Friday 7 May 2010

Outlook: Suppress notification popups

To turn this feature off, click Tools, Options, Email Options, and Advanced Email Options

Friday 30 April 2010

MS SQL: Expensive queries

  1. Use SQL Server Profiler (on the tools menu in SSMS) to create a
    trace that logs these events:



    • RPC:Completed
    • SP:Completed
    • SP:StmtCompleted
    • SQL:BatchCompleted
    • SQL:StmtCompleted

Also

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC




2. You can start with the standard trace template and prune it. You
didn't specify whether this was for a specific database or the whole
server, if it is for specific Db's, include the DatabaseID column and
set a filter to your DB (SELECT DB_ID('dbname')). Make sure
the logical Reads data column is included for each event. Set the
trace to log to a file. If you are leaving this trace to run unattended
in the background, it is a good idea to set a maximum trace file size
say 500MB or 1GB if you have plenty of room (it all depends on how much
activity there is on the server, so you will have to suck it and see).



  1. Briefly start the trace and then pause it. Goto
    File->Export->Script Trace Definition and pick your DB version,
    and save to a file. You now have a sql script that creates a trace that
    has much less overhead than running through the profiler GUI. When you
    run this script it will output the Trace ID (usually @ID=2);
    note this down.

  2. Once you have a trace file (.trc) (either the trace completed due
    to reaching the max file size or you stopped the running trace using



    EXEC sp_trace_setstatus @ID, 0



    EXEC sp_trace_setstatus @ID, 2



You can load the trace into profiler, or use ClearTrace
(very handy) or load it into a table like so:



<code><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> INTO </span><span class="typ">TraceTable</span><span class="pln"><br />FROM </span><span class="pun">::</span><span class="pln">fn_trace_gettable</span><span class="pun">(</span><span class="str">'C:\location of your trace output.trc'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">default</span><span class="pun">)</span><span class="pln"><br /></span></code>


Then you can run a query to aggregate the data such as this one:



<code><span class="pln">SELECT COUNT</span><span class="pun">(*)</span><span class="pln"> AS </span><span class="typ">TotalExecutions</span><span class="pun">,</span><span class="pln"> <br />    </span><span class="typ">EventClass</span><span class="pun">,</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">TextData</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">2000</span><span class="pun">))</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Duration</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DurationTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="pln">CPU</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">CPUTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Reads</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">ReadsTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Writes</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">WritesTotal</span><span class="pln"><br />FROM </span><span class="typ">TraceTable</span><span class="pln"><br />GROUP BY </span><span class="typ">EventClass</span><span class="pun">,</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">TextData</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">2000</span><span class="pun">))</span><span class="pln"><br />ORDER BY </span><span class="typ">ReadsTotal</span><span class="pln"> DESC<br /></span></code>


Once you have identified the costly queries, you can generate and
examine the actual execution plans.

From http://stackoverflow.com/questions/257906/how-can-i-log-and-find-the-most-expensive-queries



Windows : Trace recent rdp sessions

Install log parser

Create a bat file like (change server accordingly):
echo off
cls
c:
cd "c:\Program Files\Log Parser 2.2\"
logparser.exe file:TSLoginsDetails_More.sql?source=\\server\Security -o:DATAGRID

Create a sql file like :

SELECT
      timegenerated,
      EXTRACT_TOKEN(Strings,0,'|') AS User,
      EXTRACT_TOKEN(Strings,4,'|') AS ClientName,
      EXTRACT_TOKEN(Strings,5,'|') AS ClientAddress
FROM %Source%
WHERE EventID=682
ORDER BY timegenerated desc

Run the bat file as admin

Windows : Uptime

net stats srv

Thursday 29 April 2010

MS Sql : Ctas = SELECT INTO

In Oracle

create table T1 as select * from T1

The equivalent of that in Sql Server is SELECT INTO

select * into T2 from T1

Wednesday 28 April 2010

Windows : Audit recent RDP logins

If the login/logoff is being audited in group policy (default?)
  • Event log id 682 in the security log shows all log on events including
    client name and ip
  • Event log id 683 in the security log shows all log off events including client name and ip


Monday 26 April 2010

Excel: Worksheet splitter vb

Sub SalesmanToSheet()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To lastrow
If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then
iEnd = i
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = .Range("A" & iStart).Value
On Error GoTo 0
ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
With ws.Rows(1)
.HorizontalAlignment = xlCenter
With .Font
.ColorIndex = 5
.Bold = True
End With
End With
.Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
iStart = iEnd + 1
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thursday 22 April 2010

WIndows : Stop Robocopy trying forever

robocopy srcDir destDir /R:2 /W:10 /MIR'
retry and wait between attempts - default is 1million and 30 seconds

SQL Server: Listing sessions in 2000

SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid

SELECT DB_NAME(dbid) as 'Database Name', a.*
FROM master.dbo.sysprocesses a WITH (nolock)
WHERE dbid > 0

Thursday 11 March 2010

SQLServer : Object sizes Report

USE DEALHUB_ARCH1

select 'Database Name: ', db_name()
set nocount on
if exists(select name from tempdb..sysobjects where name='##tmp')
drop table ##tmp
create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))
go

declare @tblname varchar(50)
declare tblname CURSOR for select name from sysobjects where xtype='U'
open tblname
Fetch next from tblname into @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
insert into ##tmp

exec sp_spaceused @tblname
  FETCH NEXT FROM tblname INTO @tblname

END
CLOSE tblname

deallocate tblname
go

select nam Table_Name,rows Total_Rows,res Total_Table_Size,data Data_size,ind_sze Index_Size,unsed Unused_Space from ##tmp
drop table ##tmp



Monday 8 March 2010

Audacity : Recording from a sound card in Vista

  • Download and install Virtual Audio Cable (not freeware) - confirm when prompted
  • Taskbar -> Go to the playback devices (right-click the speaker icon) - Select the 'Playback' tab.
  • There will be a new option named 'Line 1' or 'VAC 1' etc
  • Make that 'Line 1' Default (button)
  • Close all programs (specially programs that have sound - itunes, realplayer, windows media player)
  • Then, run the program Audio Repeater (Included in the VAC install use start->all programs)
  • Set 'Wave in' to 'Line 1'
  • Set 'Wave out' to "SPEAKER" (or your computer's original default selection)
  • After this, click 'Start' tab below.
  • Then, turn on any music on the computer you should hear the sound from your speaker.
  • Right-click the speaker icon on your taskbar-> recording devices -> 'Recording' tab -> make 'Line 1' Default
  • Run Audacity -> Edit -> Preferences -> Audio I/O tab -> See 'Recording' and make 'device' into 'Line 1'


Friday 5 March 2010

Oracle Dataguard : dgmgrl - various commands

<b>Start dgmgrl
</b>
  • export ORACLE_SID=dbdg; . oraenv; dgmgrl
  • sys/<password>
<b>Check the configuration status<br /></b>
  • SHOW CONFIGURATION;
  • SHOW DATABASE VERBOSE 'North_Sales'
  • SHOW DATABASE 'North_Sales';
  • SHOW DATABASE 'North_Sales' 'StatusReport';
  • SHOW DATABASE 'North_Sales' 'InconsistentProperties';
  • SHOW DATABASE 'North_Sales' 'InconsistentLogXptProps';
Change properties
  • EDIT DATABASE 'DR_Sales' SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';
  • EDIT DATABASE 'DR_Sales' SET STATE='READ-ONLY';
<b>Create/enable configuration<br /></b>
  • CREATE CONFIGURATION 'DRSolution' AS PRIMARY DATABASE IS 'North_Sales' CONNECT IDENTIFIER IS North_Sales.foo.com;
  • ADD DATABASE 'DR_Sales' AS CONNECT IDENTIFIER IS DR_Sales.foo.com MAINTAINED AS PHYSICAL;
  • SHOW CONFIGURATION;
  • ENABLE CONFIGURATION;
<b>Stop transmit of dbs from primary <br /></b>
  • EDIT DATABASE North_Sales SET STATE=LOG-TRANSPORT-OFF
<b>Temporarily stop broker managing and monitoring a standby database.<br /></b>
Switchover
  • SWITCHOVER TO DR_Sales;
  • SHOW CONFIGURATION;
<b>Manual Failover<br /></b>
  • FAILOVER TO "DR_Sales";
  • SHOW CONFIGURATION;



Saturday 27 February 2010

SQL Server : Roll the Activity log

Run sp_cycle_errorlog


Saturday 13 February 2010

Redhat : Up2date crib

up2date --showall  | grep libaio
up2date download unixODBC-devel
up2date download unixODBC-devel--arch-i386
up2date --install unixODBC-devel


Thursday 11 February 2010

MS SQL : List all tables and columns

SELECT   SysObjects.[Name] as TableName,  
    SysColumns.[Name] as ColumnName,  
    SysTypes.[Name] As DataType,  
    SysColumns.[Length] As Length  
FROM  
    SysObjects INNER JOIN SysColumns  
ON SysObjects.[Id] = SysColumns.[Id]  
    INNER JOIN SysTypes 
ON SysTypes.[xtype] = SysColumns.[xtype] 
WHERE  SysObjects.[type] = 'U'
and SysColumns.name like '%ap%'
ORDER BY  SysObjects.[Name]

Wednesday 10 February 2010

Vmware : Direct shortcut to a Vsphere machine

D:\Program Files\VMware\Infrastructure\Virtual Infrastructure Client\4.0\vmware-vmrc.exe"  -h 192.168.69.42 -u root -p PASSWORD  -d "[datastore1] Ninefingers2/Ninefingers2.vmx" -X

-h Vsphere host
-u User
-p Passord
-d Datastore path to vmx file
-X Cant remember


Oracle Dataguard : Bring standby in line using rman

* ON STANDBY
{noformat}
sqlplus / as sysdba
SQL>startup mount
SQL>select current_scn from v$database;

CURRENT_SCN
-----------
  151214061
{noformat}

* ON LIVE
{noformat}
export ORACLE_SID=FOSDB
. oraenv
$ORACLE_HOME/bin/rman nocatalog
RMAN>connect target DBA_CRON/DBA_CRON
RMAN>backup incremental from scn 151214061 database format '/b01/volbackupfos/FOSDB/201002/ForStandby_%U' tag 'FORSTANDBY';
RMAN>backup current controlfile for standby format '/b01/volbackupfos/FOSDB/201002/ForStandby_ctl.bkp';
scp * oracle@slofosdb01:/u03/volfoslivedata01/oradata/201002
{noformat}
OR - actually did a full backup and transfer
{noformat}
RMAN>backup full database format '/b01/volbackupfos/FOSDB/201002/ForStandby_%U' tag 'FORSTANDBY';
RMAN>backup current controlfile for standby format '/b01/volbackupfos/FOSDB/201002/ForStandby_ctl.bkp';
scp * oracle@slofosdb01:/u03/volfoslivedata01/oradata/201002
{noformat}

{noformat}
* Must stop dataguard or recovery complains cannot get unique lock on files
sqlplus / as sysdba
SQL> alter database recover managed standby database cancel;
{noformat}

* ON STANDBY
{noformat}
$ORACLE_HOME/bin/rman nocatalog
RMAN> connect target DBA_CRON/DBA_CRON
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore standby controlfile from '/u03/volfoslivedata01/oradata/201002/ForStandby_ctl.bkp';
RMAN> shutdown immediate;
RMAN> startup mount
RMAN> -- Catalog the copied files
RMAN> catalog start with '/u03/volfoslivedata01/oradata/201002/'


RMAN> restore database;
RMAN> recover database noredo;
RMAN> exit
{noformat}

{noformat}
sqlplus / as sysdba
SQL>startup mount
SQL> alter database recover managed standby database disconnect;
{noformat}

* Needed to recreate STANDBY LOGFILEs as named differently from source db
{noformat}
sqlplus / as sysdba
SQL> alter database recover managed standby database cancel;
Database altered.

select 'alter database drop standby logfile group ' group# ';' from v$standby_log;
--Run generated script

--This plus others
ALTER DATABASE ADD STANDBY LOGFILE '/u02/volfoslivelogs01/oradata/FOSDGFOSDB_1srl2.f' SIZE 100M REUSE;
{noformat}

sqlplus / as sysdba
SQL>startup mount
SQL> alter database recover managed standby database disconnect;
{noformat}

Oracle Dataguard : Check status from SQL

--On secondary
select current_scn from v$database

--On primary
select scn_to_timestamp(287481398) from v$database
union all
select scn_to_timestamp(current_scn) from v$database;

If too far apart may need to force log switch on primary

ALTER SYSTEM SWITCH LOGFILE;

Also see
select * from  V$DATAGUARD_STATUS;

Friday 5 February 2010

Oracle : Rolling sequences forward

declare
dummy number;
begin
    for sq in (
    select sequence_owner||'.'||sequence_name nm
    from dba_sequences
    where sequence_owner
       in ( 'CLIENTDBO','DEALERTOOLS_DBO','GAINDBO','IT_JOB_LOG','MXDBO','MYBROKERDBO',
            'NEWTOPSDBO','ODFSDBO','ODL_DBADMIN_DBO','ODL_DBSERVICES_DBO','ODL_USERAPPS_DBO',
            'PRODUCTDBO','RECONCILIATIONSDBO','SETTLEMENTSDBO','USER_ADMIN_SYS','WEB')
            )
        loop
        dbms_output.put_line(sq.nm);
        for i in 1..10000 loop
           execute immediate 'select  '||sq.nm||'.nextval from dual' into dummy;
        end loop;
    end loop;
end;


Thursday 28 January 2010

Powershell : Get inner errors for more info (sometimes)

Statement that fails
#                        Trap {
#  write-output "herea"
#  $_.Exception
#  $err = $_.Exception
#  while ( $err.InnerException )
#    {
#    $err = $err.InnerException
#    write-output $err.Message
#    };
#    continue
#  }


Wednesday 20 January 2010

Powershell : IUnable to install CTP3 - Access Denied Error

Reason was a anti-confickr strategy of adding a permission to stop values being changed in a registry key :
HKLM, Software\Microsoft\Windows Nt\CurrentVersion\Svchost

This has advanced acl perms set to Deny Everyone Set Value



Tuesday 12 January 2010

Powershell - Performance Diagnostics reports error

When using powershell to query performance counters (via New-Object Diagnostics.PerformanceCounter) getting an errors (The network path was not found)

Fix - Assuming wiondows firewall on the target machine is turned on enable File and Printer Sharing in the exceptions on the target