Sep 21st 2014

Locks in Oracle Database

Here are list of all locks available in Oracle database 11.2.0.1.0

S.No	Type	Description
1	WM	Synchronizes new WLM Plan activation
2	CI	Coordinates cross-instance function invocations
3	PR	Synchronizes process startup
4	AK	Lock used for internal testing
5	DI	Coordinates Global Enqueue Service deadlock detection
6	RM	Coordinates Global Enqueue Service resource remastering
7	PE	Synchronizes system parameter updates
8	PG	Synchronizes global system parameter updates
9	FP	Synchronizes various File Object(FOB) operations
10	RE	Synchronizes block repair/mirror resilvering operations
11	KD	Determine DBRM master
12	KM	Synchronizes various Resource Manager operations
13	KT	Synchronizes accesses to the current Resource Manager plan
14	CA	Lock used by IO Calibration
15	PV	Synchronizes slave start_shut
16	SP	Spare enqueue for one-off backports
17	FM	Synchronizes access to global file mapping state
18	XY	Lock used for internal testing
19	AS	Synchronizes new service activation
20	PD	Serializes property update
21	RU	Serilized rolling migration operations
22	MX	Used to synchronize storage server info across all nodes
23	SC	Coordinates system change number generation on multiple instances
24	CF	Synchronizes accesses to the controlfile
25	SW	Coordinates the 'alter system suspend' operation
26	DS	Prevents a database suspend during LMON reconfiguration
27	TC	Lock held to guarantee uniqueness of a tablespace checkpoint
28	PW	Coordinates Direct Loads with Prewarmed cache buffers
29	RO	Coordinates flushing of multiple objects
30	KO	Coordinates checkpointing of multiple objects
31	CR	Coordinates checkpointing of block range reuse
32	WS	Used by LogWriter to coordinate communication to standby databases
33	WR	Coordinates access to logs by Async LNS and ARCH/FG
34	WL	Coordinates access to redo log files and archive logs
35	RN	Coordinates nab computations of online logs during recovery
36	DF	Enqueue held by foreground or DBWR when a datafile is brought online in RAC
37	IS	Enqueue used to synchronize instance state changes
38	FS	Enqueue used to synchronize recovery and file operations or synchronize dictionary check
39	DM	Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
40	RP	Enqueue held when resilvering is needed or when datablock is repaired from mirror
41	MV	Synchronizes online datafile move operation or cleanup
42	RT	Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
43	KK	Lock held by open redo thread, used by other instances to force a log switch
44	IR	Synchronizes instance recovery
45	MR	Lock used to coordinate media recovery with other uses of datafiles
46	BR	Lock held by a backup/restore operation to allow other operations to wait for it
47	ID	Lock held by a NID operation to allow other operations to wait for it
48	AB	A general class of locks used by auto BMR for various purposes
49	MN	Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
50	PL	Coordinates plug-in operation of transportable tablespaces
51	SB	Synchronizes Logical Standby metadata operations
52	XR	Lock held during database quiesce or for database force logging
53	CT	A general class of locks used by change tracking for various purposes
54	RS	Lock held by a space reclaimable operation to allow other operations to wait for it
55	FL	Synchronize access to flashback database log
56	FD	Coordinate flashback database
57	FW	Coordinate RVWR on multiple instances
58	TM	Synchronizes accesses to an object
59	ST	Synchronizes space management activities in dictionary-managed tablespaces
60	TA	Serializes operations on undo segments and undo tablespaces
61	TX	Lock held by a transaction to allow other transactions to wait for it
62	TW	Lock held by one instance to wait for transactions on all instances to finish
63	US	Lock held to perform DDL on the undo segment
64	IM	Serializes block recovery for an IMU txn
65	TD	Serializes updates and inserts to the SMON_SCN_TIME mapping table
66	TE	Serializes broadcasts for flushes to SMON_SCN_TIME
67	CN	Enqueue held for registrations for change notifications
68	CO	Enqueue held for determining Master Slaves
69	FE	Flashback archive Enqueue to serialize recovery
70	TF	Serializes dropping of a temporary file
71	DT	Serializes changing the default temporary table space and user creation
72	HW	Lock used to broker the high water mark during parallel inserts
73	TS	Serializes accesses to temp segments
74	FB	Ensures that only one process can format data blocks in auto segment space managed tablespaces
75	SS	Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up
76	SK	Serialize shrink of a segment
77	DW	Serialize in memory dispenser operations
78	SU	Serializes access to SaveUndo Segment
79	TT	Serializes DDL operations on tablespaces
80	SM	Lock to check SMON global work in RAC
81	SJ	Serializes cancelling task executed by slave process
82	SQ	Lock to ensure that only one process can replenish the sequence cache
83	SV	Lock to ensure ordered sequence allocation in RAC mode
84	HV	Lock used to broker the high water mark during parallel inserts
85	DL	Lock to prevent index DDL during direct load
86	HQ	Synchronizes the creation of new queue IDs
87	HP	Synchronizes accesses to queue pages
88	KL	KSI lock for buffer cache and wgc concurrency
89	WG	Long term lock on wgc file state
90	SL	Request serialization to LCK0
91	ZH	Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
92	Q	Coordinates updates and accesses to row cache objects
93	DV	Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
94	SO	Synchronizes access to Shared Object(PL/SQL Shared Object Manager)
95	V	Synchronizes accesses to library cache objects
96	E	Synchronizes accesses to library cache objects
97	L	Synchronizes accesses to library cache objects
98	Y	Synchronizes accesses to the contents of library cache objects
99	G	Synchronizes accesses to the contents of library cache objects
100	N	Synchronizes accesses to the contents of library cache objects
101	IV	Synchronizes library cache object invalidations across instances
102	TP	Lock used for fixed runtime table purge and definition
103	RW	Lock used when creating or readingmaterialized view flags in detail tables
104	OC	Synchronizes write accesses to the outline cache
105	OL	Synchronizes accesses to a particular outline name
106	CU	Recovers cursors in case of death while compiling
107	AE	Prevent Dropping an edition in use
108	PF	Synchronizes accesses to the password file
109	IL	Synchronizes accesses to internal label data structures
110	CL	Synchronizes accesses to label cache and label tables
111	MK	Serializes enc$ access
112	OW	Serializes wallet initialization and access
113	RK	Serializes wallet master key rekey
114	RL	RAC encryption wallet lock
115	ZZ	Lock held for updating Global context hash tables
116	AU	Lock held to synchronize access XML to audit index file
117	ZA	Lock held for adding partition to Aud table
118	ZF	Lock held for adding partition to Fga table
119	DX	Serializes tightly coupled distributed transaction branches
120	DR	Serializes the active distributed recovery operation
121	BB	2PC distributed transaction branch across RAC instances
122	JD	Synchronizes dates between job queue coordinator and slave processes
123	JQ	Lock to prevent multiple instances from running a single job
124	OD	Lock to prevent concurrent online DDLs
125	DB	Synchronizes modification of database wide supplementallogging attributes
126	MD	Lock held during materialized view log DDL statements
127	MS	Lock held during materialized view refresh to setup MV log
128	PI	Communicates remote Parallel Execution Server Process creation status
129	PS	Parallel Execution Server Process reservation and synchronization
130	AY	Affinity Dictionary test affinity synchronization
131	TO	Synchronizes DDL and DML operations on a temp object
132	IT	Synchronizes accesses to a temp object's metadata
133	BF	Synchronize access to a bloom filter in a parallel statement
134	RC	Accessing a result in the result-set cache
135	JX	synchronize SQL statement queue operations
136	RD	synchronize update of RAC load info
137	KP	Synchronizes kupp process startup
138	SR	Coordinates replication / streams operations
139	SI	Prevents muiltiple streams table instantiations
140	ZG	Coordinates file group operations
141	IA
142	JI	Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
143	AT	Serializes 'alter tablespace' operations
144	UL	Lock used by user applications
145	CQ	Serializes access to cleanup client query cache registrations
146	SE	Lock used by transparent session migration
147	TQ	Synchronizes access to queue table
148	DP	Synchronizes access to LDAP parameters
149	MH	Lock used for recovery when setting Mail Host for AQ e-mail notifications
150	ML	Lock used for recovery when setting Mail Port for AQ e-mail notifications
151	PH	Lock used for recovery when setting Proxy for AQ HTTP notifications
152	SF	Lock used for recovery when setting Sender for AQ e-mail notifications
153	XH	Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
154	WA	Lock used for recovery when setting Watermark for memory usage in AQ notifications
155	XC	Synchronization access to XDB configuration
156	RF	Synchronizes broker lock operation involving lock value
157	AW	Synchronizes access to Analytic Workspace resources
158	AG	Synchronizes generation use of a particular workspace
159	AO	Synchronizes access to objects and scalar variables
160	OQ	Synchronizes access to olapi history resources
161	IZ	Proctects the lock name space used by the instance
162	AM	ASM instance general-purpose enqueue
163	FZ	Freezes ASM Cache for a diskgroup
164	CM	ASM instance and gate enqueue
165	XQ	ASM extent relocation
166	AD	Synchronizes accesses to a specific ASM disk AU
167	DO	Synchronizes disk onlines and their recovery
168	XL	Keep multiple processes from faulting in the same extent chunk
169	DG	Synchronizes accesses to ASM disk groups
170	DD	Synchronizes local accesses to ASM disk groups
171	HD	Serializes accesses to ASM SGA data structures
172	DQ	Inter-RBAL process metadata invalidation notification
173	DN	Serializes Group number generations
174	XB	Prevents client diskgroup use during storage reconfiguration
175	FA	Synchronizes accesses to open ASM files
176	RX	Synchronizes relocating ASM extents
177	AR	Protects locked extent pointers during ASM file relocation
178	FR	enqueue to control ASM recovery
179	FG	ACD relocation serialization
180	FT	controls the privilege to generate redo in a thread
181	FC	controls access to an ACD chunk
182	FX	serialize ACD relocation CIC
183	RB	Serializes ASM rollback recovery operations
184	PT	Gates inter-node synchronization of ASM PST metadata
185	PM	Signals inter-instance access to ASM PST metadata
186	KE	Synchronization of ASM cached attributes
187	KQ	Single Inst Sync of ASM attributes
188	AV	id1 is for persistent DG number locking. id2 is for other volume serialization
189	WF	This enqueue is used to serialize the flushing of snapshots
190	WP	This enqueue handles concurrency between purging and baselines
191	FU	This enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark Statistics
192	MW	This enqueue is used to serialize the calibration of the           manageability schedules with the Maintenance Window
193	TB	Synchronizes writes to the SQL Tuning Base Existence Cache
194	SH	To prevent multiple concurrent flushers
195	AF	This enqueue is used to serialize access to an advisor task
196	MO	Serialize MMON operations for restricted sessions
197	TL	Serializes threshold log table read and update
198	TH	Serializes threshold in-memory chain access
199	TK	Prevents more than one AutoTask Background Slave from being spawned
200	RR	Prevents concurrent invokers of DBMS_WORKLOAD_*
201	JS	Synchronizes accesses to the job cache
202	XD	Serialize Auto Management of Exadata disks

Sep 21st 2014

D: Drive became Inaccessible

Recently i installed oracle 12c on windows 7 . As a security measure Installation of oracle 12c on windows requires non-administrative user .

At the time of installation i created user db1 for same . I already had user which was one less than administrator privilege and equivalent to administrator user privilege and not equal .

This step resuled in one error

D:/ drive became in accessible to all non-administrative user

Resolution

I had to login as administrator and re-create the permission for all the users whose access got revoked 

DATE :-- 5th Sep 2014

Network Manager Issue in RHEL 7

Error: ---

1        1)      Error: Connection activation failed: Connection 'enp0s3' is not available on the device enp0s3              at this time.

2        2)      Wrong MAC Address

3        3)      Ifconfig –a was not showing the static ip address even after putting the static ip in
            application à Sundry à network connections

Issue:--

After cloning the Machine. I was not able to get the static and dhcp ip address displayed

Resolution:--

All command run using root

Step 1:-- nmcli general status   (NetworkManager Overall status)

Step 2:-- nmcli connection show –active (To Show active connection)

Step 3:-- nmcli connection show (To show all connection)

Step 4:-- nmcli device status (To show device status)

Step 5:-- ip addr (To get the correct MAC address of the device)

Step 6:-- nmtui (GUI Console will open change the MAC Address)

Step 7:-- Activate the network interface one by one 

DATE :-- 5th Sep 2014

RAC Two Node Cluster Not able to ping

Error: ---

Destination host unreachable

Issue:--

In VM in Network Settings

Adapter 1 : Inter PRO/1000 MT Desktop (Bridged Adapter,Inter(R) Dual Band Wireless AC 7260
Adapter 2 : Intel PRO/1000 MT Desktop (Bridged Adapter, Microsoft Virtual WiFi Miniport Adapter)
Adapter 3 : Intel PRO/1000 MT Desktop (Host-only Adapter, ‘VirtualBox Host-Only Ethernet Adapter’)

The setting for Adapter 1, Adapter 2 and Adapter 3 were different.

Resolution:--

Corrected the Device assigned to each Adapter 1, Adapter 2 and Adapter 3 on both the nodes. Resulted in ping between the server successful

DATE :-- 02nd Sep 2014

Configuring DNS for resolving SCAN IP in VM

Step 1 :-- yum install dnsmasq

Step 2 :-- yum list dnsmasq

Step 3 :-- rpm -qa dnsmasq

Step 4 :-- Add IP to resolv.conf

cat /etc/resolv.conf
# Generated by NetworkManager
nameserver 127.0.0.1

On 127.0.0.1 dnsmasq service is running 

Step 5 :-- chattr +i /etc/resolv.conf

Step 6 :-- vi /etc/dnsmasq.conf

listen-address=192.168.56.166
port=53
no-resolv
domain-needed
bogus-priv
dns-forward-max=150
cache-size=1000
neg-ttl=3600
no-poll
no-hosts
add-hosts=/etc/racdns

Step 7 :-- vi /etc/racdns

192.168.56.151  rac12c-scan.localdomain   rac12c-scan
192.168.56.152  rac12c-scan.localdomain   rac12c-scan
192.168.56.153  rac12c-scan.localdomain   rac12c-scan
192.168.56.166  RAC112C.localdomain  RAC112C
192.168.0.122   RAC112C-vip.localdomain   RAC112C-vip
192.168.56.168  RAC212C.localdomain           RAC212C
192.168.0.132   RAC212C-vip.localdomain    RAC212C-vip

Step 8 :--  Testing nslookup

[root@RAC112C ~]# nslookup rac12c-scan

;; connection timed out; trying next origin

Server:  192.168.56.166

Address: 192.168.56.166#53

Name: rac12c-scan.localdomain

Address: 192.168.56.152

Name: rac12c-scan.localdomain

Address: 192.168.56.153

Name: rac12c-scan.localdomain

Address: 192.168.56.151

Note :-- SCAN entries in /etc/hosts needs to be commented out 

Every time VM is started .. dnsmasq services needs to be started to nslookup to work

service dnsmasq start

DATE :-- 1st SEP 2014

                                          DATA GUARD BROKER :-- ORA - 16826

Solution :--

DGMGRL> show configuration

Configuration - dg1

  Protection Mode: MaxPerformance
  Databases:
    DBPR11G  - Primary database
    dbstp11g - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property
/* Note the warning */
Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL>

Resolution :--

Step 1 :-- Check RECOVERY_MODE by querying v$archive_dest_status on primary


SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE
-----------------------
IDLE
MANAGED

Step 2 :-- Cancel the recovery process on standby

SQL> alter database recover managed standby database cancel;

Step 3:-- Start the recovery process

SQL> alter database recover managed standby database using current logfile disconnect;

Step 4:-- Query the primary

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE
-----------------------
IDLE
MANAGED REAL TIME APPLY

IDLE

MANAGED has Changed to MANAGED REAL TIME APPLY

Step 5:-- Query 

DGMGRL> show database dbstp11g

Database - dbstp11g

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       25 minutes 3 seconds

  Real Time Query: OFF

  Instance(s):

    DBSTP11G

Database Status:

SUCCESS

DGMGRL>

DATE :-- 1st Sep 2014

RMAN-04006 :-- RMAN Duplicate Standby Creation Active Database

Error Detail

released channel: p3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/01/2014 02:24:14
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

RMAN> exit

Issue

Tnsnames.ora Entry 

###################### STANDBY ##############################

DBSTP11G =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.163)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DBSTP11G)

      (UR = A)

    )

  )

DBPR11G =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.161)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DBPR11G)

    )

  )

Listener.ora Entry

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.161)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

        (GLOBAL_DBNAME = DBPR11G_DGMGRL)

        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

        (SID_NAME = DBPR11G)

        (SERVICE_NAME = DBPR11G)

   )

   (SID_DESC =

     (GLOBAL_DBNAME=DBSTP11G_DGMGRL)

     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

     (SID_NAME=DBSTP11G)

     (SERVICE_NAME=DBSTP11G)

   )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Solution

_DGMGRL should not be part of GLOBAL_DBNAME .. GLOBAL_DBNAME should be same as tnsnames.ora file entry

After changing the above my issue resolved

DATE :-- 1st Sep 2014

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Error :--

SQL> conn sys@DBSTP11G as sysdba

Enter password:

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Warning: You are no longer connected to ORACLE.

SQL> exit

         

Resolution :--

DBSTP11G =

  (DESCRIPTION =

   (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.163)(PORT=1521))

   (CONNECT_DATA =

    (SERVER=DEDICATED)

    (SID_NAME=DBSTP11G)

    (SERVICE_NAME=DBSTP11G)

    (UR=A)

   )

  )

UR=A 

It will resolve the issue 

 network issue resolved