The move command compacts the rows within Oracle blocks, resolves row chaining, and resets a table's High Water Mark
Both the move and shrink commands reset the high water mark of a table, but which command is more efficient? This article discusses re-organizing a table using the move and shrink commands, then compares how the rows are compacted within Oracle blocks and how row chaining is resolved.
Note: It is beyond the scope of this document to present a detailed discussion about the High Water Mark of a table and all of the different methodologies for resetting the High Water Mark of a table.
The following steps briefly describe various operations performed on the table, TEMP_JP, during the re-org of the table using the move and shrink commands. A few exceptions in the series of steps are marked distinctly(Move related commands in BLUE, Shrink related commands in GREEN).
01 – 03 Created table temp_jp and inserted 2500 rows into the table
04 Displayed the rows distribution in the table’s Oracle blocks.
Strangely, the number of rows inserted per Oracle block is not uniform.
05 Indexed my table temp_jp.
06 Verified the disk space usage for the table and index.
By default, Oracle allocates one extent for a table and two extents for an index.
07 Added a third column to temp_jp table to simulate row chaining.
08 Temp_jp table is analyzed.
09 Select the number of rows and number of chained rows from temp_jp table.
Almost all of the rows in the table temp_jp are chained.
10 Verified the disk space usage for the table and index after simulating row chaining.
The disk space usage for the table temp_jp increased by 10 fold.
11 Deleted all rows from the table, leaving one row per Oracle block within the table.
12 Displayed the spread of rows from temp_jp table after deleting records.
The number of rows present per Oracle block are uniform, since we deleted all of the rows from table, leaving one row per Oracle block within the table.
13 Temp_jp table is analyzed.
14 Select number of rows and chained rows from temp_jp table.
15 Verify the status of the index on temp_jp table. It’s VALID.
16 16-A
Performed move operation on temp_jp table.
16-BA
16-BB Performed shrink operation on temp_jp table.
17 17-A
The status of the index is unusable after temp_jp table’s move operation.
17-B The status of the index is valid after temp_jp table’s shrink operation.
18 Displayed the disk space usage for the temp_jp table and it’s index.
18-A
After the move operation, one extent was allocated to the table and two for the index.
18-B
After the shrink operation, the table and index are allocated one extent each of 8 Oracle blocks. The initial two extents for the index had come down to one.
19 Displayed the spread of rows within Oracle blocks of temp_jp table.
19-A
All five rows in temp_jp table are compacted into one Oracle block.
19-B
All five rows in temp_jp table are spread into three Oracle blocks.
20 20-AA
Table analyze operation failed, with the error ORA-01502
20-AB
Rebuilt the index on temp_jp table to validate it.
After the index rebuild operation, one Oracle extent was allocated to the index.
20-AC
After index rebuild the table is analyzed.
20-B
Table is analyzed.
21 Select the number of rows and row chaining of temp_jp table.
21-A
After move operation on temp_jp table, row chaining was resolved. 0 rows chained.
21-B
After shrink operation on temp_jp table, row chaining was not resolved. 2 rows chained.
22 Verify the status of the index on temp_jp table is valid.
TEST A
How move operation affects row chaining and distribution of data within Oracle blocks in a table:
01-A
drop table temp_jp;
02-A
create table temp_jp(col1 number(10),col2 varchar2(20)) tablespace users;
03-A
declare
begin
for i in 1..2500 loop
insert into temp_jp values(i,'RAMA');
end loop;
commit;
end;
/
04-A
select dbms_rowid.rowid_relative_fno(rowid) ,
dbms_rowid.rowid_block_number(rowid) ,
count(*)
from temp_jp
group by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ -------------------------- ----------
4 2764 526
4 2765 519
4 2766 417
4 2767 519
4 2768 519
5 rows selected.
05-A
create index temp_jp_idx on temp_jp(col1) tablespace users;
06-A
select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
--------------- ------------------ ---------- ---------- ----------
TEMP_JP TABLE .0625 8 1
TEMP_JP_IDX INDEX .125 16 2
07-A
alter table temp_jp add(col3 varchar2(256) default 'THIS IS TO TEST THE ROW CHAINING ISSUE
WITH MOVE COMMAND AND HOW THE DATA IS SPREAD BEFORE AND AFTER THE MOVE COMMAND IN EACH BLOCK OF THE TABLE');
08-A
analyze table temp_jp compute statistics;
09-A
select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEMP_JP 2500 2426
10-A
select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
--------------- ------------------ ---------- ---------- ----------
TEMP_JP TABLE .625 80 10
TEMP_JP_IDX INDEX .125 16 2
11-A
declare
begin
for c1 in (select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block,
max(rowid) max_rowid
from temp_jp group by DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) loop
for c2 in (select rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block
from temp_jp
where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=c1.block) loop
if ((c2.block = c1.block) and (c2.rowid <> c1.max_rowid)) then
delete from temp_jp where rowid = c2.rowid;
end if;
end loop;
end loop;
commit;
end;
/
12-A
select dbms_rowid.rowid_relative_fno(rowid) ,
dbms_rowid.rowid_block_number(rowid) ,
count(*)
from temp_jp
group by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ------------------------------------ ----------
4 2764 1
4 2765 1
4 2766 1
4 2767 1
4 2768 1
13-A
analyze table temp_jp compute statistics;
14-B
select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEMP_JP 5 5
15-A
select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME STATUS
------------------------------ --------
TEMP_JP_IDX VALID
16-A
alter table temp_jp move tablespace users;
17-A
select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME STATUS
------------------------------ --------
TEMP_JP_IDX UNUSABLE
18-A
select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
--------------- ------------------ ---------- ---------- ----------
TEMP_JP TABLE .0625 8 1
TEMP_JP_IDX INDEX .125 16 2
19-A
select dbms_rowid.rowid_relative_fno(rowid) ,
dbms_rowid.rowid_block_number(rowid) ,
count(*)
from temp_jp
group by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ------------------------------------ ----------
4 2908 5
20-AA
analyze table temp_jp compute statistics;
analyze table temp_jp compute statistics
*
ERROR at line 1:
ORA-01502: index 'GZBGQT.TEMP_JP_IDX' or partition of such index is in unusable state
20-AB
alter index TEMP_JP_IDX rebuild online;
20-AC
analyze table temp_jp compute statistics;
21-A
select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEMP_JP 5 0
22-A
select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME STATUS
------------------------------ --------
TEMP_JP_IDX VALID
TEST B
How the shrink operation affects row chaining and distribution of data within Oracle blocks in a table:
01-B
drop table temp_jp;
02-B
create table temp_jp(col1 number(10),col2 varchar2(20)) tablespace users;
03-B
declare
begin
for i in 1..2500 loop
insert into temp_jp values(i,'RAMA');
end loop;
commit;
end;
/
04-B
select dbms_rowid.rowid_relative_fno(rowid) ,
dbms_rowid.rowid_block_number(rowid) ,
count(*)
from temp_jp
group by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ------------------------ ----------
4 1908 526
4 1909 519
4 1910 417
4 1911 519
4 1912 519
5 rows selected.
05-B
create index temp_jp_idx on temp_jp(col1) tablespace users;
06-B
select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
--------------- ------------------ ---------- ---------- ----------
TEMP_JP TABLE .0625 8 1
TEMP_JP_IDX INDEX .125 16 2
07-B
alter table temp_jp add(col3 varchar2(256) default 'THIS IS TO TEST THE ROW CHAINING ISSUE WITH MOVE
COMMAND AND HOW THE DATA IS SPREAD BEFORE AND AFTER THE MOVE COMMAND IN EACH
BLOCK OF THE TABLE');
08-B
analyze table temp_jp compute statistics;
09-B
select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEMP_JP 2500 2426
10-B
select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
--------------- ------------------ ---------- ---------- ----------
TEMP_JP TABLE .625 80 10
TEMP_JP_IDX INDEX .125 16 2
11-B
declare
begin
for c1 in (select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block,
max(rowid) max_rowid
from temp_jp group by DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) loop
for c2 in (select rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block
from temp_jp
where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=c1.block) loop
if ((c2.block = c1.block) and (c2.rowid <> c1.max_rowid)) then
delete from temp_jp where rowid = c2.rowid;
end if;
end loop;
end loop;
commit;
end;
/
12-B
select dbms_rowid.rowid_relative_fno(rowid) ,
dbms_rowid.rowid_block_number(rowid) ,
count(*)
from temp_jp
group by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ -------------------------- ----------
4 1908 1
4 1909 1
4 1910 1
4 1911 1
4 1912 1
5 rows selected.
13-B
13-B
analyze table temp_jp compute statistics;
14-A
select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEMP_JP 5 5
15-B
select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME STATUS
------------------------------ --------
TEMP_JP_IDX VALID
16-BA
alter table temp_jp enable row movement;
16-BB
alter table temp_jp shrink space cascade;
17-B
select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME STATUS
------------------------------ --------
TEMP_JP_IDX VALID
18-B
select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
--------------- ------------------ ---------- ---------- ----------
TEMP_JP TABLE .0625 8 1
TEMP_JP_IDX INDEX .0625 8 1
19-B
select dbms_rowid.rowid_relative_fno(rowid) ,
dbms_rowid.rowid_block_number(rowid) ,
count(*)
from temp_jp
group by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ -------------------------- ----------
4 1908 3
4 1909 1
4 1910 1
20-B
analyze table temp_jp compute statistics;
21-B
select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEMP_JP 5 2
22-B
select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME STATUS
------------------------------ --------
TEMP_JP_IDX VALID
All of the rows are compacted into one oracle block, after the move operation on temp_jp table. Row chaining is completely resolved in temp_jp table.
The shrink operation could not completely resolve row chaining in the table. The remaining 5 rows in the table are spread across three oracle blocks in the table.
After all the foregoing, in a read intensive application, where milliseconds in performance count, I would vote for the move command. I am prepared to go the extra mile, rebuilding the unusable indexes and provisioning extra disk space for the objects’ move operation while resetting high water mark
Oracle 10 DBA Interview questions and answers
1. Is the following SQL statement syntactically correct? If not, please rewrite it correctly.
SELECT col1 FROM tableA WHERE NOT IN (SELECT col1 FROM tableB);
Ans. SQL is incorrect.
Correct SQL : SELECT col1 FROM tableA WHERE col1 NOT IN (SELECT col1 FROM tableB);
2. What is a more efficient way to write this query, to archive the same set?
Ans: SELECT col1 from tableA minus SELECT col1 from tableB
3.How would you determine that the new query is more efficient than the original query?
Ans: Run explain plan on both query and see the result .
4.How can we find the location of the database trace files from within the data dictionary?
Ans: Generally trace file on the database server machine is located in one of two locations:
1. If you are using a dedicated server connection, the trace file will be generated in the directory specified by
the USER_DUMP_DEST parameter.
2.If you are using a shared server connection, the trace file will be generated in the directory specified by the
BACKGROUND_DUMP_DEST parameter.
you can run sqlplus>SHOW PARAMETER DUMP_DEST
or
select name, value
from v$parameter
where name like '%dump_dest%'
5. What is the correct syntax for a UNIX endless WHILE loop?
while :
do
commands
done
6. Write the SQL statement that will return the name and size of the largest datafile in the database.
SQL> select name,bytes from v$datafile where bytes=(select max(bytes) from v$datafile);
7. What are the proper steps to changing the Oracle database block size?
cold backup all data files and backup controlfile to trace, recreate your database
with the new block size using the backup control file, and restore. It may be easier
with rman. You can not change datbase block size on fly.
8. Using awk, write a script to print the 3rd field of every line.
Ans:
awk '{print }'
awk '{print $3}
awk '{print $3}
9.Under what conditions, is a nested loop better than a merge join?
Ans:
Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving
condition.
It is important to have an index on column of inner join table as this table is probed every time for a new value
from outer table.
Optimizer may not use nested loop in case:
1. No of rows of both the table is quite high
2. Inner query always results in same set of records
3. The access path of inner table is independent of data coming from outer table.
merge join is used to join two independent data sources. They perform better than nested loop when the volume of
data is big in tables
but not as good as hash joins in general.
10.Which database views would you use to ascertain the number of commits a user's session has performed?
Joining V$SESSTAT ,V$STATNAME
select * from V$SESSTAT a ,V$STATNAME b where b.CLASS=a.STATISTIC# and b.NAME='user commits' and a.sid=
11.What does the #!bin/ksh at the beginning of a shell script do? Why should it be there?
Ans: On the first line of an interpreter script, the "#!", is the name of a program which should be used to
interpret the contents of the file.
For instance, if the first line contains "#! /bin/ksh", then the contents of the file are executed as a korn shell
script.
12.What command is used to find the status of Oracle 10g Clusterware (CRS) and the various components it manages
(ONS, VIP, listener, instances, etc.)?
Ans:
$ocrcheck
13.Describe a scenario in which a vendor clusterware is required, in addition to the Oracle 10g Clusterware.
If you choose external redundancy for the OCR and voting disk, then to enable redundancy, the disk subsystem must be configurable for RAID mirroring/vendor clusterware. Otherwise, your system may be vulnerable because the OCR and voting disk are single points of failure.
14.How would you find the interconnect IP address from any node within an Oracle 10g RAC configuration?
using oifcfg command.
se the oifcfg -help command to display online help for OIFCFG. The elements of OIFCFG commands, some of which are
optional depending on the command, are:
*nodename—Name of the Oracle Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the interface is configured in the system
*subnet—Subnet address of the interface
*if_type—Type of interface: public or cluster_interconnect
You can use OIFCFG to list the interface names and the subnets of all of the interfaces available on the local node
by executing the iflist keyword as shown in this example:
oifcfg iflist
hme0 139.185.141.0
qfe0 204.152.65.16
You can also retrieve specific OIFCFG information with a getif command using the following syntax:
oifcfg getif [ [-global | -node nodename] [-if if_name[/subnet]] [-type if_type] ]
To store a new interface use the setif keyword. For example, to store the interface hme0, with the subnet
139.185.141.0, as a global interface (to be used as an interconnect for all of the RAC instances in your cluster),
you would use the command:
oifcfg setif -global hme0/139.185.141.0:cluster_interconnect
For a cluster interconnect that exists between only two nodes, for example rac1 and rac2, you could create the cms0
interface with the following commands, assuming 139.185.142.0 is the subnet addresses for the interconnect on rac1
and rac2 respectively:
oifcfg setif -global cms0/139.185.142.0:cluster_interconnect
Use the OIFCFG delif command to delete the stored configuration for global or node-specific interfaces. A specific
node-specific or global interface can be deleted by supplying the interface name, with an optional subnet, on the
command line. Without the -node or -global options, the delif keyword deletes either the given interface or all of
the global and node-specific interfaces on all of the nodes in the cluster. For example, the following command
deletes the global interface named qfe0 for the subnet 204.152.65.0:
oifcfg delif -global qfe0/204.152.65.0
On the other hand, the next command deletes all of the global interfaces stored with OIFCFG:
oifcfg delif -global
15.What is the Purpose of the voting disk in Oracle 10g Clusterware?
Voting disk record node membership information. Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster.
16.What is the purpose of the OCR in Oracle 10g Clusterware?
Ans: Oracle Cluster Registry (OCR) is a component in 10g RAC used to store the cluster configuration information. It is a shared disk component, typically located in a shared raw volume that must be accessible to all nodes in the cluster.
The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.
17. In Oracle Streams archived log downstream capture, which database view can be used to determine which archived
logs are no longer needed by the capture process?
Ans: V$ARCHIVE_DEST_STATUS
SELECT col1 FROM tableA WHERE NOT IN (SELECT col1 FROM tableB);
Ans. SQL is incorrect.
Correct SQL : SELECT col1 FROM tableA WHERE col1 NOT IN (SELECT col1 FROM tableB);
2. What is a more efficient way to write this query, to archive the same set?
Ans: SELECT col1 from tableA minus SELECT col1 from tableB
3.How would you determine that the new query is more efficient than the original query?
Ans: Run explain plan on both query and see the result .
4.How can we find the location of the database trace files from within the data dictionary?
Ans: Generally trace file on the database server machine is located in one of two locations:
1. If you are using a dedicated server connection, the trace file will be generated in the directory specified by
the USER_DUMP_DEST parameter.
2.If you are using a shared server connection, the trace file will be generated in the directory specified by the
BACKGROUND_DUMP_DEST parameter.
you can run sqlplus>SHOW PARAMETER DUMP_DEST
or
select name, value
from v$parameter
where name like '%dump_dest%'
5. What is the correct syntax for a UNIX endless WHILE loop?
while :
do
commands
done
6. Write the SQL statement that will return the name and size of the largest datafile in the database.
SQL> select name,bytes from v$datafile where bytes=(select max(bytes) from v$datafile);
7. What are the proper steps to changing the Oracle database block size?
cold backup all data files and backup controlfile to trace, recreate your database
with the new block size using the backup control file, and restore. It may be easier
with rman. You can not change datbase block size on fly.
8. Using awk, write a script to print the 3rd field of every line.
Ans:
awk
awk '{print $3}
awk '{print $3}
9.Under what conditions, is a nested loop better than a merge join?
Ans:
Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving
condition.
It is important to have an index on column of inner join table as this table is probed every time for a new value
from outer table.
Optimizer may not use nested loop in case:
1. No of rows of both the table is quite high
2. Inner query always results in same set of records
3. The access path of inner table is independent of data coming from outer table.
merge join is used to join two independent data sources. They perform better than nested loop when the volume of
data is big in tables
but not as good as hash joins in general.
10.Which database views would you use to ascertain the number of commits a user's session has performed?
Joining V$SESSTAT ,V$STATNAME
select * from V$SESSTAT a ,V$STATNAME b where b.CLASS=a.STATISTIC# and b.NAME='user commits' and a.sid=
11.What does the #!bin/ksh at the beginning of a shell script do? Why should it be there?
Ans: On the first line of an interpreter script, the "#!", is the name of a program which should be used to
interpret the contents of the file.
For instance, if the first line contains "#! /bin/ksh", then the contents of the file are executed as a korn shell
script.
12.What command is used to find the status of Oracle 10g Clusterware (CRS) and the various components it manages
(ONS, VIP, listener, instances, etc.)?
Ans:
$ocrcheck
13.Describe a scenario in which a vendor clusterware is required, in addition to the Oracle 10g Clusterware.
If you choose external redundancy for the OCR and voting disk, then to enable redundancy, the disk subsystem must be configurable for RAID mirroring/vendor clusterware. Otherwise, your system may be vulnerable because the OCR and voting disk are single points of failure.
14.How would you find the interconnect IP address from any node within an Oracle 10g RAC configuration?
using oifcfg command.
se the oifcfg -help command to display online help for OIFCFG. The elements of OIFCFG commands, some of which are
optional depending on the command, are:
*nodename—Name of the Oracle Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the interface is configured in the system
*subnet—Subnet address of the interface
*if_type—Type of interface: public or cluster_interconnect
You can use OIFCFG to list the interface names and the subnets of all of the interfaces available on the local node
by executing the iflist keyword as shown in this example:
oifcfg iflist
hme0 139.185.141.0
qfe0 204.152.65.16
You can also retrieve specific OIFCFG information with a getif command using the following syntax:
oifcfg getif [ [-global | -node nodename] [-if if_name[/subnet]] [-type if_type] ]
To store a new interface use the setif keyword. For example, to store the interface hme0, with the subnet
139.185.141.0, as a global interface (to be used as an interconnect for all of the RAC instances in your cluster),
you would use the command:
oifcfg setif -global hme0/139.185.141.0:cluster_interconnect
For a cluster interconnect that exists between only two nodes, for example rac1 and rac2, you could create the cms0
interface with the following commands, assuming 139.185.142.0 is the subnet addresses for the interconnect on rac1
and rac2 respectively:
oifcfg setif -global cms0/139.185.142.0:cluster_interconnect
Use the OIFCFG delif command to delete the stored configuration for global or node-specific interfaces. A specific
node-specific or global interface can be deleted by supplying the interface name, with an optional subnet, on the
command line. Without the -node or -global options, the delif keyword deletes either the given interface or all of
the global and node-specific interfaces on all of the nodes in the cluster. For example, the following command
deletes the global interface named qfe0 for the subnet 204.152.65.0:
oifcfg delif -global qfe0/204.152.65.0
On the other hand, the next command deletes all of the global interfaces stored with OIFCFG:
oifcfg delif -global
15.What is the Purpose of the voting disk in Oracle 10g Clusterware?
Voting disk record node membership information. Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster.
16.What is the purpose of the OCR in Oracle 10g Clusterware?
Ans: Oracle Cluster Registry (OCR) is a component in 10g RAC used to store the cluster configuration information. It is a shared disk component, typically located in a shared raw volume that must be accessible to all nodes in the cluster.
The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.
17. In Oracle Streams archived log downstream capture, which database view can be used to determine which archived
logs are no longer needed by the capture process?
Ans: V$ARCHIVE_DEST_STATUS
Subscribe to:
Post Comments (Atom)
Blog Archive
-
▼
2008
(277)
-
▼
September
(261)
- Setting oracle database in archive mode from non-a...
- Oracle Version Information - How to CheckYour Curr...
- Database and Instance Shutdown
- Database and Instance Shutdown
- What Happens When You Open a Database
- How to Identify Your Oracle Database Software Release
- What is block change tracking in RMAN Oracle 10g
- What is Incremental Merge in RMAN 10g?
- Moving from DBMS_JOB to DBMS_SCHEDULER
- Moving from DBMS_JOB to DBMS_SCHEDULER
- Moving from DBMS_JOB to DBMS_SCHEDULER
- Moving from DBMS_JOB to DBMS_SCHEDULER
- Oracle Database 10g Scheduler - Associating Jobs w...
- Oracle Database 10g Scheduler - Creating Jobs With...
- Oracle Database 10g Scheduler
- DBMS_JOB Package
- Data Guard Database Synchronization Options
- Data Guard Role Management
- Data Guard Broker
- Data Guard Protection Modes
- Redo Apply and SQL Apply
- Data Guard Benefits
- Creating a Nested Materialized View: Example
- Creating a Fast Refreshable Materialized View: Exa...
- Automatic Refresh Times for Materialized Views: Ex...
- Periodic Refresh of Materialized Views: Example
- Creating Rowid Materialized Views: Example
- Creating Primary Key Materialized Views: Example
- Creating Subquery Materialized Views: Example
- Creating Prebuilt Materialized Views: Example
- Creating Materialized Join Views: Example
- Creating Materialized Aggregate Views: Example
- Privilages to create a refresh-on-commit materiali...
- Privilages to create a materialized view in anothe...
- What Privilages required to create a materialized ...
- What is materialized view?
- What is Optimizer Plan Stability?
- DBMS_ADVISOR Package
- What is Bigfile tablespace? why do we create it? w...
- Reverting a Table to its Previous Statistics
- Manipulating Statistics Using DBMS_STATS
- Copying Statistics Using DBMS_STATS
- Gathering statistics with DBMS_STAT Package
- Handling Errors During Backups: Example
- Backup Validation with RMAN
- Detection of Logical Block Corruption
- Detecting Physical and Logical Block Corruption
- Tests and Integrity Checks for Backups?
- When RMAN Performs Control File Autobackups
- How RMAN Performs Control File Autobackups?
- what command do you execute to ensure that the con...
- Which background processes coordinates the rebalan...
- Which background processes coordinates the rebalan...
- What is oracle ASM in Oracle 10g? How do we create...
- Load Balancing In Oracle 10g Real Application Clus...
- Oracle 10gR2 load balancing - Server side load bal...
- Oracle 10gR2 load balancing- Client-Side Connectio...
- Oracle 10gR2 load balancing - Part2 - Client-Side ...
- Oracle 10gR2 load balancing - Part1
- Oracle database tuning without any tools - using p...
- Oracle database tuning without any tools - using p...
- Oracle database tuning without any tools - using p...
- Oracle database tuning without any tools - using p...
- Quick DBA Test - Oracle 10g
- what are the major new featurs in Oracle 10g data ...
- Flashback version query in oracle 10g
- Optimizing Performance of Direct Path Loads
- Data Conversion During Direct Path Loads
- Data Loading Methods - SQL Loader
- How Does Data Pump Access Data?
- Oracle data pump 10g impdp help
- Oracle data pump 10g expdp help
- Exporting and Importing External tables using Data...
- Oracle 10g Data Pump APIs
- Difference between old exp/imp and new data pump e...
- Oracle Data pump 10g getting started
- Oracle Data Pump in Oracle Database 10g
- What is FLASHBACK TABLE ? Explain with examples?
- What is Oracle Secure Backup (OSB)?
- What is auxiliary instance? when it is created?
- Automatic Instance Creation for RMAN TSPITR
- Oracle 10 RMAN Views
- How to setup RMAN in Oracle 9i
- What is block pinging and why is it so bad?
- Can I test if a database is running in RAC mode?
- How does one stop and start RAC instances?
- How does one convert a single instance database to...
- How many OCR and voting disks should one have?
- Do you need special hardware to run RAC?
- Can any application be deployed on RAC?
- Oracle Real Application Clusters (RAC) FAQ - previ...
- Backup Scripts When Few Data Blocks Change
- Improving Incremental Backup Performance: Change T...
- Incrementally Updated Backups: A One Week Example
- Basic Incremental Backup Strategy
- Cumulative Incremental Backups
- Differential Incremental Backups
- Level 0 and Level 1 Incremental Backups
- RMAN Incremental Backups
- Using Compressed Backupsets
-
▼
September
(261)
Posts
No comments:
Post a Comment