Real-Time SQL Monitoring

Onwards Oracle Database 11g

http://www.oracle.com/technetwork/database/manageability/sqlmonitor-084401.html

Read bind variable values of currently executing SQL

If you are running on Oracle 11.2+ and have the licenses for Oracle Diagnostics & Tuning Packs and when Oracle’s SQL monitoring feature actually kicks in, then you can use V$SQL_MONITOR to view the bind variable values of currently executing SQL. Normally the bind variable values live in the private memory (PGA) of a process, but when SQL Monitoring kicks in for a statement in Oracle 11.2, it will copy the current bind variable values to SGA (V$SQL_MONITOR), so these will be readable by other users (like you, who’s trying to troubleshoot a performance problem) as well.

http://tech.e2sn.com/oracle/troubleshooting/oracle-s-real-time-sql-monitoring-feature-v-sql_monitor

Advertisements

controlfile autobackup in 11.2.0.1

rman target /
RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f'; # default
sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER TABLESPACE HR
  2  ADD DATAFILE;

Tablespace altered.

-bash-3.2$ pwd
/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_07_12

-bash-3.2$ ls -ltr
total 56160
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:17 o1_mf_s_852740246_9w24l0hv_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:38 o1_mf_s_852741517_9w25ss1s_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:52 o1_mf_s_852742363_9w26n78n_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:09 o1_mf_s_852746957_9w2c3s9h_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:32 o1_mf_s_852748332_9w2dgoqq_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:56 o1_mf_s_852749758_9w2fv831_.bkp
-bash-3.2$
-bash-3.2$ ls -ltr | wc -l
7

“Within few minutes…”

-bash-3.2$ ll
total 65520
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:17 o1_mf_s_852740246_9w24l0hv_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:38 o1_mf_s_852741517_9w25ss1s_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:52 o1_mf_s_852742363_9w26n78n_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:09 o1_mf_s_852746957_9w2c3s9h_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:32 o1_mf_s_852748332_9w2dgoqq_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:56 o1_mf_s_852749758_9w2fv831_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 21:34 o1_mf_s_852759231_9w2q3bdk_.bkp
-bash-3.2$ ls -ltr | wc -l
8

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10643/rcmsynta010.htm#RCMRF113

Starting with Oracle 11g Release 2, RMAN creates a single autobackup file encompassing all of the structural changes that have occurred within a few minutes of each other rather than creating a new backup of the controlfile on each structural change to the database.

In 10g, as well, when you add a datafile to a tablespace, CONTROLFILE AUTOBACKUP creates a backup of the controlfile automatically when you alter the database structure (e.g. adding new datafile(s)).
However, if you are creating a tablespace with 10 datafiles (eg. “CREATE TABLESPACE x datafile ..” and then followed by 9 “ALTER TABLESPACE x ADD DATAFILE …” commands), you would find 10 distinct backups of the controlfile being “autocreated”.
So the “improvement” in 11g is that Oracle will wait “a few minutes” to see if you have multiple changes to the database structure before deciding on a controlfile autobackup. That way, you will likely see only 1 controlfile autobackup after having added all 10 datafiles.

Oracle introduced the SKIP READONLY option

Until 11gr1 by default, the restore command skipped datafiles associated with read-only tablespaces. If you needed read-only tablespaces restored, then you had to use the “check readonly” command or restore each read-only tablespace individually.
Have a look at the restore syntax on “Oracle Database Backup and Recovery Reference 10g Release 2 (10.2)” Part Number B14194-03  and compare it with the syntax used on “Oracle Database Backup and Recovery Reference 11g Release 1 (11.1) Part Number B28273-03” .

In the latter syntax Oracle introduced the SKIP READONLY option, inverting the previous logic: the SKIP READONLY does not restore read-only files.