RMAN-06207 RMAN-06208 RMAN-06214 - backup standby controlfile is missing from standby server/database

Symptoms

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/cisdbc3/cisdbc3_controlfile_bak_11-15-11_20:02:56

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /ora01/oracle/admin/BACKUP/cisdbc3/cisdbc3_controlfile_bak_11-15-11_20:02:56

 

Cause

rman delete fails due to mismatched status of backup pieces

In this case,  backup standby controlfile is missing from standby server/database

Solution

Step 1 thru 5

Step 1

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Report of obsolete backups and copies

Type                 Key    Completion Time    Filename/Handle

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

Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56

Step 2

RMAN> crosscheck copy of controlfile;

validation failed for control file copy

control file copy filename=/ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56 recid=676 stamp=767304177

Crosschecked 1 objects

Step 3

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Deleting the following obsolete backups and copies:

Type                 Key    Completion Time    Filename/Handle

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

Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56

deleted control file copy

control file copy filename=/ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56 recid=676 stamp=767304177

Deleted 1 objects

Step 4

RMAN>  crosscheck copy of controlfile;

Step 5

RMAN>  report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

no obsolete backups found

RMAN>

Hope this helps!Rupam

What is causing account lock

 -- check profile

col PROFILE format a10

col LIMIT format a15

select * from dba_profiles where resource_type='PASSWORD' order by 1,2;

-- options for trouble shooting

1. audit not successful connections

2. trace 1017 event

-- Steps for option 1

Reference: oracle doc # 352389.1

Reference:   oracle doc #  221944.1

-- setup audit

sqlplus / as sysdba

AUDIT CREATE SESSION BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT CONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;

-- Get information related to failed connections

col USERID format a10

col USERHOST format a25

col TERMINAL format a10

select returncode, action#, userid, userhost, terminal from aud$ where returncode='1017' and action#=100;

SELECT TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE   from aud$ where returncode=1017;

NOTE : TIME is in GMT, so subtract -5 hours

-- Steps for option 2

-- to trace what is locking the account

1.       Login to sqlplus as sysdba

2.       enable trace, using

                   alter system set events ‘1017 trace name errorstack level 10’;

3.       Show parameter dump

4.       Goto the udump location

5.       grep ORA – 01017 *

6.       get the timestamp and related information  from trace generated

7.       When trace is no more needed , set it off using:

              alter system set events ‘1017 trace name errorstack off’

8.       Goto the listener.log file location

9.       Get the connectivity information for the selected time from listener log file.

Hope this help. Regards Rupam

Image/PDF data via DML

 

1.  Upload the mybook.pdf to IMAGES folder /tmp

2. Create directory in the database using Create directory images as ‘/tmp/’;

3.  Run this DML

DECLARE

    f_lob BFILE;

    b_lob BLOB;

BEGIN

    INSERT INTO dummy_table (BILLING_ID,

                                      BILLING_RUN_ID,

                                      BILLING_INFO_ID,

                                      STMT_DATA,

                                      CREATED_DATE)

    VALUES (myseqeunce.NEXTVAL,

            108,

            26753,

            EMPTY_BLOB (),

            TO_DATE ('31-AUG-2011'))

    RETURN STMT_DATA

    INTO   b_lob;

    f_lob := BFILENAME ('IMAGES', 'mybook.pdf');

    DBMS_LOB.fileopen (f_lob, DBMS_LOB.file_readonly);

    DBMS_LOB.loadfromfile (b_lob, f_lob, DBMS_LOB.getlength (f_lob));

    DBMS_LOB.fileclose (f_lob);

    COMMIT;

END;

Hope this help. Regards Rupam

Use of Message Broker

 Testing the use of Message Broker to the database and then to an object in another database via a database link. They received an ORA-24777 error. Metalink reveals this note:

Error "ORA-24777: Use Of Non-Migratable Database Link Not Allowed" Using Oracle XA Datasource When Executing a Select via Database Link. [ID 879543.1]

Which recommends configuring shared servers for network connections. To test this, I made the following changes in the spfile for database db1:

ALTER SYSTEM SET shared_servers=5 SCOPE=BOTH SID='*';

ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)' SCOPE=BOTH SID='*';

The listener now shows a shared server listener available for db1:

oracle@tiger  > lsnrctl services

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 11-OCT-2011 15:13:31

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

..snipped..

Service "db1" has 1 instance(s).

  Instance "db1", status READY, has 2 handler(s) for this service...

    Handler(s):

      "D000" established:592 refused:0 current:9 max:1022 state:ready

         DISPATCHER

         (ADDRESS=(PROTOCOL=tcp)(HOST=tiger)(PORT=23033))

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

developed a small test using Message Broker connecting to db1 and referencing an object in db2 via a database link. It worked. They did not get the ORA-24777 error and they did get the expected results.

Hope this help. Regards Rupam

swap area in database server

The command to see what’s available is:

free –m This will show you the free space in megabytes

Here’s the output:

             total       used       free     shared    buffers     cached
Mem:         48299      26358      21940          0        647      19719
-/+ buffers/cache:       5992      42306
Swap:         4095          0       4095

So, the swap line shows you that you have 4 gig of swap free…excellent.

For the amount of free memory, you want to look at the -/+ buffers/cache line. So, looking at that line, you have 21940 megabytes (21 gig) free.

Keep in mind the cached value and the buffers value will be reclaimed by the system is needed. You actually want a large cached value. I’m not sure what’s stored in it but….


Caution ! Running out  of SWAP area may  cause  the server to hang

Rupam

count # of processes for each database

Count number of client connections to oracle Database on a server

countproc.sh

#/bin/ksh

# count # of processes for each database

for i in ` pgrep -fl smon|awk -F_ '{print $3}'`

do

echo "database name : " $i

ps -ef|grep $i|grep LOCAL=NO |wc -l

done

Rupam

sudo

How to sudo to oracle account in solaris

/usr/local/bin/sudo /bin/su - oracle

Rupam

Tablespaces

The System Tablespace
The system tablespace stores the data dictionary and is available whenever a database is open. Unlike other tablespaces, it cannot be taken offline.

The Sysaux Tablespace
Also new with Oracle 10g is the sysaux tablespace. The sysaux tablespace is used to store database components that were previously stored in the system tablespace in releases prior to version 10g.

Bigfile Tablespaces
Another new feature of Oracle 10g is the bigfile tablespace. A bigfile tablespace contains a single datafile which can address up to 4,294,967,296 db blocks. A bigfile tablespace two terabytes in size is created with the BIGFILE command shown below:

CREATE BIGFILE TABLESPACE bigfile_data DATA FILE '/tspace/data/bigfile_data.dbf' size 2T

With two exceptions, only locally managed tablespaces with automatic segment-space management can be created as bigfile tablespaces. The two exceptions are locally managed undo tablespaces and temporary tablespaces.

In general, bigfile tablespaces should always be used with automatic storage management that support dynamically extensible logical volumes. The use of striping and RAID is also recommended to improve the overall performance of bigfile tablespaces. Note that the system and sysaux tablespace cannot be created as bigfile tablespaces.

Smallfile Tablespaces
A smallfile tablespace is the usual type of tablespace, can contain up to 1,022 datafiles, and can address up to 222 db blocks.

Locally Managed Tablespaces
A 'bitmap' is stored in the tablespace, and each bit in the bitmap determines if a corresponding extent in the tablespace is free or used. Because the extent sizes are of the type uniform or autoallocate, the following storage parameters aren't applicable and therefore are not permitted:
next
minextents
maxextents
pctincrease
default storage
Locally managed tablespaces have two basic advantages: adjacent free space is automatically coalesced, and recursive space management is avoided.

Hope this helps! Rupam

log file sync" wait is topping AWR

From the AWR report, we can see that log file sync average wait time was 24 ms.

We normally expect the average wait time for log file sync less than 5ms.

So, first, please check if the disk I/O for redo logs.

High 'log file sync' waits could be caused by various reasons, some of these are:
1. slower writes to redo logs
2. very high number of COMMITs
3. insufficient redo log buffers
4. checkpoint
5. process post issues

The five reasons  listed above  are examples of reasons that could cause 'log file sync' we need more information to determine the reason for the wait,
 then we can make recommendations.

Please also download and run the script from note 1064487.1.

When a user session(foreground process) COMMITs (or rolls back), the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session.

The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk.

This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable.

Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally).

For further information about log file sync, please refer to MOS note 34592.1 and note 857576.1 How to Tune Log File Sync.


1. For analysis check AWR 

1.1 "log file sync" wait is topping AWR:

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
log file sync 59,558 3,275 55 54.53 Commit <===== average wait time 55 ms. for 8 hrs. Very high.
DB CPU 1,402 23.34

1.2. Most wait time is contributed by "log file parallel write":

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % bg time
db file parallel write 136,730 0 3,957 29 2.44 42.37
log file parallel write 61,635 0 2,810 46 1.10 30.09 <===== 46 ms or 84% of "log file sync" time

2. Output of script from note 1064487.1
Wait histogram also shows the same:
INST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------------------- -------------------------- ----------------------
1 log file sync 1 176
1 log file parallel write 1 117
1 LGWR wait for redo copy 1 150

1 log file sync 2 3013
1 log file parallel write 2 3433
1 LGWR wait for redo copy 2 8

1 log file sync 4 15254
1 log file parallel write 4 18064
1 LGWR wait for redo copy 4 6

1 log file sync 8 44676
1 log file parallel write 8 51155
1 LGWR wait for redo copy 8 9

2.2. Spikes of log file sync are really bad (hundreds of ms):

APPROACH: These are the minutes where the avg log file sync time
was the highest (in milliseconds).
MINUTE INST_ID EVENT
--------------------------------------------------------------------------- ---------- ------------------------------
TOTAL_WAIT_TIME WAITS AVG_TIME_WAITED
----------------- ---------- -----------------
Aug18_0311 1 log file sync
48224.771 58 831.462

Aug18_0312 1 log file sync
100701.614 117 860.698

Aug18_0313 1 log file sync
20914.552 33 633.774

Aug18_0613 1 log file sync
84651.481 93 910.231

Aug18_0614 1 log file sync
139550.663 139 1003.962

2.3. User commits averaged 2/sec, which is low. (Caveat: this number is an 8-hr average.)


2.  oswatcher data, to pinpoint where the IO bottleneck is


From the oswatcher data,

I see extreme IO contention across almost all of the disks between 4-7AM. Disk utilization is between 80-100% with worst IO response time over 200-300 ms. Sdc and sdd are the busiest disks with util constantly above 80%. Example:

avg-cpu: %user %nice %system %iowait %steal %idle

         69.62 1.48  13.46   15.44   0.00   0.00

Device: rrqm/s wrqm/s r/s  w/s rsec/s  wsec/s avgrq-sz avgqu-sz await svctm %util

   sdc 5.21   0.65 119.54 7.49 8932.90 834.85 76.89    33.03    240.00 7.87 100.00 <=== This disk is saturated

  sdc1 5.21   0.65 119.54 7.49 8932.90 834.85 76.89    33.03    240.00 7.87 100.00

  sdd 4.89 0.33 119.87 7.82 9386.32 103.58 74.32 6.64 52.81 7.68 98.05 <=== Same with this one

 sdd1 4.89 0.33 119.87 7.82 9386.32 103.58 74.32 6.64 52.81 7.68 98.05

  sde 0.00 0.33 0.65 8.47 5.21 232.57 26.07 1.79 201.43 92.86 84.69 <=== High utilization @85%

  sde1 0.00 0.33 0.65 8.47 5.21 232.57 26.07 1.79 201.43 92.86 84.69

I also noticed that there were many DBMS jobs running from ~ 7 different databases at the same time. CPU util is ~80%.

zzz ***Sun Aug 21 06:02:00 EDT 2011
top - 06:02:04 up 18 days, 13:51, 6 users, load average: 16.87, 8.64, 4.56
Tasks: 1042 total, 22 running, 1019 sleeping, 0 stopped, 1 zombie
Cpu(s): 69.9%us, 10.2%sy, 0.3%ni, 0.0%id, 18.8%wa, 0.2%hi, 0.6%si, 0.0%st
Mem: 49458756k total, 49155380k used, 303376k free, 951836k buffers
Swap: 4194296k total, 1548904k used, 2645392k free, 39128504k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7680 oracle 15 0 2255m 90m 80m S 12.9 0.2 0:00.80 ora_j002_oracl1 <=== db 1


3. Trace the LGWR while running a dummy DELETE transaction.

3.1) create a dummy user table that has, say 1 million rows.
3.2) At the OS prompt, run
strace -fo lgwr_strace.out -r -p <LGWR_PID>
3.3) From a different window, sqlplus session:
DELETE FROM <dummy_table>;
3.4) COMMIT; -- IMPORTANT. To trigger a log flush.
3.5) CTRL-C the strace and upload the lgwr_strace.out file.


Conclusion
All this indicates IO capacity/hot disk issue. You are over-leveraging the server in terms of IO capacity (especially on the 2 disks: sdc and sde).

You would experience the same slow IO issue in other databases if those 2 disks are shared across the other DBs.

Action Plan

1. Review ASM diskgroup configuration. Make sure the diskgroups are spanned across all available disks.
Example: avoid creating a DATA diskgroup on a couple of disks that are shared among ALL your databases.
2. Consider using EXTERNAL disk redundancy for your DEV databases (especially the REDO logs) to reduce IO load provided that
3. Stagger the DBMS job schedules across multiple DBs so that the workload is more evenly distributed throughout the day,
especially if the jobs are of a similar nature and all run in the say, 4-7AM window.

Hope this helps! Rupam

Disk IO rate on Oracle Database Sever

From Unix Server

iostat -d -m

iostat -d /dev/sdc -m -x 2

   where

         /dev/sdc is the name of the disk  ( refer my earlier post on how to map ASM device to physical disks)

         await is disk response time

         svctm is time to process i/o request by disk

hostname : tiger

From OEM:

Targets / tiger / performance / under the Disk I/O utilization click ‘Total I/Os per second’ / change view data to last 31 days. See statistics on the left side of the page.

In OEM:

Targets / +ASM_tiger / Performance / Bottom of the page – Disk Group I/O Cumulative Statistics

OEM is Targets / Hosts. Click the Total IO/sec header to sort the list to see what other hosts are similar to tiger. Scroll down to find tiger . This is the same variable as noted above but for the last 24 hours (instead of last 31 days).

Hope this help! Rupam

Unable to start database - ORA-09925: Unable to create audit trail file

Error

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 28: No space left on device

Symptom

Unable to start database

Cause

----snip---

Could not open audit file: /ora01/grid/11.2.0.2/grid/rdbms/audit/+asm2_ora_25189_b.aud

Retry Iteration No: 1 OS Error: 28

Retry Iteration No: 2 OS Error: 28

Retry Iteration No: 3 OS Error: 28

Retry Iteration No: 4 OS Error: 28

Retry Iteration No: 5 OS Error: 28

OS Audit file could not be created; failing after 5 retries

Solution :

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms > du -ks audit

355614 audit

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms > cd audit

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms/audit > find . -name "*.aud" |wc -l

340729

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms/audit > find . -name "*.aud" \( -mtime +3 -o -atime +3 \) | xargs rm

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms/audit > find . -name "*.aud" |wc -l

25701

Hope this help. Regards Rupam

Disk IO rate on Oracle Database Sever

iostat -d -m

iostat -d /dev/sdc -m -x 2

   where

         /dev/sdc is the name of the disk  ( refer my earlier post on how to map ASM device to physical disks)

         await is disk response time

         svctm is time to process i/o request by disk

Hope this help! Rupam

How to find mapping of ASM disks to Physical Devices?

1.Login as oracle and list ASM diskgroups

$ oracleasm listdisks

DATA1

2. Query Disk

$ oracleasm querydisk -d  DATA1

Disk "DATA1" is a valid ASM disk on device [8, 33]

3. oracleasm querydisk  DATA1  you will get in addition the major - minor numbers, that can be used to match the physical device,

# ls -l /dev |grep 8|grep 33

brw-r----- 1 root disk     8,  33 Aug  2 16:10 sdc1

Hope this help! Rupam

FlashBack Restore Point in oracle

1.  Requirements for Guaranteed Restore Points

The COMPATIBLE initialization parameter must be set to 10.2 or greater.

The database must be running in ARCHIVELOG mode.

A flash recovery area must be configured Guaranteed restore points use a mechanism similar to flashback logging. Oracle must store the required logs in the flash recovery area.

Oracle 10.2

If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point

SQL> ALTER DATABASE FLASHBACK ON;

Oracle 11.x

There is no need to mount the database. Flashback can be tunred on at open state.

SQL> ALTER DATABASE FLASHBACK ON;

2. Creating Restore points [CREATE RESTORE POINT]

1 2 3 4 5 6 7

# Create Normal restore points SQL> CREATE RESTORE POINT before_upgrade; #Create guaranteed restore points SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

3. Listing restore points [V$RESTORE_POINT]

1 2 3 4 5 6 7

# To see a list of the currently defined restore points SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,     GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; #To view only the guaranteed restore points: SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,     GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT     WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.

4. Dropping restore points [DROP RESTORE POINT]

1 2	#Same statement is used to drop both normal and guaranteed restore points. SQL> DROP RESTORE POINT before_app_upgrade;

5. Turn of Flashback

SQL> ALTER DATABASE FLASHBACK OFF;

Hope this helps! Regards Rupam

what is locking the oracle account

steps:-

1.       Login to sqlplus as sysdba

2.       enable trace, using

                   alter system set events ‘1017 trace name errorstack level 10’;

3.       Show parameter dump

4.       Goto the udump location

5.       grep ORA – 01017 *

6.       get the timestamp and related information  from trace generated

7.       When trace is no more needed , set it off using:

              alter system set events ‘1017 trace name errorstack off’

8.       Goto the listener.log file location

9.       Get the connectivity information for the selected time from listener log file.

Hope this help. Regards Rupam

Rman: Ora-01008 When Connecting To Target in 11.2.0.2

Metalink note : [ID 1280447.1]

symptons

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup plus archivelog command at 04/26/2011 19:00:15

RMAN-03014: implicit resync of recovery catalog failed

ORA-01008: not all variables bound

Cause

It's a bug with a patch

Solution

It's a bug with a patch. Workaround is to :

SQL> alter system flush shared_pool;

Hope this helps! Rupam

How to remove control characters from text file

tr -d '\015\032' < dosformatfile.txt > unixformatfile.txt

The above command deletes all ^M and ^Z

Using Perl:

perl -i -ep 's/\015//g' filename

perl -pi.bak -0777 -e 's#\r##gi' filename

using sed:

sed -e "s/^M/" filename > newfilename

press ctrl them V & M

Using vi:

vi filename

:s/^M//g

press control then V & M

with:

:%s/^M/\r/g

works perfectly !!!

Heres another little script

#!/bin/sh

FILE="$1"

# Use sed with the -i command line for inline interpreting.

sed -i '' "s/\r//g" $FILE

# the end

Hope this helps. Rupam

Killing the Oracle DBMS JOB

Summary

1. Find the Job You Want to Bring Down
2. Mark the DBMS_JOB as Broken
3. Kill the Oracle Session

4. Kill the O/S Process
5. Check if the Job is Still Running

6. Determine the Current Number of Job Queue Processes
7. Alter the Job Queue to Zero
8. Validate that No Processes are Using the Job Queue
9. Mark the DBMS_JOB as Not Broken
10. Alter the Job Queue to Original Value
11. Validate that DBMS_JOB Is Running

scheduled_dbms_jobs.sql

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

What Jobs are Actually Running

Returns sid of the jobs running

running_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

What Sessions are Running the Jobs
returned values
spid  is the operating system process identifier and sid, serial# which is the session id and session  serial number.

session_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down

In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the session_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.

2. Mark the DBMS_JOB as Broken
Use the following command  to break the  job. It won’t stop the running job, just marks it broken

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

NOTE :  you will need to mark the jobs as unbroken when the database comes back up, more on that later.

3. Kill the Oracle Session

Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.

ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process

More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

The orakill is an Oracle command, while kill is a Unix command.

5. Check if the Job is Still Running

Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.

8. Validate that No Processes are Using the Job Queue

Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.

9. Mark the DBMS_JOB as Not Broken

You can now reset the broken job to not broken so they can run again. Just issue the command.

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):

10. Alter the Job Queue to Original Value

Set the job queue to its' original value so that the jobs can run again.

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running

To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.

Hope this Help! Rupam

Restore oracle database on another server

Steps (1 to 10) to restore database on another server

1. create directory structure

2. add database to /etc/oratab or /var/opt/oracl/oratab

3. copy init.ora file from source

4. edit init.ora – replace controlfile name with diskgroup name such as ORADATA

5. startup nomount

6. start restore using restore shell script

  (NOTE : This will restore archive log files too)

7. monitor alert.log and restore log

8. after restore and recover completes

9. edit inot.ora file - replace controlfile parameter with real name

10. startup database; open for business

 Hope this helps! Rupam

SQL Profile – create manually

Symptom

Sql execution is too long

Cause

After automatic statistics collection on table, the execution plan changed. It is no more picking the index and doing full table scan.

solution

Step 1. Find the SQL ID that needs a profile,

               in this example: 50ux45v27k6ab

Step 2. Find the hint that introduces a good plan

               In this example: INDEX(TRAIN_SHEET_OSPOINT PK_TRAIN_SHEET_OSPOINT)

Step 3. Run following anonymous PLQSL block

DECLARE

cl_sql_text CLOB; 

BEGIN

SELECT sql_text  

INTO cl_sql_text  

FROM gv$sqlarea where sql_id = '50ux45v27k6ab' and rownum = 1; 

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => cl_sql_text,  

profile => sqlprof_attr(‘INDEX(TRAIN_SHEET_OSPOINT PK_TRAIN_SHEET_OSPOINT)'),  

name => 'USE_PK_FOR_UPDATE',  

category => 'DEFAULT', 

force_match => TRUE); 

end; 

/

Hope this helps! Rupam

Flashback Cheatsheet

Enabling/disabling Logging for Flashback Database

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE FLASHBACK OFF;

Estimating Disk Space Requirements for Flashback Database Logs

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

By default, flashback logs are generated for all permanent tablespaces.

SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;

SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;

Backup database

RMAN> backup database plus archivelog;

Backup flash recovery area

RMAN> backup recovery area;

Flashback usage

sql> select * from v$recovery_file_dest;

sql> select * from v$flash_recovery_area_usage;

Create Restore Point

sql> create restore point rp01;

sql> select name from v$restore_point;

sql> drop restore point rp01;

sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

        FROM V$RESTORE_POINT

      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

-- Determining the Current Window for Flashback Database

sql> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME

      FROM V$FLASHBACK_DATABASE_LOG;

Restore to Restore Point using RMAN

RMAN> SHUTDOWN IMMEDIATE;

RMAN> STARTUP MOUNT;

RMAN> LIST RESTORE POINT ALL;

RMAN> LIST RESTORE POINT  RP03;

RMAN> flashback database  to  restore point rp01;

RMAN> alter database open resetlogs;

RMAN> FLASHBACK DATABASE TO SCN 46963;

RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY'; # VERIFY CHANGES

Restore to Restore Point using SQL

sql> shutdown immediate

sql> startup mount

sql> flashback database  to  restore point rp01;

sql> alter database open resetlogs;

Options After Flashback Database to the Wrong Time

RMAN> FLASHBACK DATABASE TO SCN 42963;  #earlier than current SCN

RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN

RMAN>  RECOVER DATABASE;

Performing Flashback Database to Undo an OPEN RESETLOGS

sql> select resetlogs_change# from v$database;

sql> select oldest_flashback_scn from v$flashback_database_log;

sql> select resetlogs_change# from v$database;

sql> select oldest_flashback_scn from v$flashback_database_log;

Flashback Database To The Right of Open Resetlogs

sql> select oldest_flashback_scn from v$flashback_database_log;

SQL> select prior_incarnation# from v$database_incarnation where status = 'CURRENT';

RMAN> LIST INCARNATION OF DATABASE trgt;

RMAN> RESET DATABASE TO INCARNATION 1;

RMAN> FLASHBACK DATABASE TO SCN 1500;

Flashback Table

   -- Prerequisites for Using Flashback Table

sql> ALTER TABLE table ENABLE ROW MOVEMENT;

sql> SELECT, INSERT, DELETE, and ALTER privileges on the table.

 Performing Flashback Table

sql> FLASHBACK TABLE EMP TO SCN 123456;

sql> FLASHBACK TABLE EMP TO TIMESTAMP

      TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Viewing, Querying and flashback  Objects in the Recycle Bin

SQL> show recyclebin;

SQL> SELECT object_name as recycle_name, original_name, type

     FROM recyclebin;

SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";

SQL> FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

OR

sql> FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP

     RENAME TO hr.int2_admin_emp;

Hope this helps! Regards Rupam