Using two network interfaces, vnet1 and vnet2 it is possible to create a redundant public interface that is used as one by Grid Infrastructure. If one of the interfaces (and depending on network design, links) is unavailable the public interface is still available.
Current configuration:
$ srvctl config nodeapps
Network exists: 1/165.156.22.240/255.255.255.240/vnet1, type static
VIP exists: /myserver01-vip/165.156.22.246/165.156.22.240/255.255.255.240/vnet1, hosting node myserver01
VIP exists: /myserver02-vip/165.156.22.247/165.156.22.240/255.255.255.240/vnet1, hosting node myserver02
GSD exists
ONS exists: Local port 6100, remote port 6200, EM port 2016
$ crsctl check cluster -all
**************************************************************
myserver01:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
myserver02:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
Below are the network interfaces, you can see the VIP (.246) and SCANs (.248, .250) bound to vnet1. vnet2 has nothing running on it.
$ /usr/sbin/ifconfig -a
....
vnet1: flags=9040843 mtu 1500 index 3
inet 165.156.22.251 netmask fffffff0 broadcast 165.156.22.255
groupname topside
vnet1:1: flags=1000843 mtu 1500 index 3
inet 165.156.22.244 netmask fffffff0 broadcast 165.156.22.255
vnet1:2: flags=1040843 mtu 1500 index 3
inet 165.156.22.246 netmask fffffff0 broadcast 165.156.22.255
vnet1:4: flags=1040843 mtu 1500 index 3
inet 165.156.22.248 netmask fffffff0 broadcast 165.156.22.255
vnet1:6: flags=1040843 mtu 1500 index 3
inet 165.156.22.250 netmask fffffff0 broadcast 165.156.22.255
vnet2: flags=9040843 mtu 1500 index 4
inet 165.156.22.252 netmask fffffff0 broadcast 165.156.22.255
groupname topside
...
...
To perform the change an outage for all PUBLIC traffic is required:
Passing the -debug option to runInstaller provides more information on where a pre-requisite could be failing. The 11.1 Grid Agent no longer uses a GUI and is required to be installed command line. The following error message does not provide many clues as to what the pre-requisite fail could be:
Checking Temp space: must be greater than 150 MB. Actual 1083 MB Passed
Checking swap space: must be greater than 150 MB. Actual 16383 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-05-10_10-36-59AM.
Please wait ...
*** Check for updates ***
*** Select Installation Type ***
*** Check Prerequisites ***
Some recommended prerequisites checks are failed. You might get errors during installation. Please fix those prerequisites and start the installation again.
Further, the README.TXT contains the following:
For detailed information on using silent installation method to install the Management agent please refer to .Enterprise Manager Silent Installations. described in the Enterprise Manager Grid Control Basic Installation available at:
After reading through the documentation, the -debug flag can be used for the runInstaller which gives us more detailed information as to what the problem can be:
Checking if Oracle software certified on the current O/S... 1
In Progress
Checking for required packages installed on the system .... 1
In Progress
Checking whether the software compatibile for current o/s... 7
In Progress
Checking for sufficient diskspace in TEMP location... 1
In Progress
Checking for sufficient diskspace in Inventory location... 1
In Progress
Checking for the Hostname... 1
This is a prerequisite condition to test whether the Oracle software is certified on the current O/S or not.Succeeded
This is a prerequisite condition to test whether the minimum required packages are available on the system.Succeeded
This is a prerequisite condition to test whether the software is compatible for this o/sFailed
This check ensures that sufficient diskspace is available in system TEMP location.Succeeded
This check ensures that sufficient diskspace is available in system Inventory location.Succeeded
This is a prerequisite condition to test whether the host name where the installation will be done, is correct or not.Succeeded
Silent Install page at 3 oracle.sysman.install.oneclick.EMGCPrereqDialog,pageTitle=Check Prerequisites,label=Check Prerequisites,selected
Some recommended prerequisites checks are failed. You might get errors during installation. Please fix those prerequisites and start the installation again.
At some stage a DBA will encounter the following error when resizing a datafile:
ORA-03297: file contains used data beyond requested RESIZE value
What may be more frustrating is that the datafile has plenty of free space.
So what can be done to solve this error and resize down the datafile? Why does it occur? Some might be tempted to just add more space to the filesystem and be done with it. Not everyone has that option.
A datafile cannot be resized down below the High Water Mark (HWM) and that's what causes the ORA-03297 error. . In the lifetime of the datafile some extents have been created bumping up the HWM. Others below the HWM have also been DELETED.
I will show an example of a datafile encountering this error, what is happening inside the datafile and how to resolve the error. Let's create a tablespace first and the two tables, one with many rows and a second with a few rows.
CREATE TABLESPACE "MYTS" DATAFILE '/oradata1/MYDB//mytsdatafile01.dbf' SIZE 32M
AUTOEXTEND ON NEXT 1310720 MAXSIZE 64M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
Tablespace created.
SQL> create table myuser.mytab1 tablespace MYTS as
(select level id,dbms_random.string('a',dbms_random.value(1,4000)) str
from dual
connect by level < 20000
)
/
Table created.
SQL> create table myuser.mytab2 tablespace MYTS as
(select level id,dbms_random.string('a',dbms_random.value(1,4000)) str
from dual
connect by level < 10
)
/
Table created.
How many MB is now in use by these tables?
select segment_name, bytes/1024/1024
from dba_segments
where segment_name in ('MYTAB1','MYTAB2')
and owner = 'MYUSER'
/
SEGMENT_NAME BYTES/1024/1024
-----------------------------------------------
MYTAB1 54
MYTAB2 .0625
To demonstrate how these two tables fit in the MYTS tablespace I want to firstly show a few things about the datafile.
DFSIZEMB = the datafile size in MB on disk.
HWMMB = the location of the HWM in the datafile.
DFREEMB = the number of FREE MB in the datafile.
%FREE = the percentage of FREE space in the datafile. RESIZEABLE = the amount of MB that can be gained from resizing.
Use the below SQL to see how much space can be gained from a RESIZE and the location of the HWM in a datafile. For a database with many datafiles this query may take a while.
column file_name format a50
column tablespace_name format a20
select
tablespace_name,
file_id,
file_name,
dfsizeMB,
hwmMB,
dffreeMB,
trunc((dffreeMB/dfsizeMB)*100,2) "% Free",
trunc(dfsizeMB-hwmMB,2) "Resizeble"
from
(
select
df.tablespace_name tablespace_name,
df.file_id file_id,
df.file_name file_name,
df.bytes/1024/1024 dfsizeMB,
trunc((ex.hwm*(ts.block_size))/1024/1024,2) hwmMB,
dffreeMB
from
dba_data_files df,
dba_tablespaces ts,
(
select file_id, sum(bytes/1024/1024) dffreeMB
from dba_free_space
group by file_id
) free,
(
select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id
) ex
where df.file_id = ex.file_id
and df.tablespace_name = ts.tablespace_name
and df.file_id = free.file_id (+)
order by df.tablespace_name, df.file_id
)
/
TABLESPACE_NAME FILE_ID File Name DFSIZEMB HWMMB DFFREEMB % Free Resizeble
-------------------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ----------...
MYTS 14 /oradata1/MYDB/mytsdatafile01.dbf 55.75 55.06 .6875 1.23 .69
...
Above, the datafile can only be resized by 0.69MB and the HWM is at the 55MB mark with the datafile size on disk being 55.75MB.
How do these two tables look like within the datafile? NOTE: This query may take a while for a large datafile with lots of extents.
QUERY 2 - location of segments within a datafile
column segment heading 'Segment Name' format a14
column file_name heading 'File Name' format a40
column segment_type heading 'Segment Type' format a10
select
file_name,
segment_type,
owner||'.'||segment_name segment,
block_id,
blockIdMB
from
(
select
ex.owner owner,
ex.segment_name segment_name,
ex.segment_type segment_type,
ex.block_id block_id,
df.file_name file_name,
trunc((ex.block_id*(ts.block_size))/1024/1024,2) blockIdMB
from
dba_extents ex, dba_data_files df, dba_tablespaces ts
where df.file_id = &file_id
and df.file_id = ex.file_id
and df.tablespace_name = ts.tablespace_name
order by ex.block_id desc
)
where rownum <= 100
/
Enter value for file_id: 14
File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB
--------------------------------- ---------- -------------- ---------- ----------
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 7040 55
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6912 54
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6784 53
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6656 52
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6528 51
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6400 50
...
...
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 144 1.12
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 136 1.06
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
We can see table MYTAB1 ends around the 54MB block in the datafile and MYTAB2 starts around the 55MB block.
So we now have two tables in the MYTS tablespace created one after the other. Further, how about we create a situation where there is alot of free space in the datafile and cannot be resized resulting in "ORA-03297: file contains used data beyond requested RESIZE value". DELETING all the rows from the MYTAB1 table will not free up any space and will still have it allocated to MYTAB1, so let's TRUNCATE the table MYTAB1 and run the same SQL above again to see the result of the datafile.
The datafile now has 97% and 54MB FREE space. The HWM is still at the 55MB mark. Inspecting the datafile, MYTAB1 is at block 1MB and MYTAB2 is still at of course 55MB.
File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB
--------------------------------- ---------- -------------- ---------- ----------
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 7040 55
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
So seeing we have 54MB FREE space in a 55MB datafile let's try to resize it down to 50MB.
SQL> alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 50M;
alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 50M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
The only way to resize the datafile is to lower the HWM. This is achieved by moving the extents located at the HWM either to another tablespace or the same tablespace. For the same tablespace ensure plenty of free space and run QUERY 2 after each MOVE to see if anything is achieved.
Of course for a production environment where there are many users accessing the table, index, LOB etc it might not be that easy and this sort of activity would need to be carefully planned, especially with LOB segments. Also, when moving a table the underlying INDEXES are marked UNUSABLE and need to be rebuilt.
SQL> alter table myuser.mytab2 move tablespace MYTS;
Table altered.
Now the datafile shows that MYTAB2 is at block 1MB.
File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB
----------------------------------- ---------- -------------- ---------- ----------
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 136 1.06
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
Now the HWM is at the 1.12 MB mark and there is 54MB of reclaimable space in the datafile.
SQL> alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 2M;
Database altered.
Datafile now resized! In a production environment there may be many different objects extents near the HWM and simply moving one table might not be so common. It's best to see how many different objects are around the HWM and calculate how much space can be reclaimed via the MOVE. Unless there is a benefit in reclaiming a large amount of space, or moving tables, indexes, LOBs etc is something that just has to be done, then if possible get more space added to the filesystem.
Using a GROUP BY and some arithmetic it is possible to improve the performance of SQL comparing data of two tables. Even better, in Oracle this can be achieved with the PARTITION BY analytic function.
Below is an example comparing two tables (TAB1, TAB2) with two columns (col1 number, col2 varchar2) using a PARTITION BY analytic function:
create table tab1 (col1 number, col2 varchar2(3));
create table tab2 (col1 number, col2 varchar2(3));
insert into tab1 (col1, col2) values (3, 'ccc');
insert into tab1 (col1, col2) values (1, 'aaa');
insert into tab2 (col1, col2) values (1, 'aaa');
insert into tab2 (col1, col2) values (2, 'aaa');
select * from
-- list all results
-- change to "select count(*) from" for a count of differences
(
select rid, -- ROWID
col1, -- column 1 of table to compare
col2, -- column 2 of table to compare
sum(summ) over (partition by col1, col2) summ
-- must list all columns to compare in the PARTITION BY
from
(select 'In TAB1, NOT in TAB2' tab,
rowid rid, -- need to alias ROWID
-- list all/some columns of table to compare
col1, -- column 1 of table to compare
col2, -- column 2 of table to compare
------
1 summ
-- summ is used for sum() operation,
-- a matched row from other table will sum to 0
from tab1
UNION ALL
-- change to UNION for unique rows only
select 'In TAB2, NOT in TAB1' tab,
rowid rid, -- need to alias ROWID
-- list all/some columns of table to compare
col1, -- column 1 of table to compare
col2, -- column 2 of table to compare
------
-1 summ
-- summ used for sum() operation,
-- a matched row from other table will sum to 0
from tab2)
)
-- all the rows that didn't have a match have a non-zero sum()
where summ != 0
/
RID COL1 COL SUMM
------------------ ---------- --- ----------
AAAWK9AAGAAADNHAAB 2 aaa -1
AAAWK8AAGAAADM/AAA 3 ccc 1
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 164 | 7 (15)| 00:00:01 |
|* 1 | VIEW | | 4 | 164 | 7 (15)| 00:00:01 |
| 2 | WINDOW SORT | | 4 | 124 | 7 (15)| 00:00:01 |
| 3 | VIEW | | 4 | 124 | 6 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| TAB1 | 2 | 56 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| TAB2 | 2 | 56 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SUMM"<>0)
Two full table scans and a SORT. Compare this to what traditionally people have used:
