All Posts
Using SQL Tuning Advisor from command line
I recently imported a large table which contains user log data. After import there was not index and table was not analyzed.
I ran a select query
SELECT * FROM USER_LOG WHERE LOG_USER_ID =’JOHN’;
This took 6 seconds to retrieve 70000+ rows
Then i ran
SELECT count(*) FROM USER_LOG WHERE LOG_USER_ID =’JOHN’;
Now i want to use sql tuning advisor to get tuning recommendations and plan to tune this sql.
I created below tuning task.
#############################################################################################
declare
tempstring varchar2(900);
task_id varchar2(200);
begin
tempstring := ‘SELECT * FROM USER_LOG WHERE LOG_USER_ID =”JOHN”’;
task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>’SQLTUNE3′);
dbms_sqltune.execute_tuning_task(‘SQLTUNE3’);
end;
/
###############################################################################################
select dbms_sqltune.report_tuning_task(‘SQLTUNE3’) from dual;
Below is the out put
###############################################################################################
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : SQLTUNE3
Tuning Task Owner : FARHAT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 07/09/2015 13:41:03
Completed at : 07/09/2015 13:42:40
——————————————————————————-
Schema Name: FARHAT
SQL ID : bpby9tjfzn5x3
SQL Text : SELECT * FROM USER_LOG WHERE LOG_USER_ID =’JOHN’
——————————————————————————-
FINDINGS SECTION (3 findings)
——————————————————————————-
1- Statistics Finding
———————
Table “FARHAT”.”USER_LOG” was not analyzed.
Recommendation
————–
– Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => ‘FARHAT’, tabname =>
‘USER_LOG’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);
Rationale
———
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 96.53%)
——————————————
– Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => ‘SQLTUNE3’,
task_owner => ‘FARHAT’, replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 32 will improve its response time
96.53% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement’s resource consumption by
an estimated 10.97% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
—————————————–
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
——————————————–
Weekly DB time (in sec) 0
3- Index Finding (see explain plans section below)
————————————————–
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 54.72%)
——————————————
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index FARHAT.IDX$$_497A0001 on FARHAT.USER_LOG(“LOG_USER_ID”);
Rationale
———
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run “Access Advisor”
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-
1- Original
———–
Plan hash value: 3169137855
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 77112 | 449M| 146K (1)| 00:29:18 |
|* 1 | TABLE ACCESS FULL| USER_LOG | 77112 | 449M| 146K (1)| 00:29:18 |
———————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(“LOG_USER_ID”=’JOHN’)
2- Using New Indices
——————–
Plan hash value: 2445436647
———————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————-
| 0 | SELECT STATEMENT | | 77112 | 449M| 66307 (1)| 00:13:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| USER_LOG | 77112 | 449M| 66307 (1)| 00:13:16 |
|* 2 | INDEX RANGE SCAN | IDX$$_497A0001 | 77112 | | 1704 (1)| 00:00:21 |
———————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“LOG_USER_ID”=’JOHN’)
3- Using Parallel Execution
—————————
Plan hash value: 858866093
——————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
——————————————————————————————————————
| 0 | SELECT STATEMENT | | 77112 | 449M| 5079 (1)| 00:01:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 77112 | 449M| 5079 (1)| 00:01:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 77112 | 449M| 5079 (1)| 00:01:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| USER_LOG | 77112 | 449M| 5079 (1)| 00:01:01 | Q1,00 | PCWP | |
——————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
4 – filter(“LOG_USER_ID”=’JOHN’)
——————————————————————————-
lets implement above recommendations
Create index
create index FARHAT.IDX$$_497A0001 on FARHAT.BAS_USER_LOG(“LOG_USER_ID”);
Gather Statistics
execute dbms_stats.gather_table_stats(ownname => ‘FARHAT’, tabname =>
‘BAS_USER_LOG’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);
SELECT * FROM USER_LOG WHERE LOG_USER_ID =’JOHN’
Now the query time reduces to 158m seconds
SELECT COUNT(*) FROM USER_LOG WHERE LOG_USER_ID=’JOHN’
Now the query time reduces to 46m seconds
#############################################################################################
Monitoring Tablespace growth from sql
set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100
column filename format a20
column tablespace format a15
column status format a10 trunc
column autoextend format a10
select * from dba_data_files;
select
file_name “Filename”,
tablespace_name “Tablespace”,
round(bytes/1024/1024 ,2) “Current Size (MB)”,
autoextensible “Autoextend”,
round(increment_by*8192/1024/1024 ,2) “Autoextend Size (MB)”,
round(maxbytes/1024/1024 ,2) “Max Size (MB)”
from dba_data_files;
select
file_name “Filename”,
tablespace_name “Tablespace”,
round(bytes/1024/1024 ,2) “Current Size (MB)”,
autoextensible “Autoextend”,
round(increment_by*8192/1024/1024 ,2) “Autoextend Size (MB)”,
round(maxbytes/1024/1024 ,2) “Max Size (MB)”
from dba_data_files
where tablespace_name = ‘SYSTEM’;
The following will show you the total size and percentage usage of each tablespace, order by percentage:
select T1.TABLESPACE_NAME,
round(T1.BYTES/1024/1024 ,2) “Used Size (MB)”,
round(T2.BYTES/1024/1024 ,2) “Free Size (MB)”,
round(T2.largest/1024/1024 ,2) “Largest Datafile (MB)”,
round(((T1.BYTES-T2.BYTES)/T1.BYTES)*100,2) “Used %”
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
T1,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
T2
where T1.TABLESPACE_NAME=T2.TABLESPACE_NAME
order by ((T1.BYTES-T2.BYTES)/T1.BYTES) desc;
If you do not want to convert the bytes into MB or whatever, use the following. Otherwise you will get the high values as power of 10 (eg. 3.4360E+10):
column maxbytes format 9,999,999,999,999
column bytes format 9,999,999,999,999
column user_bytes format 9,999,999,999,999
How to find an Oracle Process which is consuming most of memory and CPU
To find out which use is consuming most of memory you can use below query
SELECT a.username, a.osuser, a.program, b.spid,b.pga_used_mem, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM v$session a, v$process b WHERE a.paddr = b.addr
order by
b.pga_used_mem desc
or you can use below filter to limit results to a specific user
–a.username=’FARHAT’
b.spid = ‘6956’;
You can convert bytes into MBs using below command
SELECT a.username, a.osuser, a.program, b.spid,b.pga_used_mem/1024/1024 “PGA USED (MB)”, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM v$session a, v$process b WHERE a.paddr = b.addr
order by
b.pga_used_mem desc
———————————————————
CPU USAGE TRACKING
Below query can identify user which is consuming more CPU Resources
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like ‘%CPU used by this session%’
and
se.SID = ss.SID
and
ss.status=’ACTIVE’
and
ss.username is not null
order by VALUE desc;
After identifying culprit session you can kill it using Oracle commands or OS
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != ‘BACKGROUND’;
Kill the culprit session
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;
The KILL SESSION command doesn’t actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of “marked for kill”. It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it’s worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.
SET LINESIZE 200
COLUMN username FORMAT A15
SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
ALTER SYSTEM DISCONNECT SESSION
The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session ‘30,7’;
alter system disconnect session ‘30,7’
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
SQL>
This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
C:\> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
Warning: If you are using the Multithreaded Model in Oracle 12c, you should not attempt to kill operating system processes. To know why, read this.
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
% kill spid
If after a few minutes the process hasn’t stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
% ps -ef | grep ora
Some other useful linux commands
ps -U oracle -l
ps -U oracle -F
To check top resource consuming sessions
top
ps -A
ps -e
pstree
Configuring DG4ODBC
D:\app\Oracle\product\11.2.0\dbhome_1
dg4odbc configuration file “initdg4odbc.ora” is located under directory
D:\app\Oracle\product\11.2.0\dbhome_1\hs\admin
Contents of initdg4odbc.ora file
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = sqlsysodbc
HS_FDS_TRACE_LEVEL =
#
# Environment variables required for the non-Oracle system
#
#set =
#####################################################
where sqlsysodbc is name of system dsn we created for odbc connectivity.
Configure Oracle Listener
# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\app\Oracle\product\11.2.0\dbhome_1)
(PROGRAM=dg4odbc)
)
)
#CONNECT_TIMEOUT_LISTENER = 0
#############################################################
Above content in red is added so listener can listen for sid using parameter file
D:\app\Oracle\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora by invoking utility dg4odbc
Configure tnsnames.ora
Add entry for tnsnames.ora
dg4odbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
###############################################################
If you want to access Remote SQL Server DB using domain username you need to right click listener and click Log on tab. Add your domain username and password.
Create db link
CREATE PUBLIC DATABASE LINK “TEST_SQL”
CONNECT TO “MYDOMAIN\USERNAME”
IDENTIFIED BY “PASSWORD”
USING ‘dg4odbc’;
############################################################
Now you can connect sql server from Oracle using domain user
select * from dual@test_sql
DUMMY
—————-
X
Relocating Voting disk and OCR from one disk group to another
C:\Users\farhat>crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 03c219af7dbe4f3dbf7ad4949cccbd42 (\\.\ORCLDISKOCRVOTE0) [OCRVOTE]
Located 1 voting disk(s).
C:\Users\farhat>crsctl replace votedisk +OCRVOTENEW
Successful addition of voting disk 7e56811064114ff9bfbee059c4ebacc6.
Successful deletion of voting disk 03c219af7dbe4f3dbf7ad4949cccbd42.
Successfully replaced voting disk group with +OCRVOTENEW.
CRS-4266: Voting file(s) successfully replaced
C:\Users\farhat>crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 7e56811064114ff9bfbee059c4ebacc6 (\\.\ORCLDISKOCRVOTENEW0) [OCRVOTENEW]
Located 1 voting disk(s).
Move OCR from diskgroup +OCRVOTE to +OCRVOTENEW
C:\Users\farhat>ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3316
Available space (kbytes) : 258804
ID : 1825388376
Device/File Name : +OCRVOTE
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
———————————————————————-
C:\Users\farhat>ocrconfig -add +OCRVOTENEW
C:\Users\farhat>ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3316
Available space (kbytes) : 258804
ID : 1825388376
Device/File Name : +OCRVOTE
Device/File integrity check succeeded
Device/File Name : +OCRVOTENEW
Device/File integrity check succeeded
Device/File not configured
————————————————————————–
C:\Users\farhat>ocrconfig -delete +OCRVOTE
—————————————————————————
C:\Users\farhat>ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3316
Available space (kbytes) : 258804
ID : 1825388376
Device/File Name : +OCRVOTENEW
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
—————————————————————————
SPFILE parameter in local node’s pfile is automatically changed upon db startup
I manually created an spfile from pfile after some modifications and placed it in directory
+DATA/TESTRAC/spfile.259.858334859
This is not a standard path, instead it should be
+DATA/TESTRAC/PARAMETERFILE/spfile.259.858334859
I recreated spfile by first creating pfile from above spfile and then recreated spfile under direcotry
+DATA/TESTRAC/PARAMETERFILE/spfile.259.858334859
I updated spfile path parameter in initRACTEST1 and initRACTEST2 from
+DATA/TESTRAC/spfile.259.858334859
to
+DATA/TESTRAC/PARAMETERFILE/spfile.259.858334859
but as i started database from
C:\Users\farif> srvctl start database -d ractest
it gave me error that spfile cannot be found. when i checked the initRACTEST1 and 2 on both nodes spfile parameter line is changed again to old path
then i checked database status from using
C:\Users\farif>srvctl config database -d testrac
Database unique name: TESTRAC
Database name: TESTRAC
Oracle home: D:\app\Oracle\product\11.2.0.4.0\dbhome_1
Oracle user: nt authority\system
Spfile: +DATA/TESTRAC/spfileTESTRAC.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TESTRAC
Database instances: TESTRAC1,TESTRAC2
Disk Groups: DATA,FLASH
Mount point paths:
Services:
Type: RAC
Database is administrator managed
————————————————————————
This is the problem. I changed db configuration and updated spfile path.
C:\Users\farif>srvctl modify database -d testrac -p +DATA/TESTRAC/PARAMETERFILE/spfile.259.858334859
C:\Users\farif>srvctl config database -d testrac
Database unique name: TESTRAC
Database name: TESTRAC
Oracle home: D:\app\Oracle\product\11.2.0.4.0\dbhome_1
Oracle user: nt authority\system
Spfile: +DATA/TESTRAC/PARAMETERFILE/spfile.259.858334859
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TESTRAC
Database instances: TESTRAC1,TESTRAC2
Disk Groups: DATA,FLASH
Mount point paths:
Services:
Type: RAC
Database is administrator managed
C:\Users\farif>srvctl start database -d testrac
database is started and initTESRAC1 and 2 are not modified this time.
spfile in RAC / ASM created or restored under DB_UNKNOWN directory
Recently i came across a problem while creating spfile from pfile in RAC. Due to a wrongly added parameter in spfile db was unable to start. I created PFILE from SPFILE on local drive of one node.
SQL> create pfile=’d:\temp\initRACTEST.ora’ from pfile=’+DATA/RACTEST/spfileRACTEST.ora’
I changed the wrongly configured parameter sga_target to appropriate value and then created SPFILE from PFILE.
SQL > create spfile=’+DATA’ from pfile=’d:\temp\initRACTEST.ora’;
Now i checked directory from asmcmd
C:\Users\farhat>asmcmd -p
ASMCMD [+DATA] > ls
DB_UNKNOWN/
TESTRAC/
ASMCMD [+DATA] > cd db_unknown
ASMCMD [+DATA/db_unknown] > ls
PARAMETERFILE/
ASMCMD [+DATA/db_unknown] > cd parameterfile
ASMCMD [+DATA/db_unknown/parameterfile] > ls
SPFILE.259.858334053
OOPS….Something went wrong. Directory should be +DATA/RACTEST/PARAMETERFILE but here a new direcotry created with name DB_UNKNOWN
Lets delete it.
ASMCMD [+DATA/db_unknown/parameterfile] > rm *.*
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+DATA/db_unknown/parameterfile] > cd +data
ASMCMD [+data] > ls
TESTRAC/
after Google for a while i found that as instance was not started that’s why DB_UNKNOW direcotry is created and file is placed under this directory.
Lets start instance with pfile in nomount state
C:\Users\farhat>set oracle_home=D:\app\Oracle\product\11.2.0.4.0\dbhome_1
C:\Users\farhat>set oracle_sid=TESTRAC1
C:\Users\farhat>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 15 09:49:12 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile=’+DATA’ from pfile=’d:\temp\testracp.txt’;
File created.
SQL> startup nomount pfile=’d:\temp\testracp.txt’;
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size 2290648 bytes
Variable Size 3355446312 bytes
Database Buffers 7314866176 bytes
Redo Buffers 16871424 bytes
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string
SQL> create spfile=’+DATA’ from pfile=’D:\temp\testracp.txt’;
File created.
Lets check file path again from asmcmd
C:\Users\farif>asmcmd -p
ASMCMD [+] > ls
DATA/
FLASH/
OCRVOTE/
OCRVOTENEW/
ASMCMD [+] > cd data
ASMCMD [+data] > ls
TESTRAC/
ASMCMD [+data] > cd testrac
ASMCMD [+data/testrac] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD [+data/testrac] > cd parameterfile
ASMCMD [+data/testrac/parameterfile] > ls
spfile.259.858334859
ASMCMD [+data/testrac/parameterfile] > exit
C:\Users\farif>srvctl modify database -d testrac -p +DATA/TESTRAC/PARAMETERFILE/spfile.259.858334859
C:\Users\farif>srvctl config database -d testrac
Database unique name: TESTRAC
Database name: TESTRAC
Oracle home: D:\app\Oracle\product\11.2.0.4.0\dbhome_1
Oracle user: nt authority\system
Spfile: +DATA/TESTRAC/PARAMETERFILE/spfile.259.858334859
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TESTRAC
Database instances: TESTRAC1,TESTRAC2
Disk Groups: DATA,FLASH
Mount point paths:
Services:
Type: RAC
Database is administrator managed
C:\Users\farif>srvctl start database -d testrac
Recovering a table from RMAN Backup in Oracle 12C database
In Oracle 12C new functionality is added that we can restore an individual table from RMAN Backup. In below example we will import a table farhat_log from a dump file, take rman backup of database and then drop the table we want to restore.
imp ‘/ as sysdba’ fromuser=farhat touser=farhat file=’d:\temp\farhat_log.dmp’ log=d:\temp\farhat_log.log’ buffer=20971520
SQL> select table_name,num_rows from dba_tables where owner =’FARHAT’;
TABLE_NAME NUM_ROWS
———— ———-
FARHAT_LOG 1134527
rman target /
RMAN> backup as compressed backupset database;
SQL> select current_scn from v$database;
CURRENT_SCN
———–
2874383
SQL> drop table farhat.farhat_log cascade constraints;
Table dropped
RMAN> recover table farhat.bas until scn 2874383 auxiliary destination ‘d:\temp’;
This will first create an auxiliary instance and restore db to directory ‘D:\temp’ and restore database, extract data pump dump from of table from auxiliary database and then drop auxiliary instance and recover table from datapump file to primary db .
SQL> select table_name,num_rows from dba_tables where owner =’FARHAT’;
TABLE_NAME NUM_ROWS
———— ———-
FARHAT_LOG 1134527
Upgrading Oracle RAC 11.2.0.3 to 11.2.0.4 on Windows (GRID and Database)
Run Cluster Verification Utility to ensure that everything is fine for upgrade process
D:\Sources\11.2.0.4\grid>runcluvfy.bat stage -pre crsinst -upgrade -n OR-11,OR-11 -rolling -src_crshome D:\app\11.2.0\grid -dest_crshome D:\app\11.2.0.4\grid -dest_version 11.2.0.4.0 -verbose >> d:\temp\fixup\fixup.txt
=================================================
Output d:\temp\fixup\fixup.txt
Performing pre-checks for cluster services setup
Checking node reachability…
Check: Node reachability from node “or-11”
Destination Node Reachable?
———————————— ————————
or-12 yes
or-11 yes
Result: Node reachability check passed from node “or-11”
Checking user equivalence…
Check: User equivalence for user “farhat”
Node Name Status
———————————— ————————
or-12 passed
or-11 passed
Result: User equivalence check passed for user “farhat”
Checking node connectivity…
Interface information for node “or-12”
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
—— ————— ————— ————— ————— —————– ——
public 192.168.1.12 192.168.1.0 On-link UNKNOWN 00:1A:A0:34:91:0A 1500
public 192.168.1.14 192.168.1.0 On-link UNKNOWN 00:1A:A0:34:91:0A 1500
public 192.168.1.65 192.168.1.0 On-link UNKNOWN 00:1A:A0:34:91:0A 1500
private 10.11.11.2 10.11.11.0 On-link UNKNOWN 00:1A:A0:34:91:0C 1500
VMware Network Adapter VMnet1 192.168.253.1 192.168.253.0 On-link UNKNOWN 00:50:56:C0:00:01 1500
VMware Network Adapter VMnet8 192.168.59.1 192.168.59.0 On-link UNKNOWN 00:50:56:C0:00:08 1500
Interface information for node “or-11”
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
—— ————— ————— ————— ————— —————– ——
public 192.168.1.11 192.168.1.0 On-link UNKNOWN 00:1D:09:0C:B4:CD 1500
public 192.168.1.13 192.168.1.0 On-link UNKNOWN 00:1D:09:0C:B4:CD 1500
public 192.168.1.64 192.168.1.0 On-link UNKNOWN 00:1D:09:0C:B4:CD 1500
public 192.168.1.66 192.168.1.0 On-link UNKNOWN 00:1D:09:0C:B4:CD 1500
private 10.11.11.1 10.11.11.0 On-link UNKNOWN 00:1D:09:0C:B4:CF 1500
VirtualBox Host-Only Network 169.254.30.184 169.254.0.0 On-link UNKNOWN 08:00:27:00:D0:E0 1500
Check: Node connectivity for interface “public”
Source Destination Connected?
—————————— —————————— —————-
or-12[192.168.1.12] or-12[192.168.1.14] yes
or-12[192.168.1.12] or-12[192.168.1.65] yes
or-12[192.168.1.12] or-11[192.168.1.11] yes
or-12[192.168.1.12] or-11[192.168.1.13] yes
or-12[192.168.1.12] or-11[192.168.1.64] yes
or-12[192.168.1.12] or-11[192.168.1.66] yes
or-12[192.168.1.14] or-12[192.168.1.65] yes
or-12[192.168.1.14] or-11[192.168.1.11] yes
or-12[192.168.1.14] or-11[192.168.1.13] yes
or-12[192.168.1.14] or-11[192.168.1.64] yes
or-12[192.168.1.14] or-11[192.168.1.66] yes
or-12[192.168.1.65] or-11[192.168.1.11] yes
or-12[192.168.1.65] or-11[192.168.1.13] yes
or-12[192.168.1.65] or-11[192.168.1.64] yes
or-12[192.168.1.65] or-11[192.168.1.66] yes
or-11[192.168.1.11] or-11[192.168.1.13] yes
or-11[192.168.1.11] or-11[192.168.1.64] yes
or-11[192.168.1.11] or-11[192.168.1.66] yes
or-11[192.168.1.13] or-11[192.168.1.64] yes
or-11[192.168.1.13] or-11[192.168.1.66] yes
or-11[192.168.1.64] or-11[192.168.1.66] yes
Result: Node connectivity passed for interface “public”
Check: TCP connectivity of subnet “192.168.1.0”
Source Destination Connected?
—————————— —————————— —————-
or-11:192.168.1.11 or-12:192.168.1.12 passed
or-11:192.168.1.11 or-12:192.168.1.14 passed
or-11:192.168.1.11 or-12:192.168.1.65 passed
or-11:192.168.1.11 or-11:192.168.1.13 passed
or-11:192.168.1.11 or-11:192.168.1.64 passed
or-11:192.168.1.11 or-11:192.168.1.66 passed
Result: TCP connectivity check passed for subnet “192.168.1.0”
Check: Node connectivity for interface “private”
Source Destination Connected?
—————————— —————————— —————-
or-12[10.11.11.2] or-11[10.11.11.1] yes
Result: Node connectivity passed for interface “private”
Check: TCP connectivity of subnet “10.11.11.0”
Source Destination Connected?
—————————— —————————— —————-
or-11:10.11.11.1 or-12:10.11.11.2 passed
Result: TCP connectivity check passed for subnet “10.11.11.0”
Checking subnet mask consistency…
Subnet mask consistency check passed for subnet “192.168.1.0”.
Subnet mask consistency check passed for subnet “10.11.11.0”.
Subnet mask consistency check passed.
Result: Node connectivity check passed
Checking multicast communication…
Checking subnet “192.168.1.0” for multicast communication with multicast group “230.0.1.0”…
PRVG-11134 : Interface “192.168.1.14” on node “or-12” is not able to communicate with interface “192.168.1.11” on node “or-11”
PRVG-11134 : Interface “192.168.1.14” on node “or-12” is not able to communicate with interface “192.168.1.13” on node “or-11”
PRVG-11134 : Interface “192.168.1.14” on node “or-12” is not able to communicate with interface “192.168.1.64” on node “or-11”
PRVG-11134 : Interface “192.168.1.14” on node “or-12” is not able to communicate with interface “192.168.1.66” on node “or-11”
PRVG-11134 : Interface “192.168.1.65” on node “or-12” is not able to communicate with interface “192.168.1.11” on node “or-11”
PRVG-11134 : Interface “192.168.1.65” on node “or-12” is not able to communicate with interface “192.168.1.13” on node “or-11”
PRVG-11134 : Interface “192.168.1.65” on node “or-12” is not able to communicate with interface “192.168.1.64” on node “or-11”
PRVG-11134 : Interface “192.168.1.65” on node “or-12” is not able to communicate with interface “192.168.1.66” on node “or-11”
PRVG-11134 : Interface “192.168.1.11” on node “or-11” is not able to communicate with interface “192.168.1.12” on node “or-12”
PRVG-11134 : Interface “192.168.1.11” on node “or-11” is not able to communicate with interface “192.168.1.14” on node “or-12”
PRVG-11134 : Interface “192.168.1.11” on node “or-11” is not able to communicate with interface “192.168.1.65” on node “or-12”
PRVG-11134 : Interface “192.168.1.13” on node “or-11” is not able to communicate with interface “192.168.1.12” on node “or-12”
PRVG-11134 : Interface “192.168.1.13” on node “or-11” is not able to communicate with interface “192.168.1.14” on node “or-12”
PRVG-11134 : Interface “192.168.1.13” on node “or-11” is not able to communicate with interface “192.168.1.65” on node “or-12”
PRVG-11134 : Interface “192.168.1.64” on node “or-11” is not able to communicate with interface “192.168.1.12” on node “or-12”
PRVG-11134 : Interface “192.168.1.64” on node “or-11” is not able to communicate with interface “192.168.1.14” on node “or-12”
PRVG-11134 : Interface “192.168.1.64” on node “or-11” is not able to communicate with interface “192.168.1.65” on node “or-12”
PRVG-11134 : Interface “192.168.1.66” on node “or-11” is not able to communicate with interface “192.168.1.12” on node “or-12”
PRVG-11134 : Interface “192.168.1.66” on node “or-11” is not able to communicate with interface “192.168.1.14” on node “or-12”
PRVG-11134 : Interface “192.168.1.66” on node “or-11” is not able to communicate with interface “192.168.1.65” on node “or-12”
Checking subnet “192.168.1.0” for multicast communication with multicast group “224.0.0.251”…
Check of subnet “192.168.1.0” for multicast communication with multicast group “224.0.0.251” passed.
Checking subnet “10.11.11.0” for multicast communication with multicast group “230.0.1.0”…
PRVG-11134 : Interface “10.11.11.1” on node “or-11” is not able to communicate with interface “10.11.11.2” on node “or-12”
Checking subnet “10.11.11.0” for multicast communication with multicast group “224.0.0.251”…
Check of subnet “10.11.11.0” for multicast communication with multicast group “224.0.0.251” passed.
Check of multicast communication passed.
Checking the status of Windows firewall
Node Name Enabled? Comment
———— ———————— ————————
or-12 no passed
or-11 no passed
Result: Windows firewall verification check passed
Checking OCR integrity…
OCR integrity check passed
Check: Total memory
Node Name Available Required Status
———— ———————— ———————— ———-
or-12 15.9951GB (1.6772032E7KB) 922MB (944128.0KB) passed
or-11 15.9951GB (1.6772032E7KB) 922MB (944128.0KB) passed
Result: Total memory check passed
Check: Available memory
Node Name Available Required Status
———— ———————— ———————— ———-
or-12 10.8619GB (1.1389492E7KB) 50MB (51200.0KB) passed
or-11 9.8306GB (1.030812E7KB) 50MB (51200.0KB) passed
Result: Available memory check passed
Check: Swap space
Node Name Available Required Status
———— ———————— ———————— ———-
or-12 31.9883GB (3.3542208E7KB) 15.9951GB (1.6772032E7KB) passed
or-11 31.9883GB (3.3542208E7KB) 15.9951GB (1.6772032E7KB) passed
Result: Swap space check passed
Check: Free disk space for “or-12:D:\app\11.2.0.4\grid”
Path Node Name Mount point Available Required Status
—————- ———— ———— ———— ———— ————
D:\app\11.2.0.4\grid or-12 D 257.0327GB 4.5GB passed
Result: Free disk space check passed for “or-12:D:\app\11.2.0.4\grid”
Check: Free disk space for “or-11:D:\app\11.2.0.4\grid”
Path Node Name Mount point Available Required Status
—————- ———— ———— ———— ———— ————
D:\app\11.2.0.4\grid or-11 D 132.5272GB 4.5GB passed
Result: Free disk space check passed for “or-11:D:\app\11.2.0.4\grid”
Check: Free disk space for “or-12:C:\Windows\temp”
Path Node Name Mount point Available Required Status
—————- ———— ———— ———— ———— ————
C:\Windows\temp or-12 C 18.3252GB 1GB passed
Result: Free disk space check passed for “or-12:C:\Windows\temp”
Check: Free disk space for “or-11:C:\Windows\temp”
Path Node Name Mount point Available Required Status
—————- ———— ———— ———— ———— ————
C:\Windows\temp or-11 C 18.8738GB 1GB passed
Result: Free disk space check passed for “or-11:C:\Windows\temp”
There are no oracle patches required for home “D:\app\11.2.0\grid”.
There are no oracle patches required for home “D:\app\11.2.0.4\grid”.
Check: System architecture
Node Name Available Required Status
———— ———————— ———————— ———-
or-12 64-bit 64-bit passed
or-11 64-bit 64-bit passed
Result: System architecture check passed
Checking length of value of environment variable “PATH”
Check: Length of value of environment variable “PATH”
Node Name Set? Maximum Length Actual Length Comment
—————- ———— ———— ———— —————-
or-12 yes 5119 552 passed
or-11 yes 5119 31 passed
Result: Check for length of value of environment variable “PATH” passed.
Checking for Media Sensing status of TCP/IP
Node Name Enabled? Comment
———— ———————— ————————
or-12 no passed
or-11 no passed
Result: Media Sensing status of TCP/IP check passed
Starting Clock synchronization checks using Network Time Protocol(NTP)…
Checking daemon liveness…
Check: Liveness for “W32Time”
Node Name Running?
———————————— ————————
or-12 yes
or-11 yes
Result: Liveness check passed for “W32Time”
Check for NTP daemon or service alive passed on all nodes
Result: Clock synchronization check using Network Time Protocol(NTP) passed
Checking if current user is a domain user…
Check: If user “farhat” is a domain user
Result: User “farhat” is a part of the domain “testdomain”
Check: Time zone consistency
Result: Time zone consistency check passed
Checking VIP configuration.
Checking VIP Subnet configuration.
Check for VIP Subnet configuration passed.
Checking VIP reachability
Check for VIP reachability passed.
Checking Oracle Cluster Voting Disk configuration…
ASM Running check passed. ASM is running on all specified nodes
Oracle Cluster Voting Disk configuration check passed
Clusterware version consistency passed
Checking for status of Automount feature
Node Name Enabled? Comment
———— ———————— ————————
or-12 yes passed
or-11 yes passed
Result: Check for status of Automount feature passed
Pre-check for cluster services setup was successful.
=================================================
Stop enterprise manager dbconsole
D:\>set oracle_unqname=fintest
D:\>emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://OR-11.localdomain.com:1158/em/console/aboutApplication
The OracleDBConsolefintest service is stopping………
The OracleDBConsolefintest service was stopped successfully.
————————————————————
While installing Patchset setup will stop cluster services and associate with new home bineries and restart. You must stop windows Windows Management Instrumentation Service and check in process explorer, if any process for this service is running kill it and stop above service.
This service acquires a lock on following files under folder C:\WINDOWS\system32\drivers
oracleacfs.sys
oracleadvm.sys
oracleoks.sys
When installation setup tries to access these files it fails because these files are already locked by Windows Management Instrumentation Service process
————————————————————
Unset ORACLE environments from Windows Path
My Computer > Properties > Advanced > Environment Veriables > System Veriable > Path
Delete Oracle Homes from Path Veriable
Start Setup and install Oracle Grid Infrastructure in a new Grid Home
old home
D:\app\grid\11.2.0
New Grid Home
D:\app\grid\11.2.0.4
If you already have followed above instructions, hopefully this process should complete successfully.
After successfull upgrade
Deinstall Old Home
Deinstall old home by running deinstall script
d:\app\11.2.0\grid\deinstall\deinstall
Note: (Run it from root of D drive otherwise directories will not be properly deleted if you are into these directories)
Deinstall script will delete old home,registry entries and services from both nodes
———————————————————–
Upgrade Database.
Install Oracle Database under a new home (Different from old one)
Just Software Installation.
Old Home
D:\app\Oracle\product\11.2.0.3\dbhome_1
Install Database in new home
D:\app\Oracle\product\11.2.0.4\dbhome_1
——————————————————————————————-
Run database upgrade assistant from Newly installed home
D:\app\Oracle\product\11.2.0.4\dbhome_1
PAK DBA 