SQL monitor hidden parameters

Parameter session value instance value description
_sqlmon_binds_xml_format     default     default     format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan     80     80     Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines     300     300     Number of plan lines beyond which a plan cannot be monitored
_sqlmon_recycle_time     60     60     Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold     5     5     CPU/IO time threshold before a statement is monitored. 0 is disabled

Now 11g OCP certified

Friends,

I have upgraded certification from 10g OCP to 11g OCP ...

Tail -f in windows

     We have inconvenience in looking alert log when it grows in windows machines as we do it in linux machines with tail -f. Oracle ADRCI utility provide this feature and is very useful. We can also use it via Notepad++ but what we have to switch window to see the last update.

Remember it is 11g feature.

Steps followed:

1. Set proper environment variables
2. login to adrci
3. set sid with set home as below
4. SHOW ALERT -TAIL -F

C:\Users\mmeerha>adrci

ADRCI: Release 11.2.0.2.0 - Production on Sun Aug 25 23:31:40 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "D:\ORACLE_DB"
adrci> show homes
ADR Homes:
diag\clients\user_mmeerha\host_813904677_80
diag\clients\user_SYSTEM\host_813904677_80
diag\rdbms\clonedb\clonedb
diag\rdbms\orcl\orcl
diag\rdbms\testdb\testdb
diag\tnslsnr\MMEERHA-IN\listener
adrci> set home diag\rdbms\testdb\testdb
adrci> SHOW ALERT -TAIL -F
2013-08-25 23:31:37.488000 +05:30
Archived Log entry 3579 added for thread 1 sequence 3672 ID 0x9a702651 dest 1:
2013-08-25 23:31:42.026000 +05:30
Thread 1 cannot allocate new log, sequence 3675
Checkpoint not complete
  Current log# 1 seq# 3674 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO01.LOG
2013-08-25 23:31:44.418000 +05:30
Thread 1 advanced to log sequence 3675 (LGWR switch)
  Current log# 2 seq# 3675 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO02.LOG
Archived Log entry 3580 added for thread 1 sequence 3673 ID 0x9a702651 dest 1:
2013-08-25 23:31:51.635000 +05:30
Archived Log entry 3581 added for thread 1 sequence 3674 ID 0x9a702651 dest 1:
2013-08-25 23:31:58.339000 +05:30
Thread 1 cannot allocate new log, sequence 3676
Checkpoint not complete
  Current log# 2 seq# 3675 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO02.LOG
2013-08-25 23:31:59.473000 +05:30
Thread 1 advanced to log sequence 3676 (LGWR switch)
  Current log# 3 seq# 3676 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO03.LOG
2013-08-25 23:32:07.646000 +05:30
Thread 1 advanced to log sequence 3677 (LGWR switch)
  Current log# 1 seq# 3677 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO01.LOG
2013-08-25 23:32:16.794000 +05:30
Thread 1 cannot allocate new log, sequence 3678
Checkpoint not complete
  Current log# 1 seq# 3677 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO01.LOG
2013-08-25 23:32:18.185000 +05:30
Archived Log entry 3582 added for thread 1 sequence 3675 ID 0x9a702651 dest 1:
2013-08-25 23:32:19.720000 +05:30
Thread 1 advanced to log sequence 3678 (LGWR switch)
  Current log# 2 seq# 3678 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO02.LOG
^C
C:\Users\mmeerha>

Happy reading, leave your comments/suggestion.

Reference:

Oracle® Database Utilities 11g Release 1 (11.1)

Compare AWR reports easily.

This is my new post exactly after '5 months, 3 weeks, 6 days' long break. I have joined most prestigious company for any DBA. New office, city, house... day by day new learning and unique by its nature. I will try to post my new learning here.

I have seen feature in OEM to compare two awr reports and today found a sql and functions for it AWR_DIFF_REPORT_HTML/ AWR_DIFF_REPORT_TEXT.

I ran in a windows 7, oracle 11.2.0.2 database.

The script is @?/rdbms/admin/awrddrpt. It will confirm two dbids and 4 snaps. easier to compare two reports.

Snap of a sample report.

Reference:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#BACEHJJI

alter database link .. 

Instead of DB link recreation after password change of remote schema from 11gr2 we can use “alter database link”

alter database link SCOTT_DBLINK connect to scott identified by abc;

Check the below link for detailed info.

http://aprakash.wordpress.com/2012/05/11/change-in-database-link-11gr2/