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 🙂
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: email@example.com), 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 :-))
To locate tnsnames … echo $TNS_ADMIN.
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
… 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> rman target /
rman> crosscheck archivelog all;
This prompted me to a) resync all instances on the server, and b) run a full backup on each.
And old gotcha 🙂
To test a ‘sys’ password without getting a false positive from your o/s credentials …
First check with a wrong password …
oracle> sqlplus “sys/wrongpw@sid as sysdba”
… then with the right password
oracle> sqlplus “sys/rightpw@sid as sysdba”
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.
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
Within UNIX you can ‘echo $ORACLE_SID’, or from sqlplus use ‘select instance_name from v$instance;’.
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';
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';
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$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p