CREATE DATABASE SCRIPT DESCRIPTION SCRIPT

CREATE DATABASE SCRIPT DESCRIPTION
SCRIPT:-


CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP1 ('/u01/app/oracle/oradata/mynewdb/redo01.log')
SIZE 100M,
 GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log')
SIZE 100M,
 GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log')
SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE
325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf'
SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;


1:-CREATE DATABASE mynewdb:-The database is named mynewdb. Its global
database name is mynewdb where the domain portion is taken from
the initialization file.
2:- Three control files are created as specified by the CONTROL_FILES initialization parameter,
which was set before database creation in the initialization parameter file.
3:- USER SYS IDENTIFIED BY sys_password/USER SYSTEM IDENTIFIED BY
system_password:--The passwords for user accounts SYS and SYSTEM are
set to the values that you specified. Beginning with Release 11g, the
passwords are case-sensitive. The two clauses that specify the
passwords for SYS and SYSTEM are not mandatory in this release of
Oracle Database. However, if you specify either clause, you must
specify both clauses.
4:- The new database has three redo log files as specified in the
LOGFILE clause. MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY
define limits for the redo log.
5:- MAXDATAFILES specifies the maximum number of datafiles that can
be open in the database. This number affects the initial sizing of
the control file.

NOTE:-
You can set several limits during database creation. Some of these limits are limited by and
affected by operating system limits. For example, if you set MAXDATAFILES, Oracle Database
allocates enough space in the control file to store MAXDATAFILES filenames, even if the database
has only one datafile initially. However, because the maximum control file size is limited and
operating system dependent, you might not be able to set all CREATE DATABASE parameters at
their theoretical maximums.
MAXDATAFILE:-65533 (per database) / 1022(per tablespace)
 -The US7ASCII character set is used to store data in this database.
 -The AL16UTF16 character set is specified as the NATIONAL CHARACTER SET, used to
 store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2.
• The SYSTEM tablespace, consisting of the operating system file
/u01/app/oracle/oradata/mynewdb/system01.dbf is created as specified by the
DATAFILE clause. If a file with that name already exists, it is overwritten.
• The SYSTEM tablespace is created as a locally managed tablespace.
• A SYSAUX tablespace is created, consisting of the operating system file
/u01/app/oracle/oradata/mynewdb/sysaux01.dbf as specified in the SYSAUX
DATAFILE clause.
• The DEFAULT TABLESPACE clause creates and names a default permanent tablespace for this
database.
• The DEFAULT TEMPORARY TABLESPACE clause creates and names a default temporary
tablespace for this database.
• The UNDO TABLESPACE clause creates and names an undo tablespace that is used to store
undo data for this database if you have specified UNDO_MANAGEMENT=AUTO in the
initialization parameter file. If you omit this parameter, it defaults to AUTO.
• Redo log files will not initially be archived, because the ARCHIVELOG clause is not specified
in this CREATE DATABASE statement. This is customary during database creation. You can
later use an ALTER DATABASE statement to switch to ARCHIVELOG mode. The initialization
parameters in the initialization parameter file for mynewdb relating to archiving are
LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT.
FILES MINIMUM / DEFAULT MAXIMUM / DEFAULT
TABLESPACE 1 2 64
MAXDATAFILES 2 2 1024
Calc:-tablespace x Maxdafiles Total datafiles:-65536
REDOLOG GROUPS 2 3 255
REDOLOG MEMBERS 1 2 5
Size=4 Mb for each member Size= 2gb for each member

AUTO START DATABASE ON BOOTING

AUTO START DATABASE ON BOOTING
 STEP:-1 First make the entry in oratab as “Y” ,to which we want to start our
 database autostart .
 -MAKE ENTRY IN: vi /etc/oratab

 - EXAMPLE: orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y
 (sid): (path) :(Y/N)
STEP:-2 Create the startup and shutdown service in cd /etc/init.d
 we have to wirte the script of startup and shutdown in init.d with dbora
 file name.
– Vi /etc/init.d/dbora
– script as follow
 #! /bin/sh
#
#chkconfig: 2345 20 80
#description: start and stop Oracle Enterprise Edition on Oracle
Linux 5 and 6
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dhome_1
ORACLE_OWNER=oracle
case "$1" in
'start')
su - oracle -c /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart
 ;;
'stop')
 su - oracle -c /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbshut
 ;;
esac
 -Explanation of Script
 #! /bin/sh
 #chkconfig: 2345 20 80
 #description: start and stop Oracle Enterprise Edition on Oracle Linux 5 and 6
 This are the mandatory to mention this three line because Skeleton reads this
line in the script ,no matter it is in # tag but it will read it.
 #chkconfig: 2345 20 80

 -2345:- this are the runlevel ,wg=here you want to start the service of dbora

 -20 :- is a startup priority

 -80:- is a shutdown priority
startup script:-

'start')
su - oracle -c /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart
 ;;
-first it will switch the user because we are creating the scrip with root user
 and other user cannot set the script.

 -Then it will travel to the whole part and search for dbstart which generated
 during S/W creation ,they are binaries.
Shutdown Script:-
 'stop')

 su - oracle -c /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbshut
 ;;
 -first it will switch the user because we are creating the scrip with root user
 and other user cannot set the script.

 -Then it will travel to the whole part and search for dbshut which generated
 during S/W creation ,they are binaries.

STEP:-3 After creating the script change the rights and group of dbora service

 -chmod 750 dbora
-chgrp dba dbora
STEP:-4 create the softlink of dbora to the the specific runlevels
 # ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora
 -K01dbora:-it is kill process which we give in rc0.d
 -S99dbora :-it is start process which we create in rc3.d and
rc5.d
STEP:-5 ADD the dbora service
 -chkconfig –-add dbora
STEP:-6 TO check the service is got added or not then use
 -chkconfig --list | grep dbora
 O/P:- dbora 0:off 1:off 2:on 3:on 4:on 5:on 6:off
STEP:-7 Restart the system and check the status whether the database
 is started or not
 AT os level check using command
 ps -ef | grep pmon
 o/p:- orcl_pmon
 HERE YOUR DATABASE IS STARTED IN AUTO MODE 

Install ORACLE on SOLARIS - STEP by STEP

All session events in ORACLE DATABASE

SQL> select distinct(event)  from v$session_event;

EVENT
----------------------------------------------------------------
os thread startup
db file parallel write
control file parallel write
SQL*Net message from client
library cache pin
cursor: pin S wait on X
log file sync
direct path write temp
resmgr:cpu quantum
wait for unread message on broadcast channel
Streams AQ: waiting for messages in the queue

EVENT
----------------------------------------------------------------
control file sequential read
read by other session
SQL*Net break/reset to client
pmon timer
rdbms ipc message
db file sequential read
log file sequential read
jobq slave wait
smon timer
JS coord start wait
latch: row cache objects

EVENT
----------------------------------------------------------------
SQL*Net more data to client
buffer busy waits
direct path read temp
Log archive I/O
events in waitclass Other
SQL*Net more data from client
direct path write
db file parallel read
class slave wait
db file scattered read
Streams AQ: qmn slave idle wait

EVENT
----------------------------------------------------------------
Parameter File I/O
Disk file operations I/O
SQL*Net message to client
log file parallel write
Space Manager: slave idle wait
direct path read
Streams AQ: qmn coordinator idle wait
log file single write
Streams AQ: waiting for time management or cleanup tasks
DIAG idle wait
SGA: MMAN sleep for component shrink

44 rows selected.

What happen when db_recovery_file_dest_size of 1048576 bytes is 100.00% used.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
db_recovery_file_dest                string      G:\app\Vishwakarma\flash_re
                                                 ery_area
db_recovery_file_dest_size           big integer 3912M
recovery_parallelism                 integer     0

SQL>
SQL> alter system set db_recovery_file_dest_size=1M;

System altered.

SQL>
SQL>
SQL>
SQL>


SQL> alter system switch logfile;

System altered.

SQL> create table test1(id int);

Table created.

SQL> insert into test1 values(12434);

1 row created.

SQL> insert into test1 values(12434);

1 row created.




SQL> insert into test1 select * from test1;

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> /

32768 rows created.

SQL> /

65536 rows created.

SQL> /

131072 rows created.

SQL> /

262144 rows created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;





C:\Users\Vishwakarma>
C:\Users\Vishwakarma>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat May 20 12:07:30 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing option

SQL> @dbinfo

NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE
--------- ------------ -------------------- ----------------
TEST1     ARCHIVELOG   READ WRITE           PRIMARY

SQL> select count(*) from test1;

  COUNT(*)
----------
    524288


So, after inserting 524288 row  db_recovery_file_dest_size of 1048576 bytes is 100.00% used and it database gets stale  for any further insert or update (DML).




AlertlLog..............................................................


Errors in file g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc0_2088.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1918464 bytes disk space from 1048576 limit
ARC0: Error 19809 Creating archive log file to 'G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_20\O1_MF_1_4_%U_.ARC'
Thread 1 cannot allocate new log, sequence 7
Checkpoint not complete
  Current log# 3 seq# 6 mem# 0: G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO03.LOG
Thread 1 cannot allocate new log, sequence 7
All online logs needed archiving
  Current log# 3 seq# 6 mem# 0: G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO03.LOG
Sat May 20 12:06:28 2017
Errors in file g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc1_3556.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1048576 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Errors in file g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc1_3556.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1918464 bytes disk space from 1048576 limit
ARC1: Error 19809 Creating archive log file to 'G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_20\O1_MF_1_4_%U_.ARC'

Duplicate database using OEM (Background commands)

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 19 22:15:16 2017
 
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 SQL> SQL> Connected.
 SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27  Parameter 1:
 param# processes=150
 Parameter 2:
 param# memory_target=1728053248
 Parameter 3:
 param# control_files=G:\APP\VISHWAKARMA\ORADATA\TEST\CONTROL01.CTL, G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL
 Parameter 4:
 param# db_block_size=8192
 Parameter 5:
 param# compatible=11.2.0.0.0
 Parameter 6:
 param# db_recovery_file_dest=G:\app\Vishwakarma\flash_recovery_area
 Parameter 7:
 param# db_recovery_file_dest_size=4102029312
 Parameter 8:
 param# undo_tablespace=UNDOTBS1
 Parameter 9:
 param# remote_login_passwordfile=EXCLUSIVE
 Parameter 10:
 param# db_domain=""
 Parameter 11:
 param# dispatchers=(PROTOCOL=TCP) (SERVICE=testXDB)
 Parameter 12:
 param# audit_file_dest=G:\APP\VISHWAKARMA\ADMIN\TEST\ADUMP
 Parameter 13:
 param# audit_trail=NONE
 Parameter 14:
 param# db_name=test
 Parameter 15:
 param# open_cursors=300
 Parameter 16:
 param# diagnostic_dest=G:\APP\VISHWAKARMA
 
 PL/SQL procedure successfully completed.
 
 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
Step: Destination Preparation

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 19-MAY-2017 22:16:05
 
 Copyright (c) 1991, 2010, Oracle.  All rights reserved.
 
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
 Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
 Start Date                19-MAY-2017 21:33:20
 Uptime                    0 days 0 hr. 42 min. 46 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Listener Parameter File   G:\app\Vishwakarma\product\11.2.0\dbhome_1\network\admin\listener.ora
 Listener Log File         g:\app\vishwakarma\diag\tnslsnr\Vishwakarma-PC\listener\alert\log.xml
 Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Vishwakarma-PC)(PORT=1521)))
 Services Summary...
 Service "CLRExtProc" has 1 instance(s).
   Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
 Service "test" has 1 instance(s).
   Instance "test", status READY, has 1 handler(s) for this service...
 Service "testXDB" has 1 instance(s).
   Instance "test", status READY, has 1 handler(s) for this service...
 The command completed successfully

 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 19-MAY-2017 22:16:05
 
 Copyright (c) 1991, 2010, Oracle.  All rights reserved.
 
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 The command completed successfully

 SQL*Plus: Release 11.2.0.1.0 Production on Fri May 19 22:16:19 2017
 
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 SQL> Connected.
 SQL> ORACLE instance shut down.
 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL*Plus: Release 11.2.0.1.0 Production on Fri May 19 22:16:20 2017
 
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 SQL> Connected to an idle instance.
 SQL> ORACLE instance shut down.
 SQL> 
 File created.
 
 SQL> ORACLE instance started.
 
 Total System Global Area 1720328192 bytes
 Fixed Size      2176448 bytes
 Variable Size   1006635584 bytes
 Database Buffers   704643072 bytes
 Redo Buffers      6873088 bytes
 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options




Step: Duplicate Database
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 19 22:16:36 2017
 
 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
 RMAN> 
 echo set off
 
 RMAN> 
 connected to target database: TEST (DBID=2242576134)
 
 RMAN> 
 connected to auxiliary database: TEST1 (not mounted)
 
 RMAN> 
 echo set on
 
 RMAN> run {
 2>   SET NEWNAME FOR DATAFILE 1 TO 'G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\SYSTEM01.dbf';
 3>   SET NEWNAME FOR DATAFILE 2 TO 'G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\SYSAUX01.dbf';
 4>   SET NEWNAME FOR DATAFILE 3 TO 'G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\UNDOTBS01.dbf';
 5>   SET NEWNAME FOR DATAFILE 4 TO 'G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\USERS01.dbf';
 6>   SET NEWNAME FOR TEMPFILE 1 TO 'G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\TEMP01.dbf';
 7> allocate channel tgt1 type disk;
 8> allocate channel tgt2 type disk;
 9> allocate auxiliary channel dup1 type disk;
 10>  DUPLICATE TARGET DATABASE TO 'test1'
 11>  FROM ACTIVE DATABASE
 12>   LOGFILE
 13>   GROUP 1 ('G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\REDO01.log') size 20M reuse,
 14>   GROUP 2 ('G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\REDO02.log') size 20M reuse,
 15>   GROUP 3 ('G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\REDO03.log') size 20M reuse
 16> ;
 17> }
 executing command: SET NEWNAME
 
 executing command: SET NEWNAME
 
 executing command: SET NEWNAME
 
 executing command: SET NEWNAME
 
 executing command: SET NEWNAME
 
 using target database control file instead of recovery catalog
 allocated channel: tgt1
 channel tgt1: SID=151 device type=DISK
 
 allocated channel: tgt2
 channel tgt2: SID=28 device type=DISK
 
 allocated channel: dup1
 channel dup1: SID=134 device type=DISK
 
 Starting Duplicate Db at 19-MAY-17
 
 contents of Memory Script:
 {
    sql clone "alter system set  db_name = 
  ''TEST'' comment=
  ''Modified by RMAN duplicate'' scope=spfile";
    sql clone "alter system set  db_unique_name = 
  ''TEST1'' comment=
  ''Modified by RMAN duplicate'' scope=spfile";
    shutdown clone immediate;
    startup clone force nomount
    backup as copy current controlfile auxiliary format  'G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\CONTROL01.CTL';
    restore clone controlfile to  'G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\CONTROL02.CTL' from 
  'G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\CONTROL01.CTL';
    alter clone database mount;
 }
 executing Memory Script
 
 sql statement: alter system set  db_name =  ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
 sql statement: alter system set  db_unique_name =  ''TEST1'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
 Oracle instance shut down
 
 Oracle instance started
 
 Total System Global Area    1720328192 bytes
 
 Fixed Size                     2176448 bytes
 Variable Size               1006635584 bytes
 Database Buffers             704643072 bytes
 Redo Buffers                   6873088 bytes
 allocated channel: dup1
 channel dup1: SID=134 device type=DISK
 
 Starting backup at 19-MAY-17
 channel tgt1: starting datafile copy
 copying current control file
 output file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFTEST.ORA tag=TAG20170519T221650 RECID=1 STAMP=944432211
 channel tgt1: datafile copy complete, elapsed time: 00:00:03
 Finished backup at 19-MAY-17
 
 Starting restore at 19-MAY-17
 
 channel dup1: copied control file copy
 Finished restore at 19-MAY-17
 
 database mounted
 
 contents of Memory Script:
 {
    set newname for datafile  1 to 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\SYSTEM01.dbf";
    set newname for datafile  2 to 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\SYSAUX01.dbf";
    set newname for datafile  3 to 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\UNDOTBS01.dbf";
    set newname for datafile  4 to 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\USERS01.dbf";
    backup as copy reuse
    datafile  1 auxiliary format 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\SYSTEM01.dbf"   datafile 
  2 auxiliary format 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\SYSAUX01.dbf"   datafile 
  3 auxiliary format 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\UNDOTBS01.dbf"   datafile 
  4 auxiliary format 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\USERS01.dbf"   ;
    sql 'alter system archive log current';
 }
 executing Memory Script
 
 executing command: SET NEWNAME
 
 executing command: SET NEWNAME
 
 executing command: SET NEWNAME
 
 executing command: SET NEWNAME
 
 Starting backup at 19-MAY-17
 channel tgt1: starting datafile copy
 input datafile file number=00001 name=G:\APP\VISHWAKARMA\ORADATA\TEST\SYSTEM01.DBF
 channel tgt2: starting datafile copy
 input datafile file number=00002 name=G:\APP\VISHWAKARMA\ORADATA\TEST\SYSAUX01.DBF
 output file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSAUX01.DBF tag=TAG20170519T221659
 channel tgt2: datafile copy complete, elapsed time: 00:00:45
 channel tgt2: starting datafile copy
 input datafile file number=00003 name=G:\APP\VISHWAKARMA\ORADATA\TEST\UNDOTBS01.DBF
 output file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSTEM01.DBF tag=TAG20170519T221659
 channel tgt1: datafile copy complete, elapsed time: 00:00:45
 channel tgt1: starting datafile copy
 input datafile file number=00004 name=G:\APP\VISHWAKARMA\ORADATA\TEST\USERS01.DBF
 output file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\USERS01.DBF tag=TAG20170519T221659
 channel tgt1: datafile copy complete, elapsed time: 00:00:03
 output file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\UNDOTBS01.DBF tag=TAG20170519T221659
 channel tgt2: datafile copy complete, elapsed time: 00:00:04
 Finished backup at 19-MAY-17
 
 sql statement: alter system archive log current
 
 contents of Memory Script:
 {
    backup as copy reuse
    archivelog like  "G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_05_19\O1_MF_1_4_DKY8FORR_.ARC" auxiliary format 
  "G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_19\O1_MF_1_4_%U_.ARC"   ;
    catalog clone recovery area;
    switch clone datafile all;
 }
 executing Memory Script
 
 Starting backup at 19-MAY-17
 channel tgt1: starting archived log copy
 input archived log thread=1 sequence=4 RECID=2 STAMP=944432270
 output file name=G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_19\O1_MF_1_4_ARCH_D-TEST_ID-2242576134_S-4_T-1_A-944429704_06S4LP4E_.ARC RECID=0 STAMP=0
 channel tgt1: archived log copy complete, elapsed time: 00:00:01
 Finished backup at 19-MAY-17
 
 searching for all files in the recovery area
 
 List of Files Unknown to the Database
 =====================================
 File Name: G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_19\O1_MF_1_4_ARCH_D-TEST_ID-2242576134_S-4_T-1_A-944429704_06S4LP4E_.ARC
 cataloging files...
 cataloging done
 
 List of Cataloged Files
 =======================
 File Name: G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_19\O1_MF_1_4_ARCH_D-TEST_ID-2242576134_S-4_T-1_A-944429704_06S4LP4E_.ARC
 
 datafile 1 switched to datafile copy
 input datafile copy RECID=1 STAMP=944432272 file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSTEM01.DBF
 datafile 2 switched to datafile copy
 input datafile copy RECID=2 STAMP=944432272 file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSAUX01.DBF
 datafile 3 switched to datafile copy
 input datafile copy RECID=3 STAMP=944432272 file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\UNDOTBS01.DBF
 datafile 4 switched to datafile copy
 input datafile copy RECID=4 STAMP=944432272 file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\USERS01.DBF
 
 contents of Memory Script:
 {
    set until scn  979934;
    recover
    clone database
     delete archivelog
    ;
 }
 executing Memory Script
 
 executing command: SET until clause
 
 Starting recover at 19-MAY-17
 
 starting media recovery
 
 archived log for thread 1 with sequence 4 is already on disk as file G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_19\O1_MF_1_4_ARCH_D-TEST_ID-2242576134_S-4_T-1_A-944429704_06S4LP4E_.ARC
 archived log file name=G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_19\O1_MF_1_4_ARCH_D-TEST_ID-2242576134_S-4_T-1_A-944429704_06S4LP4E_.ARC thread=1 sequence=4
 media recovery complete, elapsed time: 00:00:01
 Finished recover at 19-MAY-17
 
 contents of Memory Script:
 {
    shutdown clone immediate;
    startup clone nomount;
    sql clone "alter system set  db_name = 
  ''TEST1'' comment=
  ''Reset to original value by RMAN'' scope=spfile";
    sql clone "alter system reset  db_unique_name scope=spfile";
    shutdown clone immediate;
    startup clone nomount;
 }
 executing Memory Script
 
 database dismounted
 Oracle instance shut down
 
 connected to auxiliary database (not started)
 Oracle instance started
 
 Total System Global Area    1720328192 bytes
 
 Fixed Size                     2176448 bytes
 Variable Size               1006635584 bytes
 Database Buffers             704643072 bytes
 Redo Buffers                   6873088 bytes
 allocated channel: dup1
 channel dup1: SID=133 device type=DISK
 
 sql statement: alter system set  db_name =  ''TEST1'' comment= ''Reset to original value by RMAN'' scope=spfile
 
 sql statement: alter system reset  db_unique_name scope=spfile
 
 Oracle instance shut down
 
 connected to auxiliary database (not started)
 Oracle instance started
 
 Total System Global Area    1720328192 bytes
 
 Fixed Size                     2176448 bytes
 Variable Size               1006635584 bytes
 Database Buffers             704643072 bytes
 Redo Buffers                   6873088 bytes
 allocated channel: dup1
 channel dup1: SID=133 device type=DISK
 sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST1" RESETLOGS ARCHIVELOG 
   MAXLOGFILES     16
   MAXLOGMEMBERS      3
   MAXDATAFILES      100
   MAXINSTANCES     8
   MAXLOGHISTORY      292
  LOGFILE
   GROUP  1 ( 'G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\REDO01.log' ) SIZE 20 M  REUSE,
   GROUP  2 ( 'G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\REDO02.log' ) SIZE 20 M  REUSE,
   GROUP  3 ( 'G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\REDO03.log' ) SIZE 20 M  REUSE
  DATAFILE
   'G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSTEM01.DBF'
  CHARACTER SET WE8MSWIN1252
 
 
 contents of Memory Script:
 {
    set newname for tempfile  1 to 
  "G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\TEMP01.dbf";
    switch clone tempfile all;
    catalog clone datafilecopy  "G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSAUX01.DBF", 
  "G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\UNDOTBS01.DBF", 
  "G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\USERS01.DBF";
    switch clone datafile all;
 }
 executing Memory Script
 
 executing command: SET NEWNAME
 
 renamed tempfile 1 to G:\app\Vishwakarma\product\11.2.0\dbhome_1\oradata\test1\TEMP01.dbf in control file
 
 cataloged datafile copy
 datafile copy file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSAUX01.DBF RECID=1 STAMP=944432290
 cataloged datafile copy
 datafile copy file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\UNDOTBS01.DBF RECID=2 STAMP=944432290
 cataloged datafile copy
 datafile copy file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\USERS01.DBF RECID=3 STAMP=944432290
 
 datafile 2 switched to datafile copy
 input datafile copy RECID=1 STAMP=944432290 file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSAUX01.DBF
 datafile 3 switched to datafile copy
 input datafile copy RECID=2 STAMP=944432290 file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\UNDOTBS01.DBF
 datafile 4 switched to datafile copy
 input datafile copy RECID=3 STAMP=944432290 file name=G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\USERS01.DBF
 
 contents of Memory Script:
 {
    Alter clone database open resetlogs;
 }
 executing Memory Script
 
 database opened
 Finished Duplicate Db at 19-MAY-17
 released channel: tgt1
 released channel: tgt2
 released channel: dup1
  
 
 RMAN> EXIT;
 
 Recovery Manager complete.
Step: Recover Database
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 19 22:18:56 2017
 
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 SQL> Connected.
 SQL> SQL> ORACLE instance started.
 
 Total System Global Area 1720328192 bytes
 Fixed Size      2176448 bytes
 Variable Size   1006635584 bytes
 Database Buffers   704643072 bytes
 Redo Buffers      6873088 bytes
 Database mounted.
 SQL> 
 Database altered.
 
 SQL> 
 STATUS
 ------------
 OPEN
 
 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
Step: Add Temporary Files
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 19 22:19:26 2017
 
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 SQL> Connected.
 SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9  Row count: 1
 Reset ORACLE_OCM_CONFIG_DIR
 
 PL/SQL procedure successfully completed.
 
 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
Step: Check Database and Run Post Cloning Scripts
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 19 22:19:36 2017
 
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 SQL> Connected.
 SQL> 
 datafile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSTEM01.DBF
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSAUX01.DBF
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\UNDOTBS01.DBF
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\USERS01.DBF
 
 SQL> 
 tempfile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\TEMP01.DBF
 
 SQL> 
 logfile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO03.LOG
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO02.LOG
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO01.LOG
 
 SQL> 
 controlfile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\CONTROL01.CTL
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\CONTROL02.CTL
 
 SQL> 
 directory name
 ------------------------------
 directory path
 --------------------------------------------------------------------------------
 XMLDIR
 c:\ade\aime_dadvfh0169\oracle/rdbms/xml
 
 DATA_PUMP_DIR
 G:\app\Vishwakarma/admin/test/dpdump/
 
 ORACLE_OCM_CONFIG_DIR
 G:\app\Vishwakarma\product\11.2.0\dbhome_1/ccr/state
 
 
 SQL> 
 no rows selected
 
 SQL> 
 spfile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILETEST1.ORA
 
 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

Step: Mask data
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 19 22:19:46 2017
 
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 SQL> Connected.
 SQL> 
 datafile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSTEM01.DBF
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\SYSAUX01.DBF
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\UNDOTBS01.DBF
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\USERS01.DBF
 
 SQL> 
 tempfile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\TEMP01.DBF
 
 SQL> 
 logfile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO03.LOG
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO02.LOG
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO01.LOG
 
 SQL> 
 controlfile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\CONTROL01.CTL
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\CONTROL02.CTL
 
 SQL> 
 directory name
 ------------------------------
 directory path
 --------------------------------------------------------------------------------
 XMLDIR
 c:\ade\aime_dadvfh0169\oracle/rdbms/xml
 
 DATA_PUMP_DIR
 G:\app\Vishwakarma/admin/test/dpdump/
 
 ORACLE_OCM_CONFIG_DIR
 G:\app\Vishwakarma\product\11.2.0\dbhome_1/ccr/state
 
 
 SQL> 
 no rows selected
 
 SQL> 
 spfile
 --------------------------------------------------------------------------------
 G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILETEST1.ORA
 
 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

How to calculate overall time of a SQL statement.

How to calculate overall time of a SQL statement.

SQL> set timing on
SQL> set time on
08:48:52 SQL> select count(*) from pk;

  COUNT(*)
----------
  25165824

Elapsed: 00:00:03.62
08:49:09 SQL>

Sar linux all command and output

[root@localhost /]# sar

Linux 2.6.18-308.el5 (localhost.localdomain)    03/14/2017

12:00:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle

12:10:01 AM       all     53.74      0.16      0.60      0.06      0.00     45.45

12:20:01 AM       all     35.60      0.06      0.50      0.13      0.00     63.71

12:30:01 AM       all      0.17      0.05      0.22      0.03      0.00     99.53

sar -b    (iostat)

[root@localhost /]# sar -b

Linux 2.6.18-308.el5 (localhost.localdomain)    03/14/2017

12:00:01 AM       tps      rtps      wtps   bread/s   bwrtn/s

12:10:01 AM     10.00      0.04      9.96      0.91    393.11

12:20:01 AM     14.62      3.94     10.68    144.79    308.07

12:30:01 AM      9.86      0.19      9.67      2.58    278.07

12:40:01 AM     12.06      0.20     11.86      2.98    328.52

12:50:01 AM      9.54      0.15      9.40      1.84    274.74

01:00:01 AM      9.70      0.13      9.57      1.70    276.60

sar -Bc

[root@localhost /]# sar -B

Linux 2.6.18-308.el5 (localhost.localdomain)    03/14/2017

12:00:01 AM  pgpgin/s pgpgout/s   fault/s  majflt/s

12:10:01 AM      0.23     98.28    422.16      0.01

12:20:01 AM     36.20     77.02    446.76      0.14

12:30:01 AM      0.65     69.53    451.04      0.00

12:40:01 AM      0.75     82.12    449.42      0.00

12:50:01 AM      0.46     68.69    409.23      0.00

sar -c (number of process /sec)

[root@localhost /]# sar -c

Linux 2.6.18-308.el5 (localhost.localdomain)    03/14/2017

12:00:01 AM    proc/s

12:10:01 AM      0.77

12:20:01 AM      0.89

12:30:01 AM      0.80

12:40:01 AM      0.80

sar -r

[root@localhost /]# sar  -r

Linux 2.6.18-308.el5 (localhost.localdomain)    03/14/2017

12:00:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached kbswpfree kbswpused  %swpused  kbswpcad

12:10:01 AM     59532   3948544     98.51     22200   1594164 170665620   3005012      1.73    333680

12:20:01 AM     55860   3952216     98.61     23404   1609856 170682552   2988080      1.72    335440

12:30:01 AM     53316   3954760     98.67     24320   1609972 170682580   2988052      1.72    335452

12:40:01 AM     46304   3961772     98.84     25144   1612908 170682620   2988012      1.72    335420

12:50:01 AM     44712   3963364     98.88     25972   1613088 170682636   2987996      1.72    335420

sar -q

[root@localhost /]# sar -q

Linux 2.6.18-308.el5 (localhost.localdomain)    03/14/2017

12:00:01 AM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15

12:10:01 AM         4       555      1.57      1.43      1.37

12:20:01 AM         0       549      0.04      0.69      1.08

12:30:01 AM         0       549      0.00      0.09      0.55

[root@localhost /]# sar -s 11:22:33 -e 21:21:22

Linux 2.6.18-308.el5 (localhost.localdomain)    03/14/2017

11:30:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle

11:40:01 AM       all      0.38      0.01      0.19      0.74      0.00     98.68

11:50:01 AM       all      0.24      0.03      0.14      0.07      0.00     99.51

12:00:01 PM       all      0.35      0.00      0.15      0.59      0.00     98.91

Control File all section TRACE ..

To take trace of control file ..
alter session set events 'immediate trace name controlf level 9';


Trace file c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace\orcl_ora_5844.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 2 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:2105M/4085M, Ph+PgF:4645M/8169M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 36
Windows thread id: 5844, image: ORACLE.EXE (SHAD)

How to take dump or trace of library cache || internal of library cache

To take trace or dump of library cache, enter following command on sqlplus.

alter session set events 'immediate trace name library_cache level 10';

I  have create a table name pk of one column (id) and inserted with some values as given below..

create table pk(id);

insert into pk values (2121);

insert into pk values(2121);

/

/

/

12288 rows created.

First flush the shared pool for any unwanted or unrelated used space on library cache

SQL> alter system flush shared_pool;

System altered.

Then, clear the buffer cache for any preloaded dirty buffer in buffer cache

SQL> alter system flush buffer_cache;

System altered.

now set autotrace on to view optimizer execution and sql execution statistics;

set autotrace on;

here view on library cache dump after performing query on table pk ( select * from pk)

select * from pk;

      2121

      2121

      2121

      2121

      2121

      2121

        ID

----------

      2121

12288 rows selected.

SP2-0612: Error generating AUTOTRACE report

Execution Plan

----------------------------------------------------------

Plan hash value: 888312997

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 12288 |   156K|     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| PK   | 12288 |   156K|     7   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

to get trace location, view location (value) from v$diag_info

select * from v$diag_info;

SQL> select * from v$diag_info;

   INST_ID NAME

---------- ----------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

         1 Diag Enabled

TRUE

         1 ADR Base

c:\app\vishwakarma

         1 ADR Home

c:\app\vishwakarma\diag\rdbms\orcl\orcl

   INST_ID NAME

---------- ----------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

         1 Diag Trace

c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace

         1 Diag Alert

c:\app\vishwakarma\diag\rdbms\orcl\orcl\alert

         1 Diag Incident

c:\app\vishwakarma\diag\rdbms\orcl\orcl\incident

   INST_ID NAME

---------- ----------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

         1 Diag Cdump

c:\app\vishwakarma\diag\rdbms\orcl\orcl\cdump

         1 Health Monitor

c:\app\vishwakarma\diag\rdbms\orcl\orcl\hm

         1 Default Trace File

c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace\orcl_ora_7556.trc

   INST_ID NAME

---------- ----------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

         1 Active Problem Count

0

         1 Active Incident Count

0

11 rows selected.

check trace of library cache in trace file which has extention of .trc

you can view this using text editor or  notepad and search your query.

========================================================================

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.1 Service Pack 1 

CPU                 : 2 - type 8664, 2 Physical Cores

Process Affinity    : 0x0x0000000000000000

Memory (Avail/Total): Ph:1521M/4085M, Ph+PgF:4148M/8169M 

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 20

Windows thread id: 4928, image: ORACLE.EXE (SHAD)

*** 2017-03-18 23:44:59.808

*** SESSION ID:(9.3) 2017-03-18 23:44:59.808

*** CLIENT ID:() 2017-03-18 23:44:59.808

*** SERVICE NAME:(SYS$USERS) 2017-03-18 23:44:59.808

*** MODULE NAME:(sqlplus.exe) 2017-03-18 23:44:59.808

*** ACTION NAME:() 2017-03-18 23:44:59.808

kwqmnich: current time:: 18: 14: 59: 0

kwqmnich: instance no 0 repartition flag 1 

kwqmnich: initialized job cache structure 

*** 2017-03-18 23:45:00.544

kwqinfy: Call kwqrNondurSubInstTsk 

*** 2017-03-18 23:46:50.366

Library Cache Dump

SGA:63def5f0 Flags=2c7

DebugContext:  DebugNameSpace=0 DebugType=0 DebugLevel=0x0 

LIBRARY CACHE HASH TABLE: size=131072 count=6604

Buckets with more than 20 objects:

NONE

Hash Chain Size     Number of Buckets

---------------     -----------------

              0                124631

              1                  6282

              2                   155

              3                     4

              4                     0

              5                     0

              6                     0

              7                     0

              8                     0

              9                     0

             10                     0

             11                     0

             12                     0

             13                     0

             14                     0

             15                     0

             16                     0

             17                     0

             18                     0

             19                     0

             20                     0

            >20                     0

========================================================================

There many query listed with the bucket, so search for your query ..................

=======================================================================

Bucket: #=27456 Mutex=63efc898(0, 6, 0, 6) 

  LibraryHandle:  Address=5becc958 Hash=5dc66b40 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD 

    ObjectName:  Name=EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * from pk

      FullHashValue=7b8f5775eab4d2d47c16bf605dc66b40 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1573284672 OwnerIdn=0 

    Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1 

    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0 

    Concurrency:  DependencyMutex=5becca08(0, 0, 0, 0) Mutex=5becca80(9, 20, 0, 6) 

    Flags=RON/PIN/TIM/PN0/DBN/[10012841] 

    WaitersLists:  

      Lock=5becc9e8[5becc9e8,5becc9e8] 

      Pin=5becc9f8[5becc9c8,5becc9c8] 

    Timestamp:  Current=03-18-2017 23:48:12 

    LibraryObject:  Address=5e354220 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] 

      DataBlocks:  

        Block:  #='0' name=PCUR^5dc66b40 pins=0 Change=NONE   

          Heap=5becc8a0 Pointer=5e354308 Extent=5e3541a0 Flags=I/-/P/A/-/- 

          FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=7815789 

      ChildTable:  size='16' 

        Child:  id='0' Table=5e3550d0 Reference=5e354b70 Handle=5becc758 

    NamespaceDump:  

      Parent Cursor:  sql_id=7s5pzc1fwcuu0 parent=000007FF5E354308 maxchild=1 plk=n ppn=n 

you can get more detail in v$sql related to hash values and the sql statement that is present in the library cache.