Labels

Saturday, August 8, 2015

User complain that query is running very slow in oracle 11g. How to trouble shoot?

Performance issue on 11g.
One day user suddenly started complaining that they are experiening slowness. So I have started troubleshooitng  like below.
Luckly I have OEM, So first I look into performance section to find out how it is currently. I found below to in the OEM.



It is found that select statement is the top SQL and the wait event is db file sequential read. So I had to find out what is the object on which this wait is happening. I found the object information by below queries.



In order to overcome this I gather statistics for that table and flush shared pool.


I Monitor the performance home page and found the performance has improved.


I also cross checked the execution time and elapsed time for the execution.

Question is why I took these steps?
Answer:- This had happen in UAT environment. So most probably states may be old. Sometimes, the stats may not be fresh and it leads to costly execution plans as in this case I had seen the execution and elapse time was high. In such case, immediately stats collection and a flush from shared pool will give new hash value for the sql_id with new plan


Interview on 07-Aug-2015

Interview question for 4-8 years experience held on 07-Aug-2015
1.Tell me about your self
2.Day to day activity in office as oracle dba
3. What is the flow of select statement?
4.What are the steps for oracle RAC installation?
5.What is the purpose of standby redolog files? why it is used?
6.Difference between 10g and 11g?
7.One table is accidentally dropped by user. You don't have flash back feature enabled. Database is production database and in terms of TB. How to restore using RMAN?
8.What is the steps of physical standby creation?
9. What are difference between exp and expdp?
10.What is voting disk and ocr?
11. Why odd numbers of voting disks are used?
12.How to trouble shoot when one node is down in RAC? What are the possible reasons?
13. In expdp what method used to take backup byte or block?
14. What are the advantages of datapump over tradition export ?
15.Suppose a user is connected to the database. and mean the time listner get down. What would happen to that user? When the user will give a statement will that process?
16.User complained one query is not performing well ? what action will be taken?
17.one index is already present in a column. Now we create primary key on that column. will new index will be created?
18. One big transaction was going on and client machine got rebooted. At that time what would happen to that transaction. If it roll back then which process will do this?
19. How to recover undo tablespace?
20 How to recover if redolog files got deleted?
21.What are the steps to create asm instance?
22. What is SCAN listner?
23.If one archive log file has not been applied and it got deleted. How to make standby database sync with primary database?
24.Explain different background process of RAC?
25. Explain background process for ASM?


Thursday, March 6, 2014

Can Common user and local user name be same in oracle 12c?

In a multitenant environment, a common user is a database user whose identity and password are known in the root and in every existing and future pluggable database (PDB).
Hence common user and local user name cannot be same.
Lets proof this satement
SQL> alter system set  "_common_user_prefix"='' scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

In pfile
ora12c._common_user_prefix=''
SQL> startup pfile='C:\app\XXXXXX\product\12.1.0\dbhome_1\database\INITora12c.ORA';
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2410864 bytes
Variable Size             666896016 bytes
Database Buffers          394264576 bytes
Redo Buffers                5365760 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user demo identified by demo;

User created.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBORA12C                      MOUNTED
SUBUPDB                        MOUNTED
TEST                           READ WRITE

SQL> alter session set container=TEST;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
TEST
SQL> create user demo identified by demo;
create user demo identified by demo
            *
ERROR at line 1:
ORA-01920: user name 'DEMO' conflicts with another user or role name
SQL> show con_name

CON_NAME
------------------------------
TEST
SQL> create user hello identified by hello;

User created.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user hello identified by hello;
create user hello identified by hello
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database TEST

ORA-01920: user name 'HELLO' conflicts with another user or role name

Supressing C## for common user in Oracle 12c

Normally common user are started with C## in 12c. But this can be eliminated by using hidden parameter below method. SQL> alter system set  "_common_user_prefix"='v##' scope=spfile; System altered. SQL> create user v## identified by test;create user v## identified by test            *ERROR at line 1:ORA-65096: invalid common user or role nameSolutions:-SQL> create pfile from spfile;
 File created. SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down. #####Opened the pfile and modify the value ora12c._common_user_prefix='V##' SQL> startup pfile='C:\app\XXXXXX\product\12.1.0\dbhome_1\database\INITora12c.ORA';ORACLE instance started. Total System Global Area 1068937216 bytesFixed Size                  2410864 bytesVariable Size             666896016 bytesDatabase Buffers          394264576 bytesRedo Buffers                5365760 bytesDatabase mounted.Database opened.SQL> show con_name CON_NAME------------------------------CDB$ROOTSQL> create user v## identified by test; User created.

Oracle 12c Multisection Incremental backup

Starting with Oracle Database 12Release 1 (12.1), RMAN supports multisection incremental backups. Wherever applicable, RMAN also uses unused block compression and block change tracking while creating multisection incremental backups. When backup sets are used, you can create multisection full or incremental backups.

To create level 0 multisection incremental backups, the COMPATIBLE parameter must be set to 11.0 or higher. However, to create multisection incremental backups of level 1 or higher, you must set the COMPATIBLE parameter to 12.0.0 or higher. RMAN always creates multisection incremental backups withFILESPERSET set to 1.

The SECTION SIZE clause specifies the size of each backup section. If you specify a section size that is larger than the size of the file, then RMAN does not use multisection backups for that file.

Multisection Incremental Backup of Database as Backup Sets
SQL> select name from v$database;

NAME
---------
ORA12C
SQL> show user
USER is "SYS"
SQL> create user c##x identified by c##x;

User created.
SQL> grant sysbackup to c##x;

Grant succeeded.

SQL> grant connect,resource to c##x;

Grant succeeded.
C:\Users\xxxxxx>rman target 'c##x@ORA12C as sysbackup'

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Mar 6 16:47:51 2014

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

target database Password:
connected to target database: ORA12C (DBID=218350063)
RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 2;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> backup incremental level 1 section size 300m datafile 'C:\APP\XXXXXX\ORADATA\ORA12C\SYSTEM01.DBF';

Starting backup at 06-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=34 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\XXXXXX\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 1 through 38400
channel ORA_DISK_1: starting piece 1 at 06-MAR-14
channel ORA_DISK_2: starting incremental level 1 datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\XXXXXX\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 38401 through 76800
channel ORA_DISK_2: starting piece 2 at 06-MAR-14
channel ORA_DISK_1: finished piece 1 at 06-MAR-14
piece handle=C:\APP\XXXXXX\RECOVERY_AREA\ORA12C\BACKUPSET\2014_03_06\O1_MF_NNND1_TAG20140306T165137_9KJPL2B2_.BKP tag=TAG20140306T165137 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:31
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\XXXXXX\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 76801 through 98560
channel ORA_DISK_1: starting piece 3 at 06-MAR-14
channel ORA_DISK_2: finished piece 2 at 06-MAR-14
piece handle=C:\APP\XXXXXX\RECOVERY_AREA\ORA12C\BACKUPSET\2014_03_06\O1_MF_NNND1_TAG20140306T165137_9KJPWFGG_.BKP tag=TAG20140306T165137 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: finished piece 3 at 06-MAR-14
piece handle=C:\APP\XXXXXX\RECOVERY_AREA\ORA12C\BACKUPSET\2014_03_06\O1_MF_NNND1_TAG20140306T165137_9KJPWKS0_.BKP tag=TAG20140306T165137 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 06-MAR-14

Starting Control File and SPFILE Autobackup at 06-MAR-14
piece handle=C:\APP\XXXXXX\RECOVERY_AREA\ORA12C\AUTOBACKUP\2014_03_06\O1_MF_S_841510637_9KJPWP6R_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAR-14



Making Multisection Backups Using Image Copies
While the image copy is being created, multiple channels are used to write files sections. However, the output of this operation is one copy for each data file. If the section size that you specify is larger than the size of the file, then RMAN does not use multisection backups for that file.

Ensure that the COMPATIBLE parameter for the target database is set to 12.0.0 or higher.
RMAN> BACKUP AS COPY SECTION SIZE 500M DATABASE;

Starting backup at 06-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=32 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\APP\XXXXXX\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 1 through 64000
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=C:\APP\XXXXXX\ORADATA\ORA12C\SYSAUX01.DBF
backing up blocks 1 through 64000
level 1 incremental backup of image copy.

RMAN> RUN {
   RECOVER COPY OF DATABASE WITH TAG 'incr_update';
   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' section size 500m
       DATABASE;
   }
Starting backup at 06-MAR-14
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\XXXXXX\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 1 through 64000
channel ORA_DISK_1: starting piece 1 at 06-MAR-14
channel ORA_DISK_2: starting incremental level 1 datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\APP\XXXXXX\ORADATA\ORA12C\SYSAUX01.DBF
backing up blocks 1 through 64000
channel ORA_DISK_2: starting piece 1 at 06-MAR-14
channel ORA_DISK_1: finished piece 1 at 06-MAR-14
piece handle=C:\APP\XXXXXX\RECOVERY_AREA\ORA12C\BACKUPSET\2014_03_06\O1_MF_NNND1_INCR_UPDATE_9KJSKMP
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set


Blogs

http://saivamsi.blogspot.com/

http://shaharear.blogspot.com/

http://harshavardhandba.blogspot.com/

http://dbataj.blogspot.com/

http://ayyudba.blogspot.com/

Oracle Patch information

12c


11g
Patch 13390677 – 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
Patch 10404530 – 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
Patch 10098816 – 11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER
Patch 6890831  – 11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER

10g

Patch 8202632 – 10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER
Patch 6810189 – 10.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
Patch 5337014 – 10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
Patch 4547817 – 10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
Patch 4505133 – 10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
Patch 4163362 – 10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3761843 – 10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER

9i

Patch 4547809 – 9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER
Patch 4163445 – 9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3948480 – 9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3501955 – 9.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3095277 – 9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
Patch 2761332 – 9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
Patch 2632931 – 9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3301544 – 9.0.1.5 PATCH SET FOR ORACLE DATABASE SERVER
Patch 2517300 – 9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER
Patch 2271678 – 9.0.1.3. PATCH SET FOR ORACLE DATA SERVER

8i


Patch 2376472 – 8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
Patch 2189751 – 8.1.7.3 PATCH SET FOR ORACLE DATA SERVER
Patch 1909158 – 8.1.7.2 PATCH SET FOR ORACLE DATA SERVER