Friday, October 8, 2010

11g SYSASM Role

Well well well, Oracle we hear you.

So, SYSDBA does not have privileges to administer ASM instance any more!!! OK fine we will start using SYSASM role to administer then...

/home/oracle:(+ASM)$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 8 13:50:43 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected.

SQL> shutdown

ORA-01031: insufficient privileges

Now try this:

SQL> conn / as sysasm

Connected.

SQL> shutdown

SQL> database dismounted.

As I said something to learn new everyday... things are never same...

Monday, August 9, 2010

Flashback in 1,2,3

No need to say what flashback means as we all seen atleast one movie that either takes us to our previous memories or the movie actors itself will go back in their memories!!!

Well, the same concept applies for Oracle Database also.
In simple terms, you just feel like to revert all your database changes to certain point of time before you either you messed up the data (well, by doing some kinda testing) or altering some structure of the database.

Or, can be used to prepare your database to restore to the point where you wanted to be after allowing other departments to work on the database.

OK, enough theory over here. Lets get to practical as to how easy it is:
Pre-Req:
1. Database needs to be running in Archive Log Mode (no need to be in archive log from the day one. But only when you want to start FLASHBACK till the time you are done with your FLASHBACK).
2. Some diskspace to record/log all the objects (that are only being modified) old images/copy while the database is in FLASHBACK mode.

Steps:
If the Database is already in ARCHIVELOG then move on to step #4:
1. shutdown immediate;
2. startup mount;
3. alter database archivelog;
4. alter system set db_recovery_file_dest_size=2G;
Note: 2G is to store all the modified object image copies before the modification is done.
5. alter system set db_recovery_file_dest='/location_where_you_want_the_FlashBack_Log_should be created';
6. alter database flashback on;
7. alter database open;
8. create restore point started_messing_it_up;
Note: remember the name that you are giving here. In this case it's "started_messing_it_up"
Let the users start using the database for a while now.

Once you are ready to Flashback/Re-Wind to the point where you wanted to be:
1. shutdown immediate;
2. startup mount;
3. flashback database to restore point started_messing_it_up;
Note: the name is the one that you have given at the time of creating the restore point.
4. alter database open resetlogs;
Now, your database is just like how it was before creating the restore point.

Tuesday, May 11, 2010

All Tables in Hierarchical Order (Tables in Parent Child order)

Here is the script that can be used to produce a nice Hierarchical View for all the tables with in the schema. (parent-children-grand children-grand grand children ... and continues).

select level, rpad( '*', (level-1)*2, '*' ) || table_name table_name
, pkey_constraint, fkey_constraint, r_constraint_name
from (select a.table_name, a.constraint_name pkey_constraint,
b.constraint_name fkey_constraint, b.r_constraint_name
from user_constraints a, user_constraints b
where a.table_name = b.table_name
and a.constraint_type in ( 'P','U')
and b.constraint_type = 'R'
union all
select table_name, constraint_name, null, null
from user_constraints
where constraint_type= 'P'
--TO COVER THE CHILDRENS WHO DONT HAVE EITHER PK OR UK
union all
select table_name, null, null, r_constraint_name
from user_constraints uc
where constraint_type = 'R'
and not exists (select 1 from user_constraints uc1
where uc1.table_name = uc.table_name
and uc1.constraint_type in ('P','U'))
)
start with fkey_constraint is null
--and table_name = 'SANTHOSHCHANNA' --un-comment this line and provide particular table name that you are looking for to get it child's.
connect by nocycle prior pkey_constraint = r_constraint_name

Wednesday, April 14, 2010

RMAN - Restore from backup - Step by Step

  1. Copy and Edit init.ora file (if init.ora cant be found or unavailable then, do strings on the controlfile from rman backup and make up init.ora)
  2. Create required folders like bdump, udump etc (that are referred in init.ora)
  3. Add entry into /etc/oratab file
  4. Add entry into /etc/tnsnames.ora file
  5. set . oraenv
  6. sqlplus / as sysdba
  7. startup nomount (use pfile= option if init.ora file is not located under $ORACLE_HOME/dbs folder)
  8. exit from sqlplus
  9. rman target / nocatalog (once connected to RMAN, pl. note that the “connected to target database:” is the one that you intended to connect and its in “(not mounted)” mode.
  10. set dbid= (find this value from the backup logs);
  11. restore controlfile from ‘full_path_here’;
  12. mount database;
  13. restore database;
  14. recover database;
  15. alter database open resetlogs;

if ORA-39700: database must be opened with UPGRADE option Then,

    1. sqlplus / as sysdba
    2. startup upgrade;
    3. run catupgrd.sql from $ORACLE_HOME/rdbms/admin Folder. To upgrade to 10.2 (latest version if the backup was from previous release)
    4. shutdown immediate
    5. startup
    6. run utlrp.sql from $ORACLE_HOME/rdbms/admin Folder to recompile any invalid objects.

Thursday, March 4, 2010

You develop new skills when you're down --Raghava KK

Raghava KK - Five Lives of an Artist:

Just watched this and yes I would recommend to watch this. Its worth watching such a creative and honest person speak.

http://www.ted.com/talks/raghava_kk_five_lives_of_an_artist.html


Wait Events - single-task message along with cursor: Pin S wait on X

Some thing or other to learn everyday...
Just got a call from one of our team stating that the application doesn't make connection to the database anymore!!!.
It just hangs.
Checked in the Database and don't see anything going on. Why is it hanging?

It was fine till yesterday... (as usual of-course. Everything starts fresh on first thing in the morning).

So, trying to figure out whats going on and looked into the sessions and found a new wait event(s)
"single-task message" on one session and "cursor: Pin S wait on X" on another session.

Good notes on resolving "cursor: Pin S wait on X" wait event was found in metalink with note id: 786507.1.

But that's not the issue that we facing...

Finally figured out that one of the DB-Link(s) that are used by Application are not valid anymore and thus the session is hanging (well "waiting").

Correct the DB-Link and all are happy... No more wait events...