Oracle DBA Videos

Oracle Database Patches

2017-11-22T04:42:00.000+05:30

This video gives an introduction to Oracle Database patches.

How to start a GUI session without VNC?

2012-01-24T01:54:00.001+05:30

a) Download & Install "XManager", You can download a free trial version from the internet.
b) Launch the Xstart and fill in the details as below:

It opens up a window

In the Xterm window, test if the GUI is working with "xclock" utility.

MAX SCN / High SCN vulnerability

2012-01-23T23:43:00.001+05:30

What is SCN?

This is a number that increments sequentially with every database commit and is crucial to normal Oracle database operation. The SCN is also incremented through linked database activities.

The Vulnerability?

Some customers expressed concerns that they may be getting closer to the current maximum SCN limit faster than the data processing they are doing would warrant.

What is the Max SCN?

Max SCN is not a hard limited number. The max scn constantly increases every second.

The current Max SCN limit is the number of seconds elapsed since 1988, multiplied by 16,384. So the max scn keeps increasing constantly.

What is SCN Headroom?

The difference between the current SCN the database is using, and the current MAX SCN, is known as the SCN headroom. For almost all Oracle Databases, this headroom is constantly increasing every second.

What is the SCN Bug we are talking about?

Oracle has determined that some software bugs could cause the database to attempt to exceed the current maximum SCN value (or get closer to the limit than was warranted).

Example: When a database “A” (whose current SCN is lets say 10) has a db link to database “B” (whose current SCN is lets say 100). When database “A” attempts to do a DML activity on one of the tables in database “B” via a DB Link, then Database “B” SCN (which is higher than A’s) is reflected in Database “A”.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

www.oracledatabase12g.com

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

SQL> select current_scn from v$database@PROD;

CURRENT_SCN

-----------

SQL> insert into testlink@PROD values(1);

1 row created.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

b) There is also another bug found – where hot backups (alter database begin backup) causes SCN numbers to jump extremely high

Oracle released a patch to fix the SCN growth rate bug in hot backup listed as 12371955: “High SCN growth rate from ALTER DATABASE BEGIN BACKUP in 11g.”

What happens if a database does hit the Max SCN Limit?

Generally if the database does try to exceed the current maximum SCN value, the transaction that caused this event would be cancelled by the database, and the application would see an error. The next second the limit increases, so typically the application then continues with a slight hiccough in processing. However, in some very rare cases, the database does need to shut down to preserve its integrity. In no cases is data lost or corrupted.

How to check if a database is close to hitting MAX SCN?

Instructions in metalink doc:

Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script [ID 1393363.1]

Fix?

Apply the Jan 2012 CPU Patch. Customers that have applied the fixes find that their SCN headroom starts to increase again.

How to enable tracing based on the type of clients connecting to database ?

2011-05-07T01:00:00.006+05:30

Our applimation application was having performance issues. The application team engaged the DBAs to find out why the steps involving database operations were taking a long time. The 1st thing I had to do was enable tracing on these sessions to dig more into the problem. However, I noticed that in each step of its execution - the application spawned multiple database sessions. So the traditional

EXECUTE dbms_system.set_sql_trace_in_session (24,13,TRUE); would not be of much use.

I had two options in hand - a) to execute a logon trigger to enable tracing at user level (The application always connect to the database using the same user - lets say 'INFRA_USER').

b) Enable tracing based on
i) Service, Module, and Action
ii) Client Identifier

I like the second approach.. than dealing with logon triggers.

So here is what I did..

1) First identify the Client ID, service, modules, action etc.

SQL> select sid, username, SERVICE_NAME,CLIENT_IDENTIFIER , module, action from v$session where username like 'INFRA_USER%'
2 ;

SID USERNAME SERVICE_NA CLIENT_IDENTIFIER MODULE ACTION
------ ------------ ---------- ------------------------- ------------------ ----------
2192 INFRA_USER TEST applmatn:14.296.88.155 JDBC Thin Client

SQL>

I can either enable tracing using the " Service, Module, and Action" or using "Client Identifier". There were several other sessions from different applications having module as "JDBC Thin Client" and SERVICE_NAME = TEST (The SID Name). So to uniquely identify my session - I used the CLIENT_IDENTIFIER instead which is "applmatn:14.296.88.155".

2) Enable tracing on the sessions having client ID applmatn:14.296.88.155

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'applmatn:144.226.90.35', waits => TRUE, binds => TRUE);

3) Disable tracing

Once you have sufficient information/or the problamatic query has been run, Disable tracing.

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'applmatn:144.226.90.35');

4) Use trcsess utility to consolidate all trace files generated and then use tkprof to analyze the trace file.
More info can be found here

How to give responsibilites of one user to another in Ebusiness Suite?

2011-02-11T03:04:00.005+05:30

Recently I came across a requirement where I had to create a user (lets say NEW_USER) similar to an existing user (lets say FINANCE_USER). After creating the new users, I realized that the FINANCE_USER had over 100 responsibilities - which had to be given to 35 new users. Assigning responsibilities one by one seemed like a mountainous task.

So here is how we can assign responsibilities using FND_LOAD:

1) Download using FNDLOAD
FNDLOAD apps/appspassword 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct SOURCE_USER.ldt FND_USER USER_NAME='SOURCE_USER'

2) Copy the LDT file to NEW_USER.ldt
cp SOURCE_USER.ldt NEW_USER.ldt

3) Edit the NEW_USER.ldt file to change the below entries:

BEGIN FND_USER "NEW_USER"
EMAIL_ADDRESS = "New_Users_Email_id"

4) Upload using FNDLOAD
FNDLOAD apps/appspassword 0 Y UPLOAD @FND:patch/115/import/afscursp.lct NEW_USER.ldt FND_USER USER_NAME='NEW_USER'

How to retrieve workflow mailer password?

2010-08-09T07:20:00.008+05:30

The workflow mailer password, SMTP address and other configurations related to Workflow are stored in $XDO_TOP/resource/xdodelivery.cfg
So, if you ever loose your workflow mailer password - this is where you can find it..

Convert database from NON-ASM to ASM

2010-02-21T01:15:00.013+05:30

CONVERTING DATABASE FROM NON-ASM to ASM
=======================================

1) Backup the database.

2) Shut down and startup the database in nomount mode.

$ sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 1 12:52:44 2010

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2127408 bytes
Variable Size 1610615248 bytes
Database Buffers 1.4361E+10 bytes
Redo Buffers 60174336 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

$

3) Copy the non-asm files to asm diskgroup.

[oracle]$ rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jan 1 12:57:14 2010

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

RMAN> connect target

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 01-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=383 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00379 name=/u10/oracle/data/TEST/undo01.dbf
output filename=+DATA/TEST/datafile/apps_undots1.257.707144559 tag=TAG20100101T130234 recid=1 stamp=707144573
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00353 name=/u10/oracle/data/TEST/system01.dbf
output filename=+DATA/TEST/datafile/system.258.707144581 tag=TAG20100101T130234 recid=2 stamp=707144595
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00406 name=/u10/oracle/data/TEST/a_txn_ind01.dbf
output filename=+DATA/TEST/datafile/apps_ts_tx_idx.259.707144605 tag=TAG20100101T130234 recid=3 stamp=707144616
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
......
.......
input datafile fno=00009 name=/u10/oracle/data/TEST/odm.dbf
output filename=+DATA/TEST/datafile/odm.298.707145081 tag=TAG20100101T130234 recid=42 stamp=707145080
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u10/oracle/data/TEST/owad01.dbf
output filename=+DATA/TEST/datafile/owapub.299.707145081 tag=TAG20100101T130234 recid=43 stamp=707145081
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-JAN-10

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/TEST/datafile/system.269.707144795"
datafile 2 switched to datafile copy "+DATA/TEST/datafile/system.270.707144811"
datafile 3 switched to datafile copy "+DATA/TEST/datafile/system.271.707144827"
.....
....
datafile 406 switched to datafile copy "+DATA/TEST/datafile/apps_ts_tx_idx.259.707144605"
datafile 407 switched to datafile copy "+DATA/TEST/datafile/apps_ts_seed.266.707144751"

RMAN>

4) Switch tempfiles

RMAN> run {
2> set newname for tempfile 1 to '+DATA';
3> set newname for tempfile 2 to '+DATA';
4> switch tempfile all;
5> }

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file
renamed temporary file 2 to +DATA in control file

RMAN> Alter database open;

database opened

5) Now, restore controlfile from non-asm to asm

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 2089336 bytes
Variable Size 427822728 bytes
Database Buffers 629145600 bytes
Redo Buffers 14684160 bytes

RMAN> restore controlfile to '+DATA' from '/u01/oracle/oradata/TEST/db/apps_st/data/cntrl01.dbf';

Starting restore at 01-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=384 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 01-JAN-10

RMAN> shutdown immediate

Oracle instance shut down

RMAN> exit

Recovery Manager complete.

6) Using asmcmd find out the complete path to the controlfile and edit the pfile/spfile to the path in the asm.

7) Add multiple logfile members to a group and drop non-asm files.

select memeber from v$logfile;

/u01/oracle/oradata/TESTlog02a.dbf
/u01/oracle/oradata/TESTlog02b.dbf
/u01/oracle/oradata/TESTlog01a.dbf
/u01/oracle/oradata/TESTlog01b.dbf

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;

Database altered.

SQL> /

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;

Database altered.

SQL> /

Database altered.

/u01/oracle/oradata/TESTlog02a.dbf
/u01/oracle/oradata/TESTlog02b.dbf
/u01/oracle/oradata/TESTlog01a.dbf
/u01/oracle/oradata/TESTlog01b.dbf

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02a.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/TESTlog02b.dbf'
ORA-00312: online log 2 thread 1: '+DATA/TEST/onlinelog/group_2.305.707145955'
ORA-00312: online log 2 thread 1: '+DATA/TEST/onlinelog/group_2.306.707145963'

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog01a.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog01b.dbf';

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/TEST/onlinelog/group_1.303.707145913 NO
1 ONLINE +DATA/TEST/onlinelog/group_1.304.707145943 NO
2 ONLINE +DATA/TEST/onlinelog/group_2.305.707145955 NO
2 ONLINE +DATA/TEST/onlinelog/group_2.306.707145963 NO

8) Restart the database.

How to clone a database when the source database is unavailable?

2010-01-27T22:40:00.005+05:30

Recently I came across a situation, where I had to clone a database from the rman backup of the source. The source database was decommissioned and there was no way to connect to the source database. How do I clone the auxiliary database with the source being unavailable? Because we usually connect to target and auxiliary and issue the command:

DUPLICATE TARGET DATABASE TO "TEST" (but we cant use this command, because target is unavailable).

Scenario:
Source: PROD Files: /data01/PROD/oradata
Target: TEST Files: /data01/TEST/oradata

Here is a work around I found (There probably other ways to do this):

a) Temporarily name the target database as source ie PROD.
b) Prepare the init.ora (initPROD.ora) on the target server.
c) export ORACLE_SID=PROD on the target server.
d) startup nomount
e) rman target / nocatalog
restore controlfile from 'backup_piece';
alter database mount;
catalog backup piece 'backup_piece1';
catalog backup piece 'backup_piece2';
......
catalog backup piece 'backup_piecen';
run {
allocate channel c1 device type DISK;
...
allocate channel cn device type DISK;
set newname for datafile 1 to '/data01/TEST/oradata/system01.dbf'; (We are using the Target datbase name here instead of source, because we would be later renaming the database to TEST, so we want the datafiles to go to the correct location).
set newname for datafile 2 to '/data01/TEST/oradata/ar_index09.dbf';
.....
set newname for datafile n '/data01/TEST/oradata/xxxxxx.dbf';
sql "alter database rename file ''/data01/PROD/redo1_01.log'' to ''/data01/TEST/redo1_01.log'' ";
sql "alter database rename file ''/data01/PROD/redon_0n.log'' to ''/data01/TEST/redon_0n.log'' ";
restore database;
switch datafile all;
recover database;
release channel c1;
...
release channel cn;
}
f) Once the database is up and open. Rename the database by recreating the controlfile.

Restoring a database using rman

2010-01-23T22:26:00.002+05:30

In the last post I demonstrated how to take a cold backup of a database using nocatalog mode. In this post I would be demonstrating:

a) How to take a backup of a database using recovery catalog
b) Simulate a hardware failure where all files would be lost (Control files + Redo logs + Data Files).
c) Then restore the database from the backup taken.

1) Set the appropriate environment, startup the database and verify the database file location.
[oracle@lxhyd01 ~]$ . oraenv
ORACLE_SID = [oracle] ? ebsdev
[oracle@lxhyd01 ~]$ sqlplus '/as sysdba'
SQL> startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2083528 bytes
Variable Size 318768440 bytes
Database Buffers 872415232 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data01/oradata/ebsdev/system01.dbf
/data01/oradata/ebsdev/undotbs01.dbf
/data01/oradata/ebsdev/sysaux01.dbf
/data01/oradata/ebsdev/users01.dbf
/data01/oradata/ebsdev/example01.dbf

SQL>

2) Since the database is not in archive log mode, we have to take a cold backup of the database.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 2
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lxhyd01 ~]$

3) Review the script to take the cold backup.

[oracle@lxhyd01 ~]$ cat level0_ebsdev.cmd
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup
incremental level=0
tag ebsdevLevel0
format '/data01/backup/ebsdev%U'
database include current controlfile;

release channel d1;
release channel d2;
}
[oracle@lxhyd01 ~]$

4) Connect to rman.

[oracle@lxhyd01 ~]$ rman target / catalog rcat/rcat@rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 23 18:57:26 2010

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

connected to target database (not started)
connected to recovery catalog database

RMAN>
RMAN> startup mount

Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 01/23/2010 18:57:50
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

5) This database was not registered in the catalog. So, we register it.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit

Recovery Manager complete.

6) Now execute the backup

[oracle@lxhyd01 ~]$ rman target / catalog rcat/rcat@rman cmdfile=/home/oracle/level0_ebsdev.cmd

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 23 19:21:01 2010

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

connected to target database: EBSDEV (DBID=2521112594, not open)
connected to recovery catalog database

RMAN> run {
2> allocate channel d1 type disk;
3> allocate channel d2 type disk;
4> backup
5> incremental level=0
6> tag ebsdevLevel0
7> format '/data01/backup/ebsdev%U'
8> database include current controlfile;
9>
10> release channel d1;
11> release channel d2;
12> }
13>
allocated channel: d1
channel d1: sid=159 devtype=DISK

allocated channel: d2
channel d2: sid=155 devtype=DISK

Starting backup at 23-JAN-10
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=/data01/oradata/ebsdev/system01.dbf
input datafile fno=00004 name=/data01/oradata/ebsdev/users01.dbf
channel d1: starting piece 1 at 23-JAN-10
channel d2: starting incremental level 0 datafile backupset
channel d2: specifying datafile(s) in backupset
input datafile fno=00003 name=/data01/oradata/ebsdev/sysaux01.dbf
input datafile fno=00005 name=/data01/oradata/ebsdev/example01.dbf
input datafile fno=00002 name=/data01/oradata/ebsdev/undotbs01.dbf
channel d2: starting piece 1 at 23-JAN-10
channel d2: finished piece 1 at 23-JAN-10
piece handle=/data01/backup/ebsdev03l4718v_1_1 tag=EBSDEVLEVEL0 comment=NONE
channel d2: backup set complete, elapsed time: 00:04:26
channel d2: starting incremental level 0 datafile backupset
channel d2: specifying datafile(s) in backupset
including current control file in backupset
channel d2: starting piece 1 at 23-JAN-10
channel d2: finished piece 1 at 23-JAN-10
piece handle=/data01/backup/ebsdev04l471ha_1_1 tag=EBSDEVLEVEL0 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:13
channel d2: starting incremental level 0 datafile backupset
channel d2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel d2: starting piece 1 at 23-JAN-10
channel d2: finished piece 1 at 23-JAN-10
piece handle=/data01/backup/ebsdev05l471hp_1_1 tag=EBSDEVLEVEL0 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:03
channel d1: finished piece 1 at 23-JAN-10
piece handle=/data01/backup/ebsdev02l4718v_1_1 tag=EBSDEVLEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:06:30
Finished backup at 23-JAN-10

released channel: d1

released channel: d2

Recovery Manager complete.
[oracle@lxhyd01 ~]$

7) Validate the backup taken.

[oracle@lxhyd01 ~]$ rman target / catalog rcat/rcat@rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 23 19:56:33 2010

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

connected to target database: EBSDEV (DBID=2521112594, not open)
connected to recovery catalog database

RMAN> list backup ;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
37 Incr 0 219.48M DISK 00:04:22 23-JAN-10
BP Key: 41 Status: AVAILABLE Compressed: NO Tag: EBSDEVLEVEL0
Piece Name: /data01/backup/ebsdev03l4718v_1_1
List of Datafiles in backup set 37
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/undotbs01.dbf
3 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/sysaux01.dbf
5 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38 Incr 0 7.08M DISK 00:00:10 23-JAN-10
BP Key: 42 Status: AVAILABLE Compressed: NO Tag: EBSDEVLEVEL0
Piece Name: /data01/backup/ebsdev04l471ha_1_1
Control File Included: Ckp SCN: 688114 Ckp time: 23-JAN-10

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39 Incr 0 80.00K DISK 00:00:01 23-JAN-10
BP Key: 43 Status: AVAILABLE Compressed: NO Tag: EBSDEVLEVEL0
Piece Name: /data01/backup/ebsdev05l471hp_1_1
SPFILE Included: Modification time: 23-JAN-10

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40 Incr 0 366.66M DISK 00:06:29 23-JAN-10
BP Key: 44 Status: AVAILABLE Compressed: NO Tag: EBSDEVLEVEL0
Piece Name: /data01/backup/ebsdev02l4718v_1_1
List of Datafiles in backup set 40
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/system01.dbf
4 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/users01.dbf

RMAN>
RMAN> RESTORE VALIDATE DATABASE;

Starting restore at 23-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev03l4718v_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev03l4718v_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: validation complete, elapsed time: 00:00:57
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev02l4718v_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev02l4718v_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: validation complete, elapsed time: 00:00:55
Finished restore at 23-JAN-10

RMAN>
RMAN> exit

Recovery Manager complete.

8) Simulate a failure, Remove all the database files at OS level.

[oracle@lxhyd01 ~]$ rm -f /data01/oradata/ebsdev/*
[oracle@lxhyd01 ~]$ ls -ltr /data01/oradata/ebsdev/*
ls: /data01/oradata/ebsdev/*: No such file or directory
[oracle@lxhyd01 ~]$

9) Restore the database from the backup taken.

[oracle@lxhyd01 ~]$ rman target / catalog rcat/rcat@rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 23 20:15:51 2010

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

connected to target database (not started)
connected to recovery catalog database

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1207959552 bytes

Fixed Size 2083528 bytes
Variable Size 318768440 bytes
Database Buffers 872415232 bytes
Redo Buffers 14692352 bytes

10) Since the controlfile is missing as well, We should restore the controlfile first.

RMAN> restore controlfile;

Starting restore at 23-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev04l471ha_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev04l471ha_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
output filename=/data01/oradata/ebsdev/control01.ctl
output filename=/data01/oradata/ebsdev/control02.ctl
output filename=/data01/oradata/ebsdev/control03.ctl
Finished restore at 23-JAN-10

RMAN>
RMAN> restore database;

Starting restore at 23-JAN-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /data01/oradata/ebsdev/undotbs01.dbf
restoring datafile 00003 to /data01/oradata/ebsdev/sysaux01.dbf
restoring datafile 00005 to /data01/oradata/ebsdev/example01.dbf
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev03l4718v_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev03l4718v_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data01/oradata/ebsdev/system01.dbf
restoring datafile 00004 to /data01/oradata/ebsdev/users01.dbf
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev02l4718v_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev02l4718v_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 23-JAN-10

RMAN>

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

11) Verify the status of the database from v$instance.

[oracle@lxhyd01 ~]$ sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 23 20:27:47 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 ebsdev
lxhyd01
10.2.0.4.0 23-JAN-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL>

Cold backup of a database using rman

2010-01-12T13:15:00.003+05:30

There were many requests made to me for covering some backup recovery videos through rman..

In this video I would be demonstrating a simple cold backup of a database using rman.

How to clear asm disk headers

2010-01-02T12:31:00.004+05:30

Use the below command if you want to clear an ASM disk header. We may want to do that if we want to reuse the disk to add it to some other diskgroup.

$ dd if=/dev/zero of=/dev/raw/raw1 bs=8192 count=12800
12800+0 records in
12800+0 records out
104857600 bytes (105 MB) copied, 3.78717 seconds, 27.7 MB/s

Let us now verify the disk header..

$ kfed read /dev/raw/raw1
kfbh.endian: 0 ; 0x000: 0x00
kfbh.hard: 0 ; 0x001: 0x00
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt: 0 ; 0x003: 0x00
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
$

KFBTYP_INVALID, indicates the disk headers are cleared. This disk should now appear as a CANDIDATE disk in the v$asm_disk.

Reading ASM Headers using kfed

2010-01-02T11:19:00.005+05:30

kfed is an oracle based utility to read ASM disk headers. kfed is not available by default. This needs to be relinked.

I found this tool particularly useful in:
a) Identifying if a particular raw device's disk headers were cleared before a raw device can be reused.
b) Identifying which raw device belongs to which diskgroup.
c) Identifying multiple paths to the same raw device. (Oracle instance complains if you have multiple paths to the same raw device, and the diskgroup does not mount)

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ikfed

[oracle]$ kfed read /dev/raw/raw10

/dev/raw/raw10
kfbh.endian: 1 ; 0x000: 0x01
kfbh.hard: 130 ; 0x001: 0x82
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt: 1 ; 0x003: 0x01
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 2147483648 ; 0x008: TYPE=0x8 NUMB=0x0
kfbh.check: 822661445 ; 0x00c: 0x3108d145
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr: ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]: 0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]: 0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]: 0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000
kfdhdb.compat: 168820736 ; 0x020: 0x0a100000
kfdhdb.dsknum: 0 ; 0x024: 0x0000
kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname: DATA_01_0000 ; 0x028: length=12
kfdhdb.grpname: DATA_01 ; 0x048: length=7
kfdhdb.fgname: DATA_01_0000 ; 0x068: length=12
kfdhdb.capname: ; 0x088: length=0
kfdhdb.crestmp.hi: 32932905 ; 0x0a8: HOUR=0x9 DAYS=0x1 MNTH=0x1 YEAR=0x7da
kfdhdb.crestmp.lo: 3835458560 ; 0x0ac: USEC=0x0 MSEC=0x31d SECS=0x9 MINS=0x39
kfdhdb.mntstmp.hi: 32932905 ; 0x0b0: HOUR=0x9 DAYS=0x1 MNTH=0x1 YEAR=0x7da
kfdhdb.mntstmp.lo: 3835458560 ; 0x0b4: USEC=0x0 MSEC=0x31d SECS=0x9 MINS=0x39
kfdhdb.secsize: 512 ; 0x0b8: 0x0200
kfdhdb.blksize: 4096 ; 0x0ba: 0x1000
kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact: 113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize: 131069 ; 0x0c4: 0x0001fffd
kfdhdb.pmcnt: 3 ; 0x0c8: 0x00000003
kfdhdb.fstlocn: 1 ; 0x0cc: 0x00000001
kfdhdb.altlocn: 2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn: 0 ; 0x0d4: 0x00000000
kfdhdb.redomirrors[0]: 0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]: 0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]: 0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]: 0 ; 0x0de: 0x0000
kfdhdb.dbcompat: 168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi: 32932905 ; 0x0e4: HOUR=0x9 DAYS=0x1 MNTH=0x1 YEAR=0x7da
kfdhdb.grpstmp.lo: 3835427840 ; 0x0e8: USEC=0x0 MSEC=0x2ff SECS=0x9 MINS=0x39
kfdhdb.ub4spare[0]: 0 ; 0x0ec: 0x00000000
kfdhdb.ub4spare[1]: 0 ; 0x0f0: 0x00000000
kfdhdb.ub4spare[2]: 0 ; 0x0f4: 0x00000000
kfdhdb.ub4spare[3]: 0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[4]: 0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[5]: 0 ; 0x100: 0x00000000
kfdhdb.ub4spare[6]: 0 ; 0x104: 0x00000000
kfdhdb.ub4spare[7]: 0 ; 0x108: 0x00000000
kfdhdb.ub4spare[8]: 0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[9]: 0 ; 0x110: 0x00000000
kfdhdb.ub4spare[10]: 0 ; 0x114: 0x00000000
kfdhdb.ub4spare[11]: 0 ; 0x118: 0x00000000
kfdhdb.ub4spare[12]: 0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[13]: 0 ; 0x120: 0x00000000
kfdhdb.ub4spare[14]: 0 ; 0x124: 0x00000000
kfdhdb.ub4spare[15]: 0 ; 0x128: 0x00000000
kfdhdb.ub4spare[16]: 0 ; 0x12c: 0x00000000
kfdhdb.ub4spare[17]: 0 ; 0x130: 0x00000000
kfdhdb.ub4spare[18]: 0 ; 0x134: 0x00000000
kfdhdb.ub4spare[19]: 0 ; 0x138: 0x00000000
kfdhdb.ub4spare[20]: 0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[21]: 0 ; 0x140: 0x00000000
kfdhdb.ub4spare[22]: 0 ; 0x144: 0x00000000
kfdhdb.ub4spare[23]: 0 ; 0x148: 0x00000000
kfdhdb.ub4spare[24]: 0 ; 0x14c: 0x00000000
kfdhdb.ub4spare[25]: 0 ; 0x150: 0x00000000
kfdhdb.ub4spare[26]: 0 ; 0x154: 0x00000000
kfdhdb.ub4spare[27]: 0 ; 0x158: 0x00000000
kfdhdb.ub4spare[28]: 0 ; 0x15c: 0x00000000
kfdhdb.ub4spare[29]: 0 ; 0x160: 0x00000000
kfdhdb.ub4spare[30]: 0 ; 0x164: 0x00000000
kfdhdb.ub4spare[31]: 0 ; 0x168: 0x00000000
kfdhdb.ub4spare[32]: 0 ; 0x16c: 0x00000000
kfdhdb.ub4spare[33]: 0 ; 0x170: 0x00000000
kfdhdb.ub4spare[34]: 0 ; 0x174: 0x00000000
kfdhdb.ub4spare[35]: 0 ; 0x178: 0x00000000
kfdhdb.ub4spare[36]: 0 ; 0x17c: 0x00000000
kfdhdb.ub4spare[37]: 0 ; 0x180: 0x00000000
kfdhdb.ub4spare[38]: 0 ; 0x184: 0x00000000
kfdhdb.ub4spare[39]: 0 ; 0x188: 0x00000000
kfdhdb.ub4spare[40]: 0 ; 0x18c: 0x00000000
kfdhdb.ub4spare[41]: 0 ; 0x190: 0x00000000
kfdhdb.ub4spare[42]: 0 ; 0x194: 0x00000000
kfdhdb.ub4spare[43]: 0 ; 0x198: 0x00000000
kfdhdb.ub4spare[44]: 0 ; 0x19c: 0x00000000
kfdhdb.ub4spare[45]: 0 ; 0x1a0: 0x00000000
kfdhdb.ub4spare[46]: 0 ; 0x1a4: 0x00000000
kfdhdb.ub4spare[47]: 0 ; 0x1a8: 0x00000000
kfdhdb.ub4spare[48]: 0 ; 0x1ac: 0x00000000
kfdhdb.ub4spare[49]: 0 ; 0x1b0: 0x00000000
kfdhdb.ub4spare[50]: 0 ; 0x1b4: 0x00000000
kfdhdb.ub4spare[51]: 0 ; 0x1b8: 0x00000000
kfdhdb.ub4spare[52]: 0 ; 0x1bc: 0x00000000
kfdhdb.ub4spare[53]: 0 ; 0x1c0: 0x00000000
kfdhdb.ub4spare[54]: 0 ; 0x1c4: 0x00000000
kfdhdb.ub4spare[55]: 0 ; 0x1c8: 0x00000000
kfdhdb.ub4spare[56]: 0 ; 0x1cc: 0x00000000
kfdhdb.ub4spare[57]: 0 ; 0x1d0: 0x00000000
kfdhdb.acdb.aba.seq: 0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk: 0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents: 0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare: 0 ; 0x1de: 0x0000

If a disk is not an ASM DISK, Then the header would look something like:

[oracle]$ kfed read /dev/raw/raw1
kfbh.endian: 0 ; 0x000: 0x00
kfbh.hard: 0 ; 0x001: 0x00
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt: 0 ; 0x003: 0x00
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
[oracle]$

How to speed up cloning

2009-12-21T17:00:00.004+05:30

Usually during clones, we normally tar & gzip source files. Then copy them over to the target and then gunzip & untar it. Using the below method, we can actually avoid the two fold step and do the entire operation in a single step.

eg.
We want to copy the Oracle Home from source to target.

Create the Oracle Home location on target (lxhyd02):
mkdir -p /data/oracle/product/10.2.0/db_1

On the source (lxhyd01):
cd /u01/oracle/product/10.2.0/db_1
tar cf - * | ssh lxhyd02.learning.com '(cd /data/oracle/product/10.2.0/db_1/; tar xf - )'

Check if files are being untarred on target:

[oracle@lxhyd02 data]$ cd /data/oracle/product/10.2.0/db_1
[oracle@lxhyd02 db_1]$ ls -tlr
total 240
drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 jre
drwxr-x--- 7 oracle dba 4096 Oct 25 20:38 javavm
drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 has
drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 diagnostics
drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 demo
drwxr-x--- 6 oracle dba 4096 Oct 25 20:38 crs
drwxr-x--- 4 oracle dba 4096 Oct 25 20:38 clone
drwxr-x--- 7 oracle dba 4096 Oct 25 20:38 assistants
drwxr-x--- 4 oracle dba 4096 Oct 25 20:49 jdbc
.....
.....

Advantages:

1) Faster that the two folds approach
2) Does not require disk space to store the tar/gzip files on source and target. Here, the tar files are created on the fly and immediately untarred on the target.

Limitations:

1) Speed would depend on the network bandwidth.

Introduction to Shell Scripting - II

2009-12-11T01:34:00.002+05:30

Here is the second video..

Introduction to Shell Scripting

2009-12-08T16:20:00.004+05:30

In these videos I would be covering the basics of shell scripting.
The videos are split into 3 parts, Here is the 1st video

The topics covered in this tutorial are:

1. A simple hello world.
2. A script to add two numbers
3. A script to add two numbers passed as command line parameters.
4. Understanding the Exit status $?
5. Demonstrating through practical example: Check if a listener is up. If it is not then display a message that the listener is down.
6. Executing sql queries from shell script- In the example we check if the database is up or not.
7. Using cut, awk to find specific fields in an output.
8. How to read contents of a file line by line and evaluating each line for a specific string.
9. Using this for finding mountpoints above 90% used.
10. Sending attachments through emails.

Shared Memory segments and Semaphores

2009-11-20T09:12:00.019+05:30

When you startup a database instance a shared memory is acquired equivalent to the size of the SGA. For more information of what a shared memory and semaphore is you can refer an article by Nidhi Jain at http://www.orafaq.com/node/8

So shared memory and semaphores are allocated when you start the instance and it should be de-allocated when the instance is shut down. There are times when you shutdown an instance and the OS still leaves behind shared memory and semaphores. How do we go about clearing these shared memory segments and semaphores?

First let us see one of the tools called "sysresv" to identify the shared memory and sempahores allocated to an instance.

As you can see, we have have identified the shared memory segment id and sempahore id.
Also note, the sysresv command is in the $ORACLE_HOME/bin location, so this is a utility supplied by Oracle.

There is also an OS level utility calls "ipcs" available to view all the shared segments and semaphores allocated on that server. So let us co-relate the output of sysresv and ipcs.

ipcs -a gives much more information here like:

owner the shared memory segment,
bytes the size shared memory allocated (which is the size of SGA)
nattach is the number of processes attached to this shared memory.

As you can see in the figure below the bytes allocated is equivalent to the sga_target.

Ideally, when we shutdown an instance the allocated shared memory segment and sempahore should be released. As you can see below.

However if this does not happen, we can release the shared memory and semaphore manually by using the "ipcrm" command.

To remove the shared memory segment we use:

ipcrm -m <SharedMemory ID>

considering the above example,
ipcrm -m 491531

To remove the semaphores:

ipcrm -s <SemaphoreID>

eg. ipcrm -s 229377

For more options of ipcrm you can refer http://publib.boulder.ibm.com/infocenter/pseries/v5r3/index.jsp?topic=/com.ibm.aix.cmds/doc/aixcmds3/ipcrm.htm

How to search a string in entire database

2009-11-13T23:13:00.005+05:30

Recently I came across an issue where there was a certain text being displayed in the application which was incorrect. This data was stored in the database, however we were unable to trace out which table/column had that entry. How I wish that I could just grep the whole oracle database for that string. Googled up everywhere for some ready made code to do that... But couldn't find any...Finally had to come up with my own pl/sql code...Here it goes..

set serveroutput on size 1000000
declare
TYPE QueryCurType is REF CURSOR;
query1 QueryCurType ;

cursor c1 is select owner,table_name from dba_tables where owner not in ('SYS','SYSTEM') and table_name not like '%$%';
cursor c2(t1 varchar2) is select column_name from dba_tab_columns where table_name=t1 and DATA_TYPE in ('NVARCHAR2','VARCHAR2','CHAR');
temp_var varchar2(3000);
query varchar2(3000);
begin
for tab1 in c1 loop
for col in c2(tab1.table_name) loop
query:='select '||col.column_name||' from '||tab1.owner||'.'||tab1.table_name||' where '||col.column_name||' like ''%Ciudad%definida%''';
--dbms_output.put_line('executing..'||query);
open query1 for query;
loop
fetch query1 into temp_var;
if concat('a',temp_var) != 'a' then
dbms_output.put_line('Found String: "'||temp_var||'"# Column:'||col.column_name||'# Table:'||tab1.table_name);
end if;
exit when query1%NOTFOUND;
end loop;
end loop;
end loop;
end;
/

The text in red is what we are searching for. Replace it with the text you would like to search.

Using TKPROF

2009-11-10T11:34:00.005+05:30

In this presentation I would be covering a practical example of using tkprof to find out the problem query from a raw trace file.

Patching using Grid Control

2009-11-09T02:51:00.000+05:30

Coming Soon..

Find Details of a session using the SPID - unix.sql

2009-11-08T01:52:00.003+05:30

set echo off
set serveroutput on size 50000
set verify off

set feedback off
accept uxproc prompt 'Enter Unix process id: '
accept inst_id prompt 'Enter instance id: '
DECLARE
v_sid number;
vs_cnt number;
s sys.gv_$session%ROWTYPE;
p sys.gv_$process%ROWTYPE;
cursor cur_c1 is select sid from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc') and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
    dbms_output.put_line('=====================================================================');
    select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc') and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
    dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id for the instance number '||'&inst_id');
    dbms_output.put_line('=====================================================================');
    open cur_c1;
    LOOP
      FETCH cur_c1 INTO v_sid;
        EXIT WHEN (cur_c1%NOTFOUND);
        select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
        select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
        dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
        dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
        dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);
        dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);
        dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);
        dbms_output.put_line('Ora User    : '|| s.username);
        dbms_output.put_line('Details     : '|| s.action||' - '||s.module);
        dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
        dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
        dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
        dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9990.0') || ' min');
        dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
        dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
        dbms_output.put_line('Current SQL statement:');
        for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&inst_id' order by piece)
        loop
            dbms_output.put_line(chr(9)||c1.sql_text);
        end loop;
        dbms_output.put_line('Previous SQL statement:');
        for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&inst_id' order by piece)
        loop
            dbms_output.put_line(chr(9)||c1.sql_text);
        end loop;
        dbms_output.put_line('Session Waits:');
        for c1 in ( select * from sys.gv_$session_wait where sid = s.sid and inst_id='&inst_id')
        loop
        dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
        end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
--    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
        dbms_output.put_line('Locks:');
        for c1 in ( select /*+ RULE */ decode(l.type,
          -- Long locks
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks
                      'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F', 'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ', 'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ', 'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR', 'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH ',
                      'MM', 'MOUNT DEF   ', 'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ', 'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ', 'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ', 'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ', 'TE', 'EXTEND SEG ',
                      'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
                      'UN', 'USER NAME   ', 'WL', 'WRITE REDO ',
                      'TYPE='||l.type) type,
                       decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                       4, 'S',    5, 'RSX', 6, 'X',
                       to_char(l.lmode) ) lmode,
                          decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                         4, 'S', 5, 'RSX', 6, 'X',
                         to_char(l.request) ) lrequest,
                           decode(l.type, 'MR', o.name,
                      'TD', o.name,
                      'TM', o.name,
                      'RW', 'FILE#='||substr(l.id1,1,3)||
                            ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
                      'WL', 'REDO LOG FILE#='||l.id1,
                      'RT', 'THREAD='||l.id1,
                      'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
                      'ID1='||l.id1||' ID2='||l.id2) objname
                       from sys.gv_$lock l, sys.obj$ o
                       where sid   = s.sid
                                and inst_id='&inst_id'
                         and l.id1 = o.obj#(+) )
            loop
             dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
            end loop;
            dbms_output.put_line('=====================================================================');
    END LOOP;
    dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id for the instance number '||'&inst_id');
    dbms_output.put_line('Please scroll up to see details of all the sessions.');
    dbms_output.put_line('=====================================================================');
    close cur_c1;
exception
    when no_data_found then
      dbms_output.put_line('Unable to find process id &&uxproc for the instance number '||'&inst_id'||' !!!');
    dbms_output.put_line('=====================================================================');
      return;
    when others then
      dbms_output.put_line(sqlerrm);
      return;
END;
/
undef uxproc
set heading on
set verify on
set feedback on
set echo on

DB Clone using Grid Control

2009-11-08T00:52:00.038+05:30

In this post I would demonstrate how to clone an Oracle Database using GRID CONTROL. The steps are pretty straight forward, however I am posting it just for the sake of familiarity - Most of the DBAs prefer to do clone the database manually (either using rman, or manual OS level copy). When cloning through grid - The agent runs the following script: $AGENT_HOME/sysman/admin/scripts/db/dbclone/db_clone.pl

1. Login to Grid Control

2. Follow the navigation "TARGETS" => DATABASES=> Click on the Source Database (ie EBSPRD in this example)

3. The working directory Location is where the source database backup files would be placed. You can choose to delete or retain the backup after the clone.

4. Since the source database is not in archive log mode, It prompts you if the database should be put in archive log mode or not? (If not, It would take the cold backup of the source).

5. The below screen shows the database file locations, by default the database files are stored in one of the directories in the $ORACLE_HOME, we don't want that..To change it hit the "Customize" button.

6. Specify the location where you want the files to be on the target.

7. Give an appropriate Description of the Job. If you want to schedule the clone later, you can do that as well.

8. You can monitor the status of the job by clicking on the "Jobs" tab on the top right hand side.

9. Incase you want to see more details of the job, just click on it.

10. Once the job completes successfully, the database is also registered in the grid control.

Check Wait events

2009-11-08T00:50:00.001+05:30

set pages 1000
set lines 120
col inst format 9999

col sid format 9999
col event format a29 trunc
col program format a20 trunc
col module format a20
col username format A11
col secs format 99999
select w.inst_id inst, w.sid, w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait Secs
from gv$session_wait w, gv$session s
where w.inst_id = s.inst_id and
w.sid=s.sid and w.state='WAITING' and
w.event not in ('pmon timer',
'smon timer',
'rdbms ipc message',
'pipe get',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'SQL*Net more data from client',
'wakeup time manager',
'slave wait',
'SQL*Net more data to client') and w.event not like '%slave wait'
group by w.inst_id, w.sid,w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait
order by 1,3;

Transparent Application Failover TAF Demo

2009-11-07T14:42:00.000+05:30

10g Clusterware Presentation

2009-11-07T00:51:00.003+05:30

Backup and Recovery of OCR Demo Part -1 - Oracle 10g RAC

2009-11-07T00:50:00.001+05:30