25^th SEP 2014

STATSPACK ADMINISTRATION

         Detail about statspack can be read from

             $ORACLE_HOME/rdbms/admin/spdoc.txt

1. Taking performance snapshot

             SQLPLUS PERFSTAT/PERFSTAT
             EXEC STATSPACK.SNAP;
             EXEC PERFSTAT.STATSPACK.SNAP(I_SNAP_LEVEL=>10);

2. Get list of snapshots

            sql > select snap_id,snap_time from stats$snapshot;

    3. Generate statspack report

sql> @$ORACLE_HOME/rdbms/admin/spreport.sql
– Provide info as require

   4. Additional statspack scripts

a) sppurge.sql – Purge(delete) a range of snapshot id's between the given snap id's
b) spauto.sql – Schedule a dbms_job to automate the collection of statspack statistics
c) spdrop.sql – Deinstall statspack from database (Run as SYS)
d) sptrunc.sql – Truncate all data in statspack tables

5. Statspack Level
   
   SQL> SELECT * FROM stats$level_description ORDER BY snap_level;
   Level 0 captures general statistics, rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information.
   Level 5 captures high resource usage SQL Statements, along with all data captured by lower levels.
   Level 6 captures SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels.
   Level 7 captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels.
   Level 10 captures Child Latch statistics, along with all data captured by lower levels.

25^th SEP 2014

STATSPACK (Definition , Installation & Generation Report)

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters

source :-- http://docs.oracle.com/cd/B10501_01/server.920/a96533/statspac.htm

Installation of statspack

Step 1) login to the database as SYS user

sqlplus / as sysdba

Step 2) Check parameter job_queue_processes

sql> show parameter job_queue_processes

Its value should be > 0

if it is not set > 0 execute the below command

sql > alter system set job_queue_processes=5 scope=both;

Step 3) check if table perfstat exists on the database

SQL> select * from v$tablespace where NAME like '%PERF%';

If it donot exist create perfstat table

sql > create tablespace perfstat datafile '/PATH/perfstat.dbf' size 3G autoextend off;

/u01/app/oracle/oradata/DBPR11G

Step 4) Run the below sql query to deinstall any previous installation

SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql

Step 5) Run the below script to install statspack on the database . This script will create PERFSTAT user on the database

SQL > @$ORACLE_HOME/rdbms/admin/spcreate.sql

-- Enter perfstat as the default tablespace

-- Enter the name of the default tablespace

Step 6) Run the below pl/sql code to schedule the snapshot collection every 30 minutes .Run as sys user

variable jobno number;

variable instno number;

BEGIN

SELECT instance_number INTO :instno from v$instance;

DBMS_JOB.SUBMIT(:jobno,'statspack.snap;',trunc(sysdate,'HH24')

+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60),'trunc(sysdate,"HH24")

+((floor(to_number(to_char(sysdate,"MI"))/30)+1)*30)/(24*60)',TRUE,:instno);

COMMIT;

END;

/

Step 7) Generate statspace report

SQL > @$ORACLE_HOME/rdbms/admin/spreport

Follow the instruction 

If Below error comes

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 3494669306 for database Id
Using          1 for instance number
declare
*
ERROR at line 1:
ORA-20200: Database/Instance 3494669306/1 does not exist in
STATS$DATABASE_INSTANCE
ORA-06512: at line 23

Resolution :--

SQL> execute statspack.snap;

24^th SEP 2014

DataGuard Broker :-- Switchover and failover physical standby

Definition DG :-- The data guard broker is a framework for monitoring Data Guard configurations.

DG (Data Guard) Configuration

On both Primary and standby sites , change the initialization parameter in the spfile to enable the data guard broker

sql > alter system set dg_broker_start=TRUE scope=both

Execute

dgmgrl

DGMGRL>

Now connect to the primary database

DGMGRL> connect sys/<password>@to_primary

connected

Create broker configuration

DGMGRL> create configuration 'broker1' as primary database is 'primary' connect identifier is 'primary';

'primary' is connect identified is the service name through which the broker is connected to the PRIMARY database)

Add standby database to the above configuration

DGMGRL>Add database 'standby' as connect identifier is to_standby maintained as physical;

("to_standby" in connect identifier is the serivce name through which broker is connected to the STANDBY database)

See the configuration

DGMGRL> show configuration

It will show current status as DISABLE

We need to enable the configuration

DGMGRL> enable configuration;

DGMGRL> show configuration

Current status will be shown as SUCCESS

SWITCHOVER :

Now we are ready to switch over the primary database role to standby database role

DGMGRL > switchover to 'Standby';

Primary database successfully converted to physical standby

DGMGRL> switchover to 'primary';

standby database is successfully converted to primary database

Failover :

DGMGRL>failover to 'standby';

DGMGRL> failover to 'Primary';

24^th SEP 2014

LOG SHIPPING CHECK & MANUAL SWITCHOVER PHYSICAL STANDBY

On Primary

Execute below command to enable log shipping to standby

sql > alter system set log_archive_dest_state_2=ENABLE scope=both

Check the status of Standby archiving destination

sql > select status,error from v$archive_dest where dest_id=2;

Note :-- The status should return -- valid . If it return error , then check connectivity between primary and standby

Physical Standby

On the standby database execuet the following command to start Managed Recovery Process (MRP) . The command is executed on Mount stage

SQL > alter database recover managed standby database disconnect from session;

Check log applied process

SQL > select name,applied,archived from v$archived_log;

SQL > select max(sequence#) from v$archived_log where applied='YES';

Role Transition

Oracle Data Guard supports two role transition operations:

switchover:

switchover allows the primary database to switch roles with one of its standby databases.There is no data loss during a switchover.After a switchover,each database continues to participate in the data guard configuration with its new role

Failover:

Failover transitions a standby database to the primary role in response to a primary database failure. If the primary database was not opening in either maximum protection mode or maximum availability mode before the failure,some data loss may occur. After a failover, the failed database no longer participates in the Data Guard configuration.It needs to be reinstated to become an active part of Data Guard configuration

Manual Switchover

On the primary Database (open stage)

Query v$database to check the role of Primary Database

sql > select database_role from v$database;

It will return primary

Now check the switchover status of the primary database

sql > select switchover_status from v$database

It will return "SESSIONS ACTIVE"

Now at this point database is ready for manual switchover

execute the below command

sql > alter database commit to switchover to physical standby with session shutdown;

Now your primary database has become physical standby.

Query the database_role column of v$database . It will return "PHYSICAL STANDBY"

Shutdown this new physical standby database and start in mount stage

On the PHYSICAL STANDBY Database (Mount Stage)

Query to check the database role

sql > select database_role from v$database;

It will return "PHYSICAL STANDBY"

Now check the switchover status of the standby database

sql > select switchover_status from v$database;

It will return "SESSIONS ACTIVE"

Cancel the MRP Process which is running in the background

sql > alter database recover managed standby database cancel;

Now you are ready to perform a manual switchover from physical standby to primary

Execute the below command using "SWITCH SESSION SHUTDOWN"

sql > alter database commit to switchover to primary with session shutdown;

Now your physical standby database has become primary .

Query database_role column of v$database . It will return "PRIMARY"

shutdown the database and again start in open stage.

Sep 24

Oracle Wait Class Definition

Note :-- I will keep updating this post as i progress

Oracle Wait Class Definition 

1) PX Deq Credit: send blkd 

English Defiintion :--  PX  -- Parallel Execution
                                    Deq -- To remove the first available element from queue
                                    Credit  -- Acknowledgment of Some Work
                                    Send  :-- to permit
                                    blkd    :-- blocked

Technical Definition :-- Parallel Execution process wants to send message to slave and its not allowed

Note :-- Any parallel execution process has two sub process . One process say p0 queries the database and send the output to p1 another parallel sub process to send back the output of the query back to client or program

alter table emp parallel (degree 2); // this will enable two parallel process. The parallel process of any table in can be viewed in degree column of user_tables

PX Deq Credit: send blkd and PX Deq Credit: need buffer are same . The first one is seem more on localsystems and second one is seen on RAC systems 

There is no general recommendation to reduce this wait times

2) direct path read

English Definition :--  Direct :-- send toward a place or object
                               Path   :--- a course of action
                               Read :-- To anticipate

Technical Definition :-- The direct path read occurs when oracle access datafile into the PGA instead of SGA buffer memory .

This happens under these conditions 

1) Reading a Lob Segment

2) Doing a full-table scan

3) Reading a temp file 

This happen in case when table in query has more degree of parallelism . Because oracle thinks that doing a full table scan is more beneficial than doing index scan 

Sep 22

Issue :-- To import tables from 11g to 10g

Resolution:--

1st Approach

Step 1) login to 10g database
Step 2) tnsping the 11g database
Step 3) Connect to 11g database from 10g database using sqlplus system/<password>@11gdatabase
Step 4) Execute exp command  from 10g database
            exp system/<password> file=tab.dmp tables=schema.T1,schema.T2
Step 5) Import the file tab.dmp on 10g database
Step 6) imp system/<password> file=tab.dmp fromuser=<11g_schema_name>  touser=<10g_schema_name>

2nd Approach

Step 1) login to 11g database .
Step 2) Take expdp of the schema
expdp system/**** schemas=<11g_schema_name> directory=< > dumpfile=< > VERSION=10.2
Step 3) Scp the dmp file to 10g database
Step 4) On 10g database
impdp \'/ as sysdba\' directory=< > dumpfile=< > remap_schema=<11g_Schema>:<10g_schema> remap_tablespace=<11g_tablespace>:<10g_tablespace>,<11g_t2>:<10g_t2>





            

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