Dataguard gap check

Thanks to E for this manual check that dataguard is updating a standby machine.

On Primary

alter system switch logfile;
select * from v$log;
Record the current Sequence#

On Standby

select process, sequence#, status from v$managed_standby;
Check RFS & MRP0 values match, and are related to the primary Sequence#.

Disk Space Used (GB)

I cobbled together this script from bits on the internet. It works fine, but needs OLE Automation to be enabled on the server. Should you wish to do this, use the Surface-Area configuration tool.

— “DiskSpaceUsed.sql” eg: C-Drive 65.3GB used
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint

SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate ‘Scripting.FileSystemObject’,@fso OUT
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur


EXEC @hr = sp_OAMethod @fso,’GetDrive’, @odrive OUT, @drive
EXEC @hr = sp_OAGetProperty @odrive,’TotalSize’, @TotalSize OUT
UPDATE #drives

SET TotalSize=@TotalSize/@MB
WHERE drive=@drive

CLOSE dcur
EXEC @hr=sp_OADestroy @fso

cast((TotalSize – FreeSpace)/1024.0 as decimal(4,2)) as ‘Used(GB)’
FROM #drives
ORDER BY drive
DROP TABLE #drives

A future proof backup plan.

Yup, I’m really getting to like SQL 2008. How’s this for two backup plans? The first completes a full backup of every database (system included) every 1am. The second backs up the transaction-logs of every database every 4 hours, from 00:45.

The beauty is, plan-1 will automatically incorporate any new databases that the developer cares to create. Similarly plan-2, backs-up every database’s transaction log, ignoring those in Simple recovery mode.

First look at SQL 2008

My first look at sql 2008 (standard edition, 64 bit, on Windows 2008) was … mixed.

First thing I wanted to do was set-up a full daily on-disk backup. I found I was unable to backup to a remote server IE: ‘\[ipaddress]backup$’, as I would get ‘access denied’. Further, I was then unable to edit the maint-plan as the Modify option from the context menu didn’t seem to work. For now I just deleted the plan and made a new one that backed up locally.

On the brighter side I like the ‘activity monitor’ with the row of graphs at the top. Influenced by Oracle me thinks.

Additionally I found the new object-explorer-details panel a joy. Although its really just a re-working of the old sql2000 ‘Taskpad’ view.

So, my initial impression of sql2008 is that its fresh, reflective of current fashion, and a bit buggy.

RMAN-06059: expected archived log not found

The customer alerted us that the scripted RMAN backups for smstrain were failing. I confirmed this by looking in the log …

oracle> cd /mserv/oracle/logs/RMAN

oracle> ls -lrt *smstrain*

oracle> pg sms_rmanhot_smstrain_Thu.log

Starting backup at 04-JUN-09 05:10:28

current log archived

released channel: t1



RMAN-00569: =============== ERROR MESSAGE

STACK FOLLOWS ===============



RMAN-03002: failure of backup plus archivelog

command at 06/04/2009 05:14:05

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

ORA-19625: error identifying file /dbsms/smstrain/oradata/smstrain/archive/arch_641734909_1_6050.arc

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information:


… which seems to say that an expected archive-log was not found. I guess another backup deleted it, or perhaps it was ‘tidied away’ to save space.
The fix was to manually resync rmans catalog for the instance …

oracle>  .oraenv

oracle> smstrain

oracle> rman target /

rman> crosscheck archivelog all;

rman> exit

This prompted me to a) resync all instances on the server, and b) run a full backup on each.

Manual Removal of Oracle9i from Solaris

I was asked to remove an unused Oracle 9206 database from a Solaris server. The first thing was to make a backup – because you never know 🙂

The database needed to be mounted so I could list all the files.

Select name from v$datafile;
Select name from v$controlfile;
Select member from v$logfile;

After ‘shutdown abort’ the unix administrator backed up these files.

I then deleted the same files, and hashed this database entry from listerner.ora and tnsnames.ora.

Upgrading SQL 2005 Express to SQL Server 2005 Standard

Having completed building a new application a customer wanted to upgrade there back-end database from SQL Express to full SQL Server 2005 for licencing issues.

Here’s my walk through …

Having already installed IIS and navigated within a command-prompt to the media (cd “D:AppsSQL 2005Disk 1”). I ran “setup.exe SKUUPGRADE=1”.

I accepted the licencing conditions, and continued installing components

1) SQL Server Database Services

2) Integration services

(resisting the temptation to re-assign the datafiles from the small C-Drive to the larger D-Drive), until Getting to the ‘Instance Name’ screen where I clicked the ‘Installed Instances’ button.

The next screen confirms the SQL Express instance name, I clicked ‘OK’, then with the instance-name autocompleted clicked Next.

In the ‘existing components’ screen I ticked the one box to request an upgrade and clicked ‘next’.


The resulting upgrade is not obvious, however it can be confirmed my right-clicking on the server in enterprize manager and examining the version.

Following on from this SP3 should be applied.

Export of GEMS database

To action the request to create a full daytime export on the GEMS database on LSC3. I first deleted the previous export from ‘/tmp/exports’ to create the needed 7.7GB.

Then I navigated to ‘/ad00/orabase/admin/scripts/exports’ and checked there was a directory called ‘exp_gems_logs’. As GEMS is quite small, and the customer wants a ‘daytime’ export so not to interfere with the evening backups, I ran the export at the command line with ‘./’.

Thirty minutes later the request was complete.

Continue reading Export of GEMS database

Export table and index DDL

As part of a project to reduce space within a cloned database it was decided that a large table could be excluded from the regular scripted copy.
I needed to extract the tables definition from the master, to create an empty version on the clone after the copy was complete.

Being Oracle version, I didn’t have to use the ‘exp’ command (the output of which needs a lot of tidying), but could instead use the <b>’get_ddl'</b> function of the <b>’dbms_metadata'</b> package – like so …

— Script to export the definition of table PROC_FIL and its index.

— Remember to add semi-colons, amend ‘storage (initial 1k next 1k minextents

— 1 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default);

— for example, and fix broken lines.
set heading off;

set echo off;

set pages 999;

set long 90000;
spool ddl_pf.sql

select dbms_metadata.get_ddl(‘TABLE’,’PROC_FIL’,’KIS’) from dual;

select dbms_metadata.get_ddl(‘INDEX’,’PF_PK’,’KIS’) from dual;

spool off;

Reinstalling HPOV to stop it freezing.

After a while using HPOV over a slow network the application may freezes when I try to perform any action that spawnes a new window (eg: ‘Closure Code’).

The cure is to re-install, but its not too difficult.

1) take copies of srv.DAT and Views.DAT from ‘C:Documents and Settingsrichard smithApplication DataHewlett-PackardOpenViewService Desk’

2) delete the folder ‘C:Documents and Settingsrichard smithApplication DataHewlett-PackardOpenViewService Desk’.

3) start up hpov and click ‘next’ within the connection wizard

4) paste the server name from srv.DAT (eg: ’′), into the text-box.

5) type name and password (eg: ‘Richard Smith’, ‘password’).

6) type anything for the friendly name (eg: ‘hpov’), then click finish.

7) before loging-in copy the Views.DAT back into the folder deleted in step-2

8) login as normal (there will be an unusually long delay first time)

How to change an Oracle users password temporarily?

How to change an Oracle password temporarily?

In Oracle it is possible to change a password temporarily. This can be useful for DBAs who wish to act as a different user.

SQL> select username,password from dba_users where username='SCOTT';

-------- ----------------
SCOTT F894844C34402B67

Save the hash of the password somewhere safe. Then change it …

SQL> alter user scott identified by tmppassword;

Login with the credentials: scott/tmppassword, then after doing your work change the password back by using an undocumented feature called “by values”

SQL> alter user scott identified by values 'F894844C34402B67';

Oracle Financials ‘Active Users’ report.

To get a report of Active Users for a customer I
– logged in as ‘sysadmin’.
– Started Systems Administrator
– Chose Requests / Run / Single Request
– Typed ‘active’ in the Name field, and clicked Submit.
– Chose Active Users, ok and ok again.
– Clicked Refresh Data until I found the ‘Active Users’ job complete …
– Chose View Output, then Tools / Copy File, File / Save as
– Chose the location as h-drive (a shared drive) / Sequent / ANN (in this case) and clicked Save.
– I exited Oracle Financials
– Typed ‘net use’ at a command prompt to find the path to the h-drive
– And emailed the file’s name and location to the customer (eg: \NW-GIR-TSTSSHARESequentANN)

Tablespace has only 1% free

My daily checks reveiled that tablespace D_LARGE3 had only 1% free (NB: these are manual extend).
I listed the current datafile’s to see the path and name, and to see how large the current ones were …

<code>col tablespace_name for a20

col file_name for a40

select tablespace_name, file_name, bytes/1024/1024

from dba_data_files

where tablespace_name = ‘D_LARGE3’

order by file_name;

TABLESPACE FILE_NAME                                BYTES/1024/1024———- —————————————- —————D_LARGE3   /db010/oradata/CIX/d_large3_01.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_02.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_03.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_04.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_05.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_06.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_07.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_08.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_09.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_10.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_11.dbf                  1985

TABLESPACE FILE_NAME                                BYTES/1024/1024———- —————————————- —————D_LARGE3   /db010/oradata/CIX/d_large3_12.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_13.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_14.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_15.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_16.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_17.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_18.dbf                  1985D_LARGE3   /db013/oradata/CIX/d_large3_19.dbf                  1000</code>

… and noticed the last one was only 1GB in size. Therefore I didn’t need to add a new datafile, just expand it …

<code>alter database datafile ‘/db013/oradata/CIX/d_large3_19.dbf’ resize 2000m


I confirmed this had worked with the ts-free script (attached) …

<code>column owner format a10

column “%free” format 999

select a.tsname, “Tot/Kb”, – “Used/Kb”, “Free/K”, ( * 100) / “%free”


(select tablespace_name tsname, sum(bytes)/1024 total

from dba_data_files

group by tablespace_name ) a,   (select tablespace_name tsname, sum(bytes)/1024 free     from dba_free_space     group by tablespace_name ) b   where a.tsname = b.tsname(+)/
TSNAME                             Tot/Kb    Used/Kb     Free/K %free—————————— ———- ———- ———- —–D_LARGE1                         58312704   56477696    1835008     3D_LARGE3                         56929280   56142848     786432     2</code>
job done :-))
btw <a href=”/node/179″>here’s</a> a similar incident were I had to add a datafile

How big is the current Oracle instance?

Here’s some code to report the size or the current Oracle instance …

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p

Useful Windows commands

This will be my repository for tried-and-tested useful Windows commands …

1) This is my perpetual alarm-lock whilst working-at-home during the week. Stops me clock-watching, and lets me know when I’m doing overtime. (Don’t forget to check the messenger-service is started).

at 17:00 /every:M,T,W,Th,F net send “richard” Its Five O’Clock lol

2) In the path (EG: C:Windows) I create a file ‘remindme.cmd’ containing the code …

at %1 net send “richard” %*

… then at some time in my busy future I can set a simple, robust, reminder (robust as I don’t need anything running – like an email client). I just open a command prompt and type something like ‘remindme 13:58 call joe for lunch’.
(Oh, and don’t forget to check the messenger-service is working).

MySQL monitoring tools

“AjaxMyTop” is a really useful MySQL monitoring tool.

If you’re on a web-server, and therefore have apache and mysql readily available you can easily implement a live web-page version of the Unix ‘top’ command.
– Download and unpack the ‘ajaxMyTop’ folder into htdocs.
– Configure the config.php file with user (root?), and password
– Then browse to http://localhost/ajaxMyTop/ and wait.

After a few seconds you should at-the-least see a line for ‘show processlist’ generated by the tool.

Testing Apache, PHP, and MySQL.

Here’s the simplest test-page I can think of that will test …
– the supplied credentials
– PHP can connect to MySQL
– Apache can display the results

$result = mysql_query(“show databases”);
echo “<h1>Databases this user can connect to …</h1>”;
while ($row=mysql_fetch_array($result)) echo($row[“Database”].”<br />”);

Paste it into a file called ‘test.php’ (with the correct password) in Apache’s ‘htdocs’ folder, then browse ‘http://localhost/test.php&#8217;.