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;