Category: Oracle

The Oracle SCN issue

Oracle-database uses a large number (‘SCN’) as a time-stamp for referential-integrity, backup/restores, patch levels etc etc. When instances are linked the highest SCN number overwrites all others.

Trouble is … a ‘warning limit’ is being reached (three quarters of the actual limit). And when exceeded Oracle-database assumes its corrupt and becomes unstable.

But wait! There’s a ‘fix’, a patch which lets you manually set the SCN back below the soft-limit. BUT this needs to occur on all linked instances within a short period, or they are just updated from the one with the highest number (dowh!).

And Finally … the fix is only available for the latest version of Oracle. So an obscure, forgotten, out-of-date, instance in the back-of-beyond can bring down hundreds of shiny new machines (‘patched’ or not) until upgraded.

‘Oracle SCN management’ then, not a disaster just another ongoing DBA task 🙂

SSH Authentication

To login to a customers remote Oracle/AIX server – without any passwords being exchanged – I downloaded puttygen. Entered a pass-phrase,  and generated matching public and private keys.

On my desktop I had two text-files called ‘public’ and ‘private’. I emailed the public one to the customer. And when they had authorized it, pasted the private one to a file on the remote machine (called ‘id_rsa.ppk’ if I recall).

I configured putty with the username/host (eg: oracle@127.0.0.1), and in the SSH/auth box browsed to the ppk file. I saved my settings and exited.

When I started putty again I loaded the profile for this host and checked everything had been saved correctly, then connected.

I was logged in as ‘oracle’ and asked for my original pass-phrase. And thats all there was to it :-))

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-00571:

===========================================================

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

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

RMAN-00571:

===========================================================

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:

3

…

… 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.


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 ‘./ exp_gems.sh’.

Thirty minutes later the request was complete.

Continue reading Export of GEMS database

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';

USERNAME PASSWORD
-------- ----------------
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';

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
/