Sunday, June 22, 2014

Recovery of the database when any one of the controlfile is lost


In this article, we will see how to recover the database when one of the multiplexed controlfile is lost. 
Controlfile is the critical component of Oracle database and this stores the names and location of the data files, log files, database name, block size, RMAN catalog information and character set.

              1.       List the controlfiles in the database.
SQL> select name from v$controlfile;

NAME
-----------------------------------------------------------------
/data1/noasmdb/redofiles/NOASMDB/control01.ctl
/data1/noasmdb/control02.ctl
/data1/noasmdb/redofiles/NOASMDB/controlfile/control03.ctl


2.       Next, we will see the status of the datafile in v$datafile and v$datafile_header views.
SQL> select file#,name,STATUS,TS#,block_size from v$datafile;

     FILE# NAME                                                              STATUS         TS# BLOCK_SIZE
---------- ----------------------------------------------------------------- ------- ---------- ----------
         1 /data1/noasmdb/datafile/system01.dbf                              SYSTEM           0       8192
         2 /data1/noasmdb/datafile/sysaux01.dbf                              ONLINE           1       8192
         3 /data1/noasmdb/datafile/testbkp01.dbf                             ONLINE           7       8192
         4 /data1/noasmdb/datafile/users01.dbf                               ONLINE           4       8192
         5 /data1/noasmdb/datafile/undotbs01.dbf                             ONLINE           6       8192
         6 /data1/noasmdb/datafile/newtest01.dbf                             ONLINE           8       8192
         7 /data1/noasmdb/datafile/novalidtbs01.dbf                          ONLINE           9       8192

7 rows selected.


SQL> select file#,tablespace_name,name,status from v$datafile_header;

     FILE# TABLESPACE_NAME                NAME                                                              STATUS
---------- ------------------------------ ----------------------------------------------------------------- -------
         1 SYSTEM                         /data1/noasmdb/datafile/system01.dbf                              ONLINE
         2 SYSAUX                         /data1/noasmdb/datafile/sysaux01.dbf                              ONLINE
         3 TEST_BKP                       /data1/noasmdb/datafile/testbkp01.dbf                             ONLINE
         4 USERS                          /data1/noasmdb/datafile/users01.dbf                               ONLINE
         5 UNDOTBS                        /data1/noasmdb/datafile/undotbs01.dbf                             ONLINE
         6 NEWTEST                        /data1/noasmdb/datafile/newtest01.dbf                             ONLINE
         7 NOVALIDTBS                     /data1/noasmdb/datafile/novalidtbs01.dbf                          ONLINE

7 rows selected.

            3.       Next, we will remove one of the control file “control02.ct1” .
[oracle@vm1 noasmdb]$ ls -ltrh
total 10M
drwxr-xr-x 2 oracle oinstall 4.0K Feb 11 14:43 datafile
drwxr-xr-x 2 oracle oinstall 4.0K Feb 14 12:32 archivelog
drwxr-xr-x 3 oracle oinstall 4.0K Feb 14 12:49 redofiles
-rw-r----- 1 oracle oinstall 9.9M Feb 14 14:01 control02.ctl
[oracle@vm1 noasmdb]$ rm control02.ctl
[oracle@vm1 noasmdb]$


              4.       Lets try to query v$datafile, v$datafile_header views now.  I am able to query the views . This mean that the database is operating fine. We will be getting alert log errors also.
SQL> select file#,name,STATUS,TS#,block_size from v$datafile;

     FILE# NAME                                                              STATUS         TS# BLOCK_SIZE
---------- ----------------------------------------------------------------- ------- ---------- ----------
         1 /data1/noasmdb/datafile/system01.dbf                              SYSTEM           0       8192
         2 /data1/noasmdb/datafile/sysaux01.dbf                              ONLINE           1       8192
         3 /data1/noasmdb/datafile/testbkp01.dbf                             ONLINE           7       8192
         4 /data1/noasmdb/datafile/users01.dbf                               ONLINE           4       8192
         5 /data1/noasmdb/datafile/undotbs01.dbf                             ONLINE           6       8192
         6 /data1/noasmdb/datafile/newtest01.dbf                             ONLINE           8       8192
         7 /data1/noasmdb/datafile/novalidtbs01.dbf                          ONLINE           9       8192

7 rows selected.



Fri Feb 14 14:04:40 2014
Errors in file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_m000_10799.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data1/noasmdb/control02.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


This concludes that this controlfile is not a subset and it is not the main file used by the datafiles.


5.       Now, lets remove another control file and see.
[oracle@vm1 NOASMDB]$ ls -ltrh
total 10M
drwxr-x--- 2 oracle asmadmin 4.0K Feb  8 20:58 onlinelog
drwxr-x--- 2 oracle asmadmin 4.0K Feb  8 21:24 datafile
drwxr-x--- 2 oracle asmadmin 4.0K Feb 14 12:48 controlfile
-rw-r----- 1 oracle oinstall 9.9M Feb 14 14:07 control01.ctl
[oracle@vm1 NOASMDB]$ rm control01.ctl


6.       Now, when we try to login using RMAN , we get following error.  But the database is not crashed as we were able to query.
[oracle@vm1 backup]$ rman target sys/sys catalog=rman_cat/rman_cat@testdb

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 14 14:11:46 2014

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Also, when you try for a fresh SQLPLUS session to connect, we get the following error.

SQL> select file#,tablespace_name,name,status from v$datafile_header;
select file#,tablespace_name,name,status from v$datafile_header
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Now, we have simulated in losing of one of the controlfile.

7.       Next, we have to see the parameter of control file to check the locations of the controlfiles.
SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /data1/noasmdb/redofiles/NOASM
                                                 DB/control01.ctl, /data1/noasm
                                                 db/control02.ctl, /data1/noasm
                                                 db/redofiles/NOASMDB/controlfi
                                                 le/control03.ctl


8.       The only option is to copy the unaffected controlfile  “control03.ctl” to all these locations and rename it.  To do this, we need to shutdown the database. Even the “shutdown immediate” will not happen.
SQL> shut immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shut abort;
ORACLE instance shut down.

[oracle@vm1 controlfile]$ cp control03.ctl /data1/noasmdb/redofiles/NOASMDB/control03.ctl
[oracle@vm1 controlfile]$ cp control03.ctl /data1/noasmdb/control03.ctl
[oracle@vm1 controlfile]$ cd /data1/noasmdb/redofiles/NOASMDB/
[oracle@vm1 NOASMDB]$ ls
[oracle@vm1 noasmdb]$ pwd
/data1/noasmdb
[oracle@vm1 noasmdb]$ mv control03.ctl control02.ctl
[oracle@vm1 controlfile]$ cd /data1/noasmdb/redofiles/NOASMDB/
[oracle@vm1 NOASMDB]$ mv control03.ctl control01.ctl


9.       Let’s start the database now and see what happens.
SQL> startup
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2227456 bytes
Variable Size             188744448 bytes
Database Buffers           67108864 bytes
Redo Buffers                4968448 bytes
Database mounted.
Database opened.

  Now, we are able to query the database.
SQL> select file#,tablespace_name,name,status from v$datafile_header;

     FILE# TABLESPACE_NAME                NAME                                                              STATUS
---------- ------------------------------ ----------------------------------------------------------------- -------
         1 SYSTEM                         /data1/noasmdb/datafile/system01.dbf                              ONLINE
         2 SYSAUX                         /data1/noasmdb/datafile/sysaux01.dbf                              ONLINE
         3 TEST_BKP                       /data1/noasmdb/datafile/testbkp01.dbf                             ONLINE
         4 USERS                          /data1/noasmdb/datafile/users01.dbf                               ONLINE
         5 UNDOTBS                        /data1/noasmdb/datafile/undotbs01.dbf                             ONLINE
         6 NEWTEST                        /data1/noasmdb/datafile/newtest01.dbf                             ONLINE
         7 NOVALIDTBS                     /data1/noasmdb/datafile/novalidtbs01.dbf                          ONLINE

7 rows selected.

SQL> select file#,name,STATUS,TS#,block_size from v$datafile;

     FILE# NAME                                                              STATUS         TS# BLOCK_SIZE
---------- ----------------------------------------------------------------- ------- ---------- ----------
         1 /data1/noasmdb/datafile/system01.dbf                              SYSTEM           0       8192
         2 /data1/noasmdb/datafile/sysaux01.dbf                              ONLINE           1       8192
         3 /data1/noasmdb/datafile/testbkp01.dbf                             ONLINE           7       8192
         4 /data1/noasmdb/datafile/users01.dbf                               ONLINE           4       8192
         5 /data1/noasmdb/datafile/undotbs01.dbf                             ONLINE           6       8192
         6 /data1/noasmdb/datafile/newtest01.dbf                             ONLINE           8       8192
         7 /data1/noasmdb/datafile/novalidtbs01.dbf                          ONLINE           9       8192

7 rows selected.


[oracle@vm1 backup]$ rman target sys/sys catalog=rman_cat/rman_cat@testdb

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 14 14:27:16 2014

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

connected to target database: NOASMDB (DBID=1704132256)
connected to recovery catalog database

RMAN>



Conclusion

If one of the multiplexed control file is lost, we need to perform following.

1.       Shutdown the database
2.       Copy the unaffected files.
3.       Start the database.


Posted by at No comments:
Labels:

Step by Step procedure to restore database to another host using RMAN

Before going to restore the database, we need to install the binaries for Oracle database and ASM (if used for storage).  Also, create the required directories like adump,bdump, cdump, pfile, redo log files, etc. The scenario is as follows.

Ø  The database is old server got crashed and this was using Server Parameter file (SPfile)
Ø  We have another fresh server with media manager installed to make the facility restore from media manager.
Ø  The database name of the new db should be as old database which got crashed.
Ø  We have all recoverable backups on tape of all datafiles.
Ø  We have backups of all archived logs to recover the datafiles.
Ø  We have controlfile and spfile backup available on tape.

1.       To restore a database to another host using RMAN
Enter the following commands to obtain the db_id value (database ID) of the database you want to restore from the RMAN catalog:

sqlplus <rman user>/<rman password>@<rman service>

SQL> select db_key, db_id, bs_key, recid, stamp, backup_type, start_time, status from rc_backup_set;
Identify the db_id value corresponding to the database you want to restore.

If you are not able to access rman catalog, the another method is to find the db id from the controlfile autobackup format configured to disk using


CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
  FOR DEVICE TYPE DISK TO '?/oradata/cf_%F';


The format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.

YYYYMMDD is a time stamp of the day the backup is generated

QQ is the hex sequence that starts with 00 and has a maximum of FFFor example, you can run the following command:

By default, the format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.

YYYYMMDD is a time stamp of the day the backup is generated

QQ is the hex sequence that starts with 00 and has a maximum of FF

2.       Set the ORACLE_SID environment variable on Target Host to the same value used on Original/old/source host
[oracle@vm1 backup]$ export ORACLE_SID=trgt db_name>

3.       Start RMAN and connect to the target instance without connecting to the recovery catalog.
[oracle@vm1 backup]$ rman target / nocatalog

4.       Enter the following command to start the destination database with the nomount option:

RMAN> set dbid=<source database db_id value>;

RMAN> startup nomount;

RMAN will fail to find the server parameter file, which has not yet been restored, but will start the instance with a "dummy" file. Sample output follows:

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittrgt.ora'
starting Oracle instance without parameter file for retrieval of spfile

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                104859368 bytes
Database Buffers              46137344 bytes
Redo Buffers                   5439488 bytes

5.       Restore and edit the server parameter file.

Because you enabled the control file autobackup feature when making your backups, the server parameter file is included in the backup sets.

Allocate a channel to the media manager, then restore the server parameter file (SPFILE) as a client-side pararameter file (PFILE).

RMAN> run
{ allocate channel c1 device type sbt_tape PARMS='ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=trgt,OB2BARLIST=Hot-backup,OB2BARHOSTNAME=newhost)';
restore spfile to pfile '/u01/app/oracle/admin/trgt/pfile/inittrgt.ora' from autobackup;
shutdown abort;
                             }

6.       Next, edit the restored PFILE. Change any location-specific parameters, for example, those ending in _DEST and _PATH, to reflect the new directory structure. For example, edit the following parameters:

- IFILE
  - *_DUMP_DEST
  - LOG_ARCHIVE_DEST*
  - CONTROL_FILES

7.       Then restart the instance, using the edited PFILE:

startup force nomount ='/u01/app/oracle/admin/trgt/pfile/inittrgt.ora';

8.       Restore the control file from an autobackup and then mount the database. RMAN restores the control file to whatever location you specified in the CONTROL_FILES initialization parameter. For example:

RUN
{
  allocate channel c1 device type sbt_tape PARMS='ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=trgt,OB2BARLIST=Hot-backup,OB2BARHOSTNAME=newhost)'; 
restore controlfile from autobackup;
alter database mount;
}


9.       Query the database filenames recorded in the control file on the new host. Note, the controlfile was restored in step 8. 
sqlplus ‘ / as sysdba’

SQL > set lines 150
SQL > col name format a60
SQL> select file# ,name from v$datafile;
SQL> col member format a60
SQL >select group#,member from v$logfile;
SQL> exit


10.   Now, from the above output , we need create RMAN script to recover the database.
Ø  For each datafile, if we need to have different path in the new host, we need to user SET NEWNAME command to specify the different path in the host.  (If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.)
Ø  For each online redo log that is to be created at a different location than it had on the source host, use SQL ALTER DATABASE RENAME FILE commands to specify the pathname on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.)
Ø  Perform a SET UNTIL to limit media recovery to the end of the archived redo logs.
Ø  Run SWITCH so that the control file recognizes the new path names as the official new names of the datafiles
Ø  Restore and recover the database


[oracle@vm1 ~]$ export ORACLE_SID=trgt
[oracle@vm1 ~]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 20 11:57:26 2014

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

The RMAN command to execute is as follows when we want to rename the datafiles and redo log file locations.

RUN
{
  # allocate a channel to the tape device
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS=''ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=trgt,OB2BARLIST=Hot-backup,OB2BARHOSTNAME=newhost)'';

  # rename the datafiles and online redo logs
  SET NEWNAME FOR DATAFILE 1 TO '/data1/trgt/datafile/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO /data1/trgt/datafile/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/data1/trgt/datafile/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/data1/trgt/datafile/users01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/data1/trgt/datafile/newtest01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/data1/trgt/datafile/blkcorrupt.dbf';
  SET NEWNAME FOR DATAFILE 7 TO '/data1/trgt/datafile/test_uncommit01.dbf';
  SET NEWNAME FOR DATAFILE 8 TO '/data1/trgt/datafile/users01.dbf';
  SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g01a.log''
      TO ''/data1/trgt/redofiles/redo01b.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g01b.log''
      TO ''/data1/trgt/redofiles/redo01b.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g02a.log''
      TO ''/data1/trgt/redofiles/redo02a.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g02b.log''
      TO ''/data1/trgt/redofiles/redo02b.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g03a.log''
      TO ''/data1/trgt/redofiles/redo03a.log'' ";
 SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g03b.log''
      TO ''/data1/trgt/redofiles/redo03b.log'' ";

  # restore the database and switch the datafile names
  RESTORE DATABASE;
  SWITCH DATAFILE ALL;
 
  # recover the database
  RECOVER DATABASE;
}


RMAN commands to restore & recover the database in the same path .

RMAN> restore database;

RMAN>recover database;


11.   Open the database with RESETLOGS options.

RMAN>alter database open resetlogs;


12.       Once the database is opened, take a fresh backup immediately.
Posted by at No comments:
Labels:

Thursday, June 19, 2014

Enqueue waits in Oracle 10g


 I was running a process with lots of parallel sessions. But the process did not complete and causing serious performance issues.  I was trying to find the wait event and it was held up with enqueue lock by 1 session and the process was not moving forward. 

I tried to find what enqueues are and what the types of enqueues we have in Oracle are.
Oracle enqueue waits indicate that a seesion is waiting for a lock that is held by another user (or sessions).

This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode.

For Oracle 10g and later, the Enqueue wait event has been separated into more than 200 unique wait events, which each include more specific information about the related lock type. In 11g, we have more additional wait events (apprx 300 unique wait events).

The enqueues wait events are available in table called x$ksqst and the aggregated table is v$enqueue_stat.

select 'enq:'||ksqsttyp||'-'||ksqstrsn ||' ' "Equeue Type",ksqstexpl "Description " from x$ksqst order by ksqsttyp;

Equeue locks
Description
enq:AD-deallocate AU
Synchronizes accesses to a specific ASM disk AU
enq:AD-allocate AU
Synchronizes accesses to a specific ASM disk AU
enq:AF-task serialization
This enqueue is used to serialize access to an advisor task
enq:AG-contention
Synchronizes generation use of a particular workspace
enq:AM-rollback COD reservation
Reserve a Rollback COD entry
enq:AM-shutdown
Prevent DB instance registration during ASM instance shutdown
enq:AM-client registration
Registers DB instance to ASM client state object hash
enq:AO-contention
Synchornizes access to objects and scalar variables
enq:AS-service activation
Synchronizes new service activation
enq:AS-modify service
modifying service attributes
enq:AT-contention
Serializes 'alter tablespace' operations
enq:AU-audit index file
lock held to operate on the audit index file
enq:AW-AW generation lock
In-use generation state for a particular workspace
enq:AW-user access for AW
Synchronizes user accesses to a particular workspace
enq:AW-AW state lock
Row lock synchronization for the AW$ table
enq:AW-AW$ table lock
Global access synchronization to the AW$ table
enq:BF-allocation contention
Allocate a bloom filter in a parallel statement
enq:BF-PMON Join Filter cleanup
PMON bloom filter recovery
enq:BR-proxy-copy
Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
enq:BR-file shrink
Lock held to prevent file from decreasing in physical size during RMAN backup
enq:CF-contention
Synchronizes accesses to the controlfile
enq:CI-contention
Coordinates cross-instance function invocations
enq:CL-drop label
Synchronizes accesses to label cache when dropping a label
enq:CL-compare labels
Synchronizes accesses to label cache for label comparison
enq:CM-gate
serialize access to instance enqueue
enq:CM-instance
indicate ASM diskgroup is mounted
enq:CN-race with init
during descriptor initialization
enq:CN-race with reg
during transaction commit to see concurrent registrations
enq:CN-race with txn
during registration
enq:CT-state change gate 2
Lock held while enabling or disabling change tracking in RAC
enq:CT-reading
Lock held to ensure that change tracking data remains in 
existence until a reader is done with it
enq:CT-state change gate 1
Lock held while enabling or disabling change tracking in RAC
enq:CT-state
Lock held while enabling or disabling change tracking, 
to ensure that it is only enabled or disabled by one user at a time
enq:CT-change stream ownership
Lock held by one instance while change tracking is 
 enabled, to guarantee access to thread-specific resources
enq:CT-local space management
Lock held during change tracking space management 
 operations that affect just the data for one thread
enq:CT-global space management
Lock held during change tracking space management 
 operations that affect the entire change tracking file
enq:CT-CTWR process start/stop
Lock held to ensure that only one CTWR process is started in a single instance
enq:CU-contention
Recovers cursors in case of death while compiling
enq:DB-contention
Synchronizes modification of database wide supplementallogging attributes
enq:DD-contention
Synchronizes local accesses to ASM disk groups
enq:DF-contention
Enqueue held by foreground or DBWR when 
a datafile is brought online in RAC
enq:DG-contention
Synchronizes accesses to ASM disk groups
enq:DL-contention
Lock to prevent index DDL during direct load
enq:DM-contention
Enqueue held by foreground or DBWR to syncrhonize 
 database mount/open with other operations
enq:DN-contention
Serializes group number generations
enq:DP-contention
Synchronizes access to LDAP parameters
enq:DR-contention
Serializes the active distributed recovery operation
enq:DS-contention
Prevents a database suspend during LMON reconfiguration
enq:DT-contention
Serializes changing the default temporary table spaceand user creation
enq:DV-contention
Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
enq:DX-contention
Serializes tightly coupled distributed transaction branches
enq:FA-access file
Synchronizes accesses to open ASM files
enq:FB-contention
Ensures that only one process can format data blcoks 
 in auto segment space managed tablespaces
enq:FC-recover an ACD thread
SMON recovers an ACD thread
enq:FC-open an ACD thread
LGWR opens an ACD thread
enq:FD-Marker generation
Synchronization
enq:FD-Tablespace flashback on/off
Synchronization
enq:FD-Flashback coordinator
Synchronization
enq:FD-Flashback on/off
Synchronization
enq:FD-Restore point create/drop
Synchronization
enq:FG-serialize ACD relocate
only 1 process in the cluster may do ACD relocation in a disk group
enq:FG-LGWR redo generation enq race
resolve race condition to acquire Disk Group Redo Generation Enqueue
enq:FG-FG redo generation enq race
resolve race condition to acquire Disk Group Redo Generation Enqueue
enq:FL-Flashback db command
Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
enq:FL-Flashback database log
Synchronization
enq:FM-contention
Synchronizes access to global file mapping state
enq:FP-global fob contention
Synchronizes various File Object(FOB) operations
enq:FR-contention
begin recovery of disk group
enq:FS-contention
Enqueue used to synchronize recovery and file operations or synchronize dictionary check
enq:FT-allow LGWR writes
allow LGWR to generate redo in this thread
enq:FT-disable LGWR writes
prevent LGWR from generating redo in this thread
enq:FU-contention
This enqueue is used to serialize the capture of the DB Feature           
Usage and High Water Mark Statistics
enq:HD-contention
Serializes accesses to ASM SGA data structures
enq:HP-contention
Synchronizes accesses to queue pages
enq:HQ-contention
Synchronizes the creation of new queue IDs
enq:HV-contention
Lock used to broker the high water mark during parallel inserts
enq:HW-contention
Lock used to broker the high water mark during parallel inserts
enq:IA-contention

enq:ID-contention
Lock held to prevent other processes from performing controlfile 
transaction while NID is running
enq:IL-contention
Synchronizes accesses to internal label data structures
enq:IM-contention for blr
Serializes block recovery for IMU txn
enq:IR-contention2
Synchronizes parallel instance recovery and shutdown immediate
enq:IR-contention
Synchronizes instance recovery
enq:IS-contention
Enqueue used to synchronize instance state changes
enq:IT-contention
Synchronizes accesses to a temp object's metadata
enq:JD-contention
Synchronizes dates between job queue coordinator and slave processes
enq:JI-contention
Lock held during materialized view operations (like refresh, alter) 
to prevent concurrent operations on the same materialized view
enq:JQ-contention
Lock to prevent multiple instances from running a single job
enq:JS-evt notify
Lock got during event notification
enq:JS-sch locl enqs
Scheduler non-global enqueues
enq:JS-q mem clnup lck
Lock obtained when cleaning up q memory
enq:JS-evtsub add
Lock got when adding subscriber to event q
enq:JS-evtsub drop
Lock got when dropping subscriber to event q
enq:JS-wdw op
Lock got when doing window open/close
enq:JS-job recov lock
Lock to recover jobs running on crashed RAC inst
enq:JS-job run lock - synchronize
Lock to prevent job from running elsewhere
enq:JS-contention
Synchronizes accesses to the job cache
enq:JS-queue lock
Lock on internal scheduler queue
enq:KK-context
Lock held by open redo thread, used by other instances to force a log switch
enq:KM-contention
Synchronizes various Resource Manager operations
enq:KO-fast object checkpoint
Coordinates fast object checkpoint
enq:KP-contention
Synchronizes kupp process startup
enq:KT-contention
Synchronizes accesses to the current Resource Manager plan
enq:MD-contention
Lock held during materialized view log DDL statements
enq:MH-contention
Lock used for recovery when setting Mail Host for AQ e-mail notifications
enq:MK-contention
changing values in enc$
enq:ML-contention
Lock used for recovery when setting Mail Port for AQ e-mail notifications
enq:MN-contention
Synchronizes updates to the LogMiner dictionary and prevents 
multiple instances from preparing the same LogMiner session
enq:MO-contention
Serialize MMON operations for restricted sessions
enq:MR-contention
Lock used to coordinate media recovery with other uses of datafiles
enq:MS-contention
Lock held during materialized view refresh to setup MV log
enq:MW-contention
This enqueue is used to serialize the calibration of the manageability 
schedules with the Maintenance Window
enq:OC-contention
Synchronizes write accesses to the outline cache
enq:OL-contention
Synchronizes accesses to a particular outline name
enq:OQ-xsoqhiAlloc
Synchronizes access to olapi history allocation
enq:OQ-xsoqhiClose
Synchronizes access to olapi history closing
enq:OQ-xsoqhistrecb
Synchronizes access to olapi history globals
enq:OQ-xsoqhiFlush
Synchronizes access to olapi history flushing
enq:OQ-xsoq*histrecb
Synchronizes access to olapi history parameter CB
enq:OW-termination
terminate the wallet context
enq:OW-initialization
initializing the wallet context
enq:PD-contention
Prevents others from updating the same property
enq:PE-contention
Synchronizes system parameter updates
enq:PF-contention
Synchronizes accesses to the password file
enq:PG-contention
Synchronizes global system parameter updates
enq:PH-contention
Lock used for recovery when setting Proxy for AQ HTTP notifications
enq:PI-contention
Communicates remote Parallel Execution Server Process creation status
enq:PL-contention
Coordinates plug-in operation of transportable tablespaces
enq:PR-contention
Synchronizes process startup
enq:PS-contention
Parallel Execution Server Process reservation and synchronization
enq:PT-contention
Synchronizes access to ASM PST metadata
enq:PV-syncstart
Synchronizes slave start_shutdown
enq:PV-syncshut
Synchronizes instance shutdown_slvstart
enq:PW-flush prewarm buffers
Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
enq:PW-perwarm status in dbw0
DBWR 0 holds enqueue indicating prewarmed buffers present in cache
enq:RB-contention
Serializes ASM rollback recovery operations
enq:RF-synchronization: chief
Anoints 1 instance's DMON as chief to other instances' DMONs
enq:RF-RF - Database Automatic Disable
Means for detecting when database is being automatically disabled
enq:RF-RF - FSFO Observed
Means for detecting whether database has been observed recently
enq:RF-atomicity
Ensures atomicity of log transport setup
enq:RF-synch: DG Broker metadata
Ensures r/w atomicity of DG configuration metadata
enq:RF-synchronization: HC master
Anoints 1 instance's DMON as healthcheck master
enq:RF-RF - FSFO synchronization
Means for synchronizing Fast-Start Failover lock operations
enq:RF-RF - FSFO connectivity
Means for detecting whether standby has connectivity to primary
enq:RF-RF - FSFO wait
Means for waiting for lock request to timeout
enq:RF-RF - FSFO state
Means for sharing Fast-Start Failover state changes around a RAC
enq:RF-synchronization: critical ai
Synchronizes critical apply instance among primary instances
enq:RF-new AI
Synchronizes selection of the new apply instance
enq:RF-synchronization: aifo master
Synchronizes apply instance failure detection and failover operation
enq:RN-contention
Coordinates nab computations of online logs during recovery
enq:RO-contention
Coordinates flushing of multiple objects
enq:RO-fast object reuse
Coordinates fast object reuse
enq:RP-contention
Enqueue held when resilvering is needed or when datablock is repaired from mirror
enq:RR-contention
Concurrent invocation of DBMS_WORKLOAD_* package API
enq:RS-read alert level
Lock held to read alert level
enq:RS-persist alert level
Lock held to make alert level persistent
enq:RS-prevent aging list update
Lock held to prevent aging list update
enq:RS-prevent file delete
Lock held to prevent deleting file to reclaim space
enq:RS-record reuse
Lock held to prevent file from accessing while reusing circular record
enq:RS-file delete
Lock held to prevent file from accessing during space reclaimation
enq:RS-write alert level
Lock held to write alert level
enq:RT-contention
Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
enq:RU-contention
Serializes rolling upgrade operations
enq:RU-waiting
Results of rolling upgrade CIC
enq:RW-MV metadata contention
Lock held by CREATE/ALTER/DROP materialized viewwhile 
updating materialized view flags in detail tables
enq:SB-contention
Synchronizes Logical Standby metadata operations
enq:SE-contention
Synchronizes transparent session migration operations
enq:SF-contention
Lock used for recovery when setting Sender for AQ e-mail notifications
enq:SH-contention
Should seldom see this contention as this Enqueue is always  acquired in no-wait mode
enq:SI-contention
Prevents multiple streams tabel instantiations
enq:SK-contention
Serialize shrink of a segment
enq:SQ-contention
Lock to ensure that only one process can replenish the sequence cache
enq:SR-contention
Coordinates replication / streams operations
enq:SS-contention
Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up
enq:ST-contention
Synchronizes space management activities in dictionary-managed tablespaces
enq:SU-contention
Serializes access to SaveUndo Segment
enq:SW-contention
Coordinates the 'alter system suspend' operation
enq:TA-contention
Serializes operations on undo segments and undo tablespaces
enq:TB-SQL Tuning Base Cache Load
Synchronizes writes to the SQL Tuning Base Existence Cache
enq:TB-SQL Tuning Base Cache Update
Synchronizes writes to the SQL Tuning Base Existence Cache
enq:TC-contention
Lock held to guarantee uniqueness of a tablespace checkpoint
enq:TC-contention2
Lock of setup of a unqiue tablespace checkpoint in null mode
enq:TD-KTF dump entries
KTF dumping time/scn mappings in SMON_SCN_TIME table
enq:TE-KTF broadcast
KTF broadcasting
enq:TF-contention
Serializes dropping of a temporary file
enq:TL-contention
Serializes threshold log table read and update
enq:TM-contention
Synchronizes accesses to an object
enq:TO-contention
Synchronizes DDL and DML operations on a temp object
enq:TQ-TM contention
TM access to the queue table
enq:TQ-INI contention
TM access to the queue table
enq:TQ-DDL contention
TM access to the queue table
enq:TS-contention
Serializes accesses to temp segments
enq:TT-contention
Serializes DDL operations on tablespaces
enq:TW-contention
Lock held by one instance to wait for transactions on all instances to finish
enq:TX-allocate ITL entry
Allocating an ITL entry in order to begin a transaction
enq:TX-index contention
Lock held on an index during a split to prevent other operations on it
enq:TX-contention
Lock held by a transaction to allow other transactions to wait for it
enq:TX-row lock contention
Lock held on a particular row by a transaction to prevent other transactions from modifying it
enq:UL-contention
Lock used by user applications
enq:US-contention
Lock held to perform DDL on the undo segment
enq:WA-contention
Lock used for recovery when setting Watermark for memory usage in AQ notifications
enq:WF-contention
This enqueue is used to serialize the flushing of snapshots
enq:WL-contention
Coordinates access to redo log files and archive logs
enq:WP-contention
This enqueue handles concurrency between purging and baselines
enq:WR-contention
Coordinates access to logs by Async LNS and ARCH/FG
enq:XH-contention
Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
enq:XQ-recovery
prevent relocation during _recovery_asserts checking
enq:XQ-relocation
wait for recovery before doing relocation
enq:XR-quiesce database
Lock held during database quiesce
enq:XR-database force logging
Lock held during database force logging mode
enq:XY-contention
Lock used for internal testing
enq:ZG-contention
Coordinates file group operations

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)