Girlgeek's Oracle Blog
A few basic notes on Oracle Database Administration.
Thursday, June 19, 2008
Q.E.D.
DBA, "You are the only user on the DB, but you have a dozen sessions open. Can you close some sessions?"
User, "I can't see my sessions through the application. Bounce the database"
DBA, " I can see the sessions just fine. I'll kill your excess sessions."
User, "No, don't kill my sessions. Bounce the database. I'm bouncing the database."
We don't need no stinking controls around here.
Everyone can do everything.
He bounced the database.
User, "I bounced the database. My [one] session is running just fine."
Q.E.D. - bouncing the database improves performance.
==========================================================
An update to the post above:
The same user has informed me that I should always shutdown the database with "Shutdown abort" . "It works much better."
I have always assumed that everyone else knows more than I do.
Maybe that isn't true. Maybe I do understand more than some people...
Monday, June 16, 2008
Lessons Learned after a Hell Weekend
- A necessary part of any database project plan is a fall back plan. What is planned if there is a failure at any particular step.
- Backup - this includes enough space allocated for additional backups as needed.
- Part of any upgrade script should be rollback scripts.
I should have know that I was in trouble when on Friday afternoon when I was given a time line which was called 'optimistic' with no what-if forseen.
Wednesday, May 14, 2008
Northern California Oracle Users' Group Conference
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Copyright © 2008 NoCOUG. All rights reserved. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Friday, May 2, 2008
Moving back to a lost spfile
I then created an spfile from the pfile with only the dynamic parameters corrected.
create pfile from spfile;
But I was looking at an out of date article which put the had an error about the default location of the spfile.
SELECT name, value FROM v$parameter WHERE name = 'spfile';
gave me the actual location of my spfile, which being in the default location was in
$ORACLE_HOME/dbs/spfileSID.ora
Then bounced the database and brought it up with the spfile.
But, of course, the static parameters need a bounce to the database.
I altered the parameters scope = spfile eg:
ALTER SYSTEM SET processes = 300 SCOPE=SPFILE;
and then rebounced the database to apply the dynamic changes also to the current instance.
THEN I BACKED UP THE SPFILE!
(now if only I could control the fonts on this blog, all would be well.)
Saturday, March 29, 2008
Semafore? Cannot mount in Exclusive.
I had a Daylight Savings Patch to apply to 2 databases. I think that someone else, was blogging about installing this just recently. The patch is trivial - bring down the database, replace 2 files deep within ORACLE_HOME and bring the database back up. The first install went went off uneventfully, so I figured that I had an idea what I was doing. Then I moved to the second DB and the fun started. The DB wouldn't restart. How can I NOT startup a database? What can be simpler than a STARTUP?
ORA-01102: cannot mount database in EXCLUSIVE mode
Now that I have a solution it all seems simple.The problem according to Metalink... no, they are gone.
- there is still an "sgadef.dbf" file in the "ORACLE_HOME/dbs"
directory
nope
- the processes for Oracle (pmon, smon, lgwr and dbwr) still exist. and indeed
- shared memory segments and semaphores still exist even though the
database has been shutdown
I got to learn about the unix command ipcs -b, but
nothing owned by oracle,
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 1048576 0x7800000a --rw-rw-rw- root system 16777216
m 1048577 0x0d001213 --rw-rw---- root system 1440
m 3 0xffffffff --rw-rw---- root system 4096
T ID KEY MODE OWNER GROUP NSEMS
Semaphores:
s 3145728 0x010000af --ra------- root system 1
s 1 0x6200105e --ra-r--r-- root system 1
- there is a "ORACLE_HOME/dbs/lk" file
sculkget: failed to lock /home/oracle/orabase/product/10.2.0/dbs/lkSID exclusive
sculkget: lock held by PID: 299506
though I have yet to figure out what a sculkget is I did find a file that was
skulking around and didn't belong there.
With trepidation I killed the process holding the lock file
kill -9 299506
and removed the lock file
rm /home/oracle/orabase/product/10.2.0/dbs/lkSID
and magic! no more lock, Database starts up normally!
Locks in their place and all is well with the world.
Meanwhile, if anyone else knows what category of animal a
sculkget: message is - I would love to know. There is a singular lack of comment
about it on the web, except as part of this specific problem.
Thursday, March 27, 2008
Exposed Password in Data Pump
Then I remembered that in the last shop Data Pumps involved
- Temporarly change the system user password
- Run the Data Pump with the temporary password in plain text
- Changing the password back.
Does anyone have a suggestion for running Data Pump with a modicum of security? I had no say in the last shop. Here I may not prevail, but at least I'm in a position to make a suggestion if I have one.
Does the solution involve something with a password protected parameter file? Or? Thanks in advance for any suggestions offered.
Tuesday, March 4, 2008
When all else fails, follow instructions.
Today I tried to open my test database and got:
ORA-16038: log 3 sequence# 729 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'C:\ORACLE\ORA10\ORADATA\DB10\REDO03.LOG'
Researching the problem I found:
- Maybe your Oracle instance has reached the maximum db recovery file destination file size assigned to it? If that is so, probably your oracle instance will stop working and you will get the 3 error messages listed above while trying to start it.
- The relevant parameter is:
DB_RECOVERY_FILE_DEST_SIZE- specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the flash recovery area. - It works in concert with
DB_RECOVERY_FILE_DESTwhich specifies the default location for the flash recovery area. The flash recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.
- What is the maximum db recovery file destination file size assigned in my DB?
SQL> show parameter db_recovery_file_dest_size
db_recovery_file_dest_size 2G
At least this wasn't a surprise.
I removed excess files
I shutdown the database. I then removed the archivelog files and the flashback files from the file system – even though they didn't amount to the 2gb in total that the system is complaining about. (This is a play database so I can do things like kill these files.)
Still had a problem.
I brought the database up to mount state, but immediately there was a trace file message:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 99.85% used, and has 3320320 remaining bytes available.
*** 2008-03-03 10:03:19.635
************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.
The files were still being seen by RMAN! When all else fails, follow instructions, in this case #4 above. “4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.”
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
validation failed for archived log
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2008_03_03\O1
_MF_1_733_3WRQ14DY_.ARC recid=70 stamp=648389669
validation failed for archived log
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2008_03_03\O1
_MF_1_734_3WRQ4V7R_.ARC recid=71 stamp=648389787
What nice list of all the files that aren't there, and all the locations where they aren't; 68 files that don't exist! [apologies to any reader of English as a second language]
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
70 1 733 X 02-MAR-08 C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2
008_03_03\O1_MF_1_733_3WRQ14DY_.ARC
71 1 734 X 03-MAR-08 C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2
008_03_03\O1_MF_1_734_3WRQ4V7R_.ARC
(etc)
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2008_03_03\O1
_MF_1_733_3WRQ14DY_.ARC recid=70 stamp=648389669
deleted archive log
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DB10\ARCHIVELOG\2008_03_03\O1
_MF_1_734_3WRQ4V7R_.ARC recid=71 stamp=648389787
(etc)
Deleted 68 EXPIRED objects
Now all is well. (with the database if not with this blog-entry's numbers). I bounced the database and the alert log shows:
db_recovery_file_dest_size of 2048 MB is 0.38% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.


