Thinking Out Loud

April 27, 2010

Email password change from Oracle

Filed under: oracle — mdinh @ 8:32 pm

I get requests for password changes frequently.

I would try to figure out a password, change the password, and email the password to the user.

This has become a bit cumbersome.

CREATE OR REPLACE PROCEDURE change_pw (pi_user VARCHAR2)
IS
c_profile CONSTANT VARCHAR2(30) := 'PROFILE_NAME';
c_domain CONSTANT VARCHAR2(30) := '@domain.com';
c_sender CONSTANT VARCHAR2(50) := SYS_CONTEXT ('USERENV', 'DB_NAME')||c_domain;
c_recipients CONSTANT VARCHAR2(30) := REGEXP_SUBSTR(pi_user,'[^_]+',1,2)||c_domain;
c_cc CONSTANT VARCHAR2(30) := 'dba'||c_domain;
c_subject CONSTANT VARCHAR2(50) := 'Password Changed For: '||pi_user;
c_pw CONSTANT VARCHAR2(10) := DBMS_RANDOM.STRING ('A', 9) || TRUNC (DBMS_RANDOM.VALUE (0, 10));
c_message CONSTANT VARCHAR2(50) := 'New Password: '||c_pw;
l_sql VARCHAR2(100);
BEGIN
FOR x IN (
SELECT u.name
FROM sys.user$ u, sys.profname$ p
WHERE u.resource$ = p.profile# AND p.name = c_profile
AND u.name=UPPER(pi_user))
LOOP
l_sql := 'ALTER USER '|| x.name || ' IDENTIFIED BY ' || c_pw || ' ACCOUNT UNLOCK PASSWORD EXPIRE';
EXECUTE IMMEDIATE(l_sql);
UTL_MAIL.SEND(sender=>c_sender,recipients=>c_recipients,cc=>c_cc,subject=>c_subject,message=>c_message);
END LOOP;
END;
/

The above stored procedure allows me to create a random password and emails the user.

You can find article – How To Send Email From 10g Oracle Database (UTL_MAIL) – open in new window

Advertisements

April 7, 2010

logical corruption

Filed under: oracle — mdinh @ 4:09 am

From what I have been told, when there are data beyond the 2nd arguments in ORA-600, there is possibility for corruption.

I have later found out the above statement is incorrect.

ORA-00600: internal error code, arguments: [kcbgtcr_12], [1], [373070], [1048578]

ORA-08103: object no longer exists

The above errors were raised by SELECT COUNT(*) FROM TABLE.

analyze table <table_name> validate structure cascade
*
ERROR at line 1:
ORA-08103: object no longer exists

After excuting RMAN> backup validate check logical database;

The following errors were in the alert log

Mon Apr  5 12:56:23 2010
Error backing up file 78, block 16026: logical corruption

This can be verified by querying V$DATABASE_BLOCK_CORRUPTION and V$BACKUP_CORRUPTION

OPS$ORACLE@db08:PRIMARY> set serverout on
OPS$ORACLE@db08:PRIMARY> exec print_table(‘select * from v$backup_corruption’);
RECID                         : 1
STAMP                         : 715270236
SET_STAMP                     : 715270042
SET_COUNT                     : 13931
PIECE#                        : 1
FILE#                         : 78
BLOCK#                        : 16026
BLOCKS                        : 1
CORRUPTION_CHANGE#            : 10488120506547
MARKED_CORRUPT                : YES
CORRUPTION_TYPE               : LOGICAL
—————–
RECID                         : 2
STAMP                         : 715528352
SET_STAMP                     : 715528177
SET_COUNT                     : 13986
PIECE#                        : 1
FILE#                         : 78
BLOCK#                        : 16026
BLOCKS                        : 1
CORRUPTION_CHANGE#            : 10488120506547
MARKED_CORRUPT                : YES
CORRUPTION_TYPE               : LOGICAL
—————–

Note: Oracle support asked me to use DBVerify; however, I don’t trust it after reading the article below from some time ago.

http://wedonotuse.blogspot.com/2007/02/dbverify-i-think-not.html

Besides, doesn’t RMAN do the job?

Here are my attempts at resolving the logical corruption.

Dropping the table with the logical corruption did not help. We had this luxury, but you might not.

Oracle support suggested using blockrecover. However, there are limitations, see below.

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta010.htm

I did forget to mention that we don’t use RMAN for backup. Let’s not go there.

Also, from Tom Kyte’s post – http://bit.ly/8Ye15N

in order to perform a block level recovery YOU MUST have used rman and be using rman, otherwise this feature is not available.Reason 4,532,543 to use RMAN as part of your backup and recovery solution.

You can only do a datafile restore, rollforward as an offline operation if you are not using RMAN.

Oracle support did suggest the following – How To Format Corrupted Block Not Part Of Any Segment ORA-19566 Note: 336133.1

If you don’t have access to Metalink, the you can find the document here – http://oracle-abc.wikidot.com/how-to-format-corrupted-block-not-part-of-any-segment

It works!

No more logical corruption.

Verify by executing RMAN> backup validate check logical datafile <FILE#>

However, you would need to querry V$DATABASE_BLOCK_CORRUPTION because V$BACKUP_CORRUPTION will still show the corruptions from previous backup.

It was a learning experience for me and hope it helps you should you ever encounter logical corruption.

April 5, 2010

Duct Tape

Filed under: Uncategorized — mdinh @ 3:52 pm

Over the weekend, I read two blog posts and could not help but chuckle slightly because the timing is just right and coincides to what I have been saying.

You should read it and it has a funny example as well.

http://hoopercharles.wordpress.com/2010/04/03/battling-the-symptoms-or-addressing-the-root-cause/
http://blog.tanelpoder.com/2010/04/03/non-trivial-performance-problems/

Unfortunately, it’s harder to find the root cause and easier to treat the symptoms.

Duct tape is a great invention.

But what are we duct taping?



That’s a picture of my water heater and it’s a long story.

Blog at WordPress.com.