Size your Undo tablespace
What should be the size of UNDO tablespace? Generally this question is vaguely answered saying that sizing comes with experience (of DBA) or looking at load on server or sometimes even by the number of ORA-1555 or out of space errors.
This paper is to help DBA’s in calculating the size of UNDO tablespace by using a simple formula.
While designing an application, generally it is tough to know about the number of transactions and subsequently number of rows changed per second.
So I suggest having a “big undo tablespace” to start with and based on load, after doing some calculations and resize your UNDO tablespace.
In my case one of the applications was going to production (live), and I had no idea that how many transactions will happen against this database. All what I was told that there will be optimum (transactional) activity on this database. The word “optimum” itself is vague.
So I started with UNDO tablespace with size of 2GB and datafiles with autoextend “on” .
Note:
In production, you must be very careful in using this (autoextend on) as the space may grow to inifinity very fast. So my advice is either dont use this option, or use with "maxsize" or continuously monitor space (which is tough).
I month later, I noticed the activity from V$undostat.
Here is the step by step approach:
Step 1: Longest running query.
SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
1793
This gives you ideal value for UNDO_RETENTION. To be on the safer size you should add few more seconds to get the right value. So in my case, the size of undo retention should be say 2000 secs.
Step 2: Size of UNDO tablespace.
Size of UNDO needed = UNDO_RETENTION x [UNDO block Generation per sec x DB_BLOCK_SIZE] + Overhead(30xDB_BLOCK_SIZE)
Out of these we know UNDO_RETENTION and DB_BLOCK_SIZE
All we need is to find out “UNDO Blocks per second”
Which can be easily fetched from v$undostat
SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 24*60*60) "UPS"
2 FROM v$undostat;
UPS
------------------------------
8.11985583
V$undostat stores data for every 10 mins and begin/end times are start/end time of those intervals. We multiplied it with 24*60*60 because the difference between two dates will be in days and to get to seconds, we need it to multiply with 24hrs*60mins*60secs
So now we have all the values needed.
Undo size needed = [8.12 x 2000 x 8192] + [30 x 8192] = 133283840 bytes = 127.11 MB
Thursday, September 2, 2010
Tuesday, August 31, 2010
Oracle Data Pump
Data Pump: Not Just for Data Moves
Data Pump – introduced in Oracle Database 10g Release 1 – has been described in many different ways: from “Export/Import from Steroids” to the “Ultimate Data Mover”. Perhaps the use of superlatives creates a perception that this is only for data movements. Numerous presentations, articles, books and even Oracle manuals permeate that perception.
However, Data Pump is much more than just moving data. While that was probably the original and primary intent and it still remains the principal usage, the other subtle but significant capabilities of Data Pump remains under represented if not undiscovered. In this article you will learn about those lesser known capabilities and how to use them to achieve your strategic and tactical objectives. Put into good use Data Pump can ease a lot of daily and longer term non-data movement related tasks and provide a viable platform for alternate use.
This article assumes that the attendee is familiar with the Data Pump tool. This is not a introduction to the tool itself.
A Data Pump Primer
Even though the audience is assumed to be fully conversant with Data Pump, a quick primer will cement that understanding and pave way for a better understanding of the material to come. If you are familiar with the basics of the tool, you may want to skip this section in its entirety.
Data Pump is an evolution of the original export and import tools; and in fact, it’s also named Data Pump Export and Import. The purpose is similar – to export data out of the Oracle database and import back. The exported data is put in a dumpfile, which is portable across Operating Systems and even across Oracle versions (10g Release 1 and above). This export works by selecting the data from the tables using the SQL interface, similar to the original export; but at a much faster rate. My tests have shown Data Pump Export to be about 10 times faster than original export when conditions are right – with high degrees of parallelism, multiple spindles and so on. Similarly Data Pump Import is also faster than the original import, albeit at a lower scale.
The dumpfile produced can only be ready by Data Pump Import, not the original import. The same holds true for the reverse process as well, i.e. the dumpfile produced by the original export is not readable by DP Import. A major difference in the two tools is the location where the dumpfiles are produced. In the original export, the dumpfiles are produced in the same server the tool is called; so if you issue the exp command at a client machine, such as your laptop, the dumpfile is produced there. In contrast, the DP Export always produces the dumpfile on the database server regardless where it is called. You can specify a location by creating a directory object and specifying that in the command line.
Finally, for the sake of completeness, the commands are “expdp” for DP Export and “impdp” for DP Import.
Now that the introduction part is complete, let’s jump to the main topic – the usefulness of this tool beyond the data movement.
Regulatory Compliance
Would you want to store the source codes of the database codes such as stored procedures and DDLs of the objects? It would have been nice; but probably not strictly needed, just a fe years ago. But thanks to the myriads of laws and regulations covering the corporate America today, this has now become a necessity; not just a nice to have. For instance, laws like Sarbanes-Oxley (SOX) actually require that you have to have a system where you can track the changes to object definitions – commonly referred to as “metadata” in Oracle technical parlance. Please note, the word metadata has many different meanings in technical discussions. For instance, the original meaning of the term is “data about the data”, i.e. the meaning of the data elements such as columns and tables. For example, the metadata on the column STATUS in the table EMP could be: it refers to the status of the employee in the company at this time and it can have values such as ACTIVE, INACTIVE, SUSPENDED, etc. This description is commonly referred to as “metadata”. However, in Oracle-speak, we usually refer to the definition of the objects, i.e. the DDL statement to create the table, the source code of the procedures and packages as metadata. I have used the latter definition of the term.
Most regulations and internal controls in organizations require that the source code be stores in a repository which can be controlled and tracked. If the definitions changed, the system should be able to track it and report changes. The historical records of the metadata should be persevered. How do you accomplish this? One way to achieve this is constructing the sources and DDL from the data dictionary in regular intervals. For tables, you would select from DBA_TAB_COLUMNS (for columns), DBA_CONSTRAINTS, and DBA_CONS_COLUMNS (for constraints), DBA_TAB_PRIVS (for privileges granted to the table) and many others. For source code, you will need to select from DBA_SOURCES; te view DBA_TRIGGERS for triggers, DBA_VIEWS for view definitions and so on. The other option is extracting the DDL from the dictionary using DBMS_METADATA.GET_DDL packaged function. The solutions are technically possible; but may seem infeasible considering the effort. So, to address that deficiency, several tool manufactures have jumped into the fray. Needless to say, the tools do their job; but at a cost.
Enter Data Pump, to accomplish the same objective. Let’s see how.
Metadata Management
One of the least used parameters of Data Pump Export is CONTENT, which controls what is exported. It accepts three parameters:
DATA_ONLY – this option exports the data from tables.
METADATA_ONLY – this option does not export the data; merely exports the metadata of the database objects.
ALL –this is the default; both the metadata and the data (where applicable) are exported.
This parameter is the one you can use to create a baseline repository of the database sources and DDLs very quickly. All you have to do is to use:
expdp directory=dpump_dir dumpfile=metadata.dmp content=metadata_only
This creates the file metadata.dmp in the directory specified by the directlory object dpump_dir containing all the DDLs and source codes of the database objects. The file does not contain any data. This is your baseline repository of the source. If you ever need to reinstate the source, simply drop it from the database and import back the source from this baseline:
impdp directory=dpump_dir dumpfile=metadata.dmp
This will import the objects that are not present in the database now and skip the ones that are there.
In many cases, your database as a whole may not be subject to the baseline repository building; you may want to store objects of specific schemas in the repository; not all. For instance you may want to include the ACCMAIN and HRMAIN schemas but not the SYSTEM schema. The above command would be slightly modified in that case:
expdp directory=dpump_dir dumpfile=metadata.dmp content=metadata_only schemas=ACCMAIN, HRMAIN
By default, all the database objects in the schema are exported – tables, views, triggers, procedures, and so on. What if you want to store each object separately in its individual files? For instance, you may want to store the stored code such as triggers, procedures, etc. on a file named metadata_code.dmp and tables, views, etc. on a file named metadata_tabs.dmp. In this case, you can use the INCLUDE parameter to include only certain objects or types of objects in the export file; not all. The above command will then turn to:
expdp directory=dpump_dir dumpfile=metadata_code.dmp content=metadata_only schemas=ACCMAIN, HRMAIN include=PROCEDURE,FUNCTION,PACKAGE,TRIGGER
Here the dumpfile has only the code related metadata, not all.
You can also specify a specific procedure or finction to export. Suppose you want to keep a very crucial function – GET_RATE and a table – RATES, in a separate file – metadata_imp.dmp, you can use:
expdp directory=dump_dir dumpfile=metadata_imp.dmp schemas=ACCMAIN include=FUNCTION:\"=\'GET_RATE\'\",TABLE:\"=\'RATES\'\"
Note, I have added the backslash (“\”) before the double quotes (“) and the single quotes (‘), just so that the unix shell will not interpret the characters as commands. If you use Windows, you will need to remove the back slashes. Alternatively, a better way is to use a parameter file where you can list all the parameter, one per line and call the parameter using the PARFILE parameter. In this case, just create a file called exp.par with the following as the contents:
directory=dump_dir
dumpfile=metadata_imp.dmp
schemas=accman
include=FUNCTION:"='GET_RATE'"
include=TABLE:"='RATES'"
Then you can call the Data Pump Export as
$ expdp parfile=exp.par
In the parfile, you can see that I have placed two lines with the INCLUDE parameter; that’s allowed. I can place as many INCLUDE parameters as I need. The include parameter takes the values as
:""
For instance, to include the functions FN1 and FN2, you would use:
include=function:"in ('FN1','FN2')"
or
include=function:"like 'FN%'"
Of course, you can also use this to get all but some:
include=function:"not in ('FN1','FN2')"
However, a more convenient alternative may be to use the EXCLUDE option. This is the inverse of INCLUDE and instructs the expdp command to exclude the objects. For example, you want to export the meta data for all objects excluding the functions FN1, FN2 and procedures PROC1 and PROC2. You will use:
exclude=function:"in ('FN1','FN2')"
exclude=procedure:"in ('PROC1','PROC2')"
To include all objects except all the tables in the export dump:
exclude=table
The syntax is the same as the include option. Note a very important difference in the usage of the parameters. Include is used to include a specific objects of a type; exclude is used to exclude a specific object of a type. They are not necessarily interchangeable. Consider these two options:
exclude=function:"in ('FN1','FN2')"
and
include=function:"not in ('FN1','FN2')"
They are not the same. The first option (the exclude one) exports all the objects – tables, views, triggers, etc. and leaves out just two functions – FN1 and FN2. The second option (the include one), on the other hand, only exports all the functions except FN1 and FN2. Only the functions are exported, not tables, views, etc. They are fundamentally different in their behavior. You have to consider this carefully when you construct your parameter file.
Creating an SQL File
Earlier you learned how to create a baseline and also selective exports of the database objects for safe keeping. However, the dumpfiles are not human readable, i.e. you can’t just glean over the files and get the metadata. You need another step – to extract the SQL out of the dumpfile. You can do that using the parameter SQLFILE in the Data Pump Import. Here is an example:
$ impdp directory=dump_dir dumpfile=metadata_full.dmp sqlfile=metadata_full.sql
This operation does not actually import the objects into the database; but creates an SQL script called metadata_full.sql containing all the objects in the dumpfile. Here is an excerpt from the file:
-- CONNECT ARUP
-- new object type path is: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'+NDSDATA7/undotbs101.dbf' SIZE 17179869184,
'+NDSDATA8/undotbs102.dbf' SIZE 17179869184,
'+NDSDATA9/undotbs103.dbf' SIZE 17179869184
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TABLESPACE "SYSAUX" DATAFILE
'+NDSDATA1/sysaux01.dbf' SIZE 4294967296,
'+NDSDATA1/sysaux02.dbf' SIZE 4194304000
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE
'+NDSDATA1/temp1_01.dbf' SIZE 1073741824
......... and so on ........
You can also apply the filters to extract the SQL statements for specific objects as well. For instance, you can extract only the procedures, functions and packages; nothing else by using the INCLUDE parameter
include=procedure, package
You can include specific objects as well; not just specific types.
include=procedure:"='PROC1'"
Here is the file that is eventually produced:
-- CONNECT SYS
-- new object type path is: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
-- CONNECT ARUP
CREATE PROCEDURE "ARUP"."PROC1"
as
begin
dbms_output.put_line ('Some text');
end;
/
-- new object type path is: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ALTER PROCEDURE "ARUP"."PROC1"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED
PLSQL_DEBUG= FALSE
REUSE SETTINGS TIMESTAMP '2006-08-11 13:27:55'
As with the export, you can exclude some objects as well:
exclude=procedure:"='PROC1'"
Building a Repository
Now that you learned how to extract the database objects into SQL files, you can build a system or a rudimentary tool to make the process resemble more like, well, a process. This can be effected somehow using the a shell script and creating the dumpfile and the SQL file named based on the date of creation. Here is how the shell script would look like:
DATEVAR=`date +"%m%d%y"`
DMPFILE=metadata_${DATEVAR}.dmp
SQLFILE=metadata_${DATEVAR}.sql
echo "arup"
expdp arup directory=tmp_dir dumpfile=${DMPFILE}
echo "arup"
impdp arup directory=tmp_dir dumpfile=${DMPFILE} sqlfile=${SQLFILE}
This script will create the file in the format metadata_.dmp where is the month, day and year string. After the creation of the SQL file, you can remove the dumpfile. I, however, prefer to keep it since it cannot be created from the SQL file. You can selectively extract anything from the dumpfile, apply filters; but not so with the SQL file.
As a part of the maintenance, you may want to move the created files periodically. Here is a simple command that moves the files to a name.old after 30 days.
find . -name "*.dmp" -ctime +30 -exec mv {} {}.old\;
Or, you can just remove it
find . -name "*.dmp" -ctime +30 -exec rm {}\;
Using this, you can build a full version control and repository system in just hours; not months and without using any tool from outside of what you already has. This is one of the most powerful yet underutilized capabilities of the Data Pump.
Create a User Like …
Here is a request you may have received several times – create a user just like JOE. The requestor doesn’t provide any other details – no tablespace quotas, no grants and so on. All the requestor knows that JOE userid works; so another one like JOE will work as well. How do you take care of the request?
Of course, there are tools that can do this quickly; but you may not have them handy. Your only option at that point is to painstakingly create an SQL file caturing the user’s properties for data dictionary, all the objects and so on. While it’s possible, it’s tedious, prone to errors and sunstantilly time consuming.
Enter Data Pump to the rescue. You can create the user in just two commands:
$ expdp schemas=JOE content=metadata_only
$ impdp remap_schema=JOE:NEWUSER
The first command (export) is necessary only if you don’t have a full export dump or baseline now. If you have been using DP to take your metadata baselines, you don’t need to perform that step. The second command (the import) imports the metadata about the user JOE, but transforms it so that instead of JOE, the user NEWUSER is created. All grants, privileges, quotas, objects, etc. of JOE now goes to NEWUSER. In just a matter of minutes you created a user like another accurately.
If you have been using the repository as several versions with timestamps as shown in the previous section, you can even use that to create a user like JOE as of a certain date. For instance, JOE has DBA privileges now; but didn’t have them yesterday. Could we have NEWUSER sans DBA role? Sure; just use the dumpfile as of that day.
Create Tablespaces in Test as in Production
This is another of the challenges you may face – you have been asked to create a small test database, of the fraction of the size of the main database; but the structural elements must be identical. i.e. the tablespaces should be same, laid out in the same manner, across the same mount points and so on. How do you quickly do it?
Sure, you could write a PL/SQL loop to get all the datafile properties from the data dictionary (dba_data_files and dba_tablespaces) and construct the SQL script to create the tablespaces. It will work; but will take a significant amount of time, and, needless to say, is highly susceptible to errors.
Again, Data Pump comes to rescue. All you have to do is import the dumpfile and use the INCLUDE option.
$ expdp content=metadata_only
$ impdp include=tablespace
Like the previous case, the first command (expdp) is not necessary if you already have a dump. Instead of creating the tablespace directly, you may also want to see the script by using the SQLFILE option:
$ impdp include=tablespace sqlfile=c.sql
This will create a file called c.sql, which will have the tablespace creation scripts. You ca edit them, if needed and run them from SQL*Plus.
A slight variation of the above may be more practical. In many cases, the test database may not have or need the size of the production database tablespaces. While creating the tablespaces, you may want to reduce the size of the datafiles by a certain percentage, say 90%. One option is to create a SQL file, edit it and manually change the sizes of all datafiles to 10% of the production size. While it’s doable, it’s not elegant as it adds time and risk of error. You can achieve that in a simple option – TRANSFORM.
$ impdp include=tablespace sqlfile=c.sql transform=pctspace:10
The parameter transform=pctspace:10 instructs the IMPDP job to change the size of the datafiles to 10% of the original size. Here is the excerpt from the SQL file created without this parameter:
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u01/undotbs101.dbf' SIZE 17179869184,
And here is the excerpt when the parameter was applied:
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u01/undotbs101.dbf' SIZE 1717986918,
As you can see, the size with the parameter (1,717,986,918) is about 10% of the original size (17,179,869,184). You will learn more about the TRANSFORM parameter later in this article.
Data File Name Change
You are moving some tables from another database and the tablespace in which these tables exist is also new in the target database. Importing those tables will create the tablespaces in the target database too; but here is a small problem – the filesystems are different. For example, the datafile for tablespace was “/u01/data1.dbf” but the target database does not have a mount point called /u01; instead it has “/u02”. Ordinarily you would have to create the tablespace manually and then import the table into the tablespace.
Data Pump eliminates the extra step in one elegant motion. All you have to do is use the REMAP_DATAFILE option as follows:
$ impdp remap_datafile=‘/u01/data1.dbf':'/u02/data1.dbf'
This creates the same datafile as /u02/data1.dbf. you can use this option to recreate the test data even across different platforms. For instance, the following option in the option converts datafiles from a unix to Windows format.
remap_datafile='/u01/data1.dbf':'c:\oradata\data1.dbf'
Segment Transforms
This is also a common problem in creating a test database from production. The test database may be very small compared to the production volume. Therefore, when you export and import the tables, they may fail if the initial extents are defined too large to fit in the test database. Instead of creating the table manually prior to importing, you will find Data Pump doing that work for you. You can remove the storage clauses of the tables, MVs etc. using a simple parameter TRANSFORM to impdp. The parameter has the following format:
transform=segment_attributes:[N/Y]:
could be “table”. For instance, to include the storage clause for tables, use it like
transform=segment_attributes:n:table
When put it in impdp, the table creation does not include physical attributes, storage attributes, tablespaces and logging features. Here is a small example to illustrate the concept. First do a normal import:
$ impdp arup/arup directory=tmp_dir dumpfile=metadata_full.dmp sqlfile=trans.sql include=table:\"=\'TRANS\'\"
Here is the DDL as shown in the SQL file trans.sql:
CREATE TABLE "ARUP"."TRANS"
( "TRANS_ID" NUMBER,
"TRANS_DT" DATE,
"PRODUCT_CODE" NUMBER,
"STORE_ID" NUMBER,
"TRANS_AMOUNT" NUMBER(12,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("TRANS_DT")
(PARTITION "Y05Q1" VALUES LESS THAN (TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "Y05Q2" VALUES LESS THAN (TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "Y05Q3" VALUES LESS THAN (TO_DATE(' 2005-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "Y05Q4" VALUES LESS THAN (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)
Now, use the same command using the transform option:
$ impdp arup/arup directory=tmp_dir dumpfile=metadata_full.dmp sqlfile=metadata_full.sql include=table:\"=\'TRANS\'\" transform=segment_attributes:n:table
If you see the SQL file:
CREATE TABLE "ARUP"."TRANS"
( "TRANS_ID" NUMBER,
"TRANS_DT" DATE,
"PRODUCT_CODE" NUMBER,
"STORE_ID" NUMBER,
"TRANS_AMOUNT" NUMBER(12,2)
)
PARTITION BY RANGE ("TRANS_DT")
(PARTITION "Y05Q1" VALUES LESS THAN (TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOCOMPRESS ,
PARTITION "Y05Q2" VALUES LESS THAN (TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) N
OCOMPRESS ,
PARTITION "Y05Q3" VALUES LESS THAN (TO_DATE(' 2005-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) N
OCOMPRESS ,
PARTITION "Y05Q4" VALUES LESS THAN (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) N
OCOMPRESS ,
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE) NOCOMPRESS ) ;
Note how the storage options such as initial/next/max extents, buffer pool, etc. have been removed, even from the partitions. This way there is no conflict with the target database’s storage parameters and the table can be created easily.
If you want to remove storage parameters from all objects, not just tables, leave the third part of the value in the TRANSFORM option:
transform=segment_attributes:n
Reducing Size
On the same line as the above, suppose you want to still keep the initial extent clause in the CREATE TABLE script but want to make it much smaller than it’s in production database. Ordinarily you would have done it using the more laborious manual approach, i.e. creating the SQL file, calculating the desired initial extent and editing the SQL file to plug in those numbers. This may be technically possible but may be infeasible.
A special modifier “PCTSPACE” in the TRANSFORM parameter shown above does the trick. Specifying a PCTSPACE puts the initial extent size as that much percentage of the production database. Here is how you can specify a 10% size of the initial extent:
$ expdp transform=pctspace:30 tables=accounts
In the above case, if the original INITIAL extent of the table ACCOUNTS was 100M, the import creates the table with INITIAL as 10M, or 10% of the original.
Sub-setting a Table
This is also a very common request – create a sub-set of a table in production in the test database so that the tests can be done against it. There could be several reasons why the full table couldn’t be used – the most common being lack of space in test database. So, how can you create a smaller portion of the table, say, 10% of the original rows?
There are two options while creating a sub-set of the table:
1. Take a random sample of 10% of the rows
2. Take the specific rows that satisfy some criteria
Random Sample
To export a random sample of 10% of the rows of all the tables, use the SAMPLE parameter in expdp command as shown below:
$ expdp SAMPLE=10
This takes a sample from all the tables. Suppose you want to take a sample from the table ACCOUNTS in ARUP schema only:
$ expdp SAMPLE=ARUP.ACCOUNTS:10
Rows Satisfying Some Condition
Suppose you want to create a subset of the table based on some criteria, e.g. “SALARY>10000”, you would issue
$ expdp query=employees:"where salary>10000" tables=employees
This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the EMPLOYEES table order by SALARY, here is how the complete command looks like (with the unix required escape characters – backslahes):
$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp query=employees:\"where salary\>10000\ order by salary" tables=employees
If you already have a full dump, you may want to import from that instead of creating a subset of the table in export. You can also use the QUERY parameter in impdp as well.
$ impdp QUERY=CUSTOMERS:“WHERE TOTAL_SPENT > 10"
This can also take the ORDER BY clause. This approach can be used to quickly load parts of the production table to test database. Please note that this does not take care of referential integrity constraints, i.e. the loaded rows do not necessarily satisfy all the foreign key relationships. For instance, if you import 10% of the tables DEPARTMENTS and EMPLOYEES, there is no guarantee that all the department number of the employees in the 10% of the rows of the EMPLOYEES table will be in the 10% of the DEPARTMENTS table. So, the import will fail. In such a case, you can either disable the constraint (not a very useful option) or use the QUERY parameter to select specific rows.
Refresh a Table Definition
You maintain two different databases, say, Development and Integration. From time to time you want to make sure the definitions of the tables are in sync between the two databases. And suddenly you realize that the databases may be out of sync in the structure of the objects. How can you refresh the table in INT so it matches the copy in DEV? Of course you can drop and recreate all the objects; but that’s a task easier said than done. You need to create the SQL script of all the objects – tables, views, procedures, triggers, grants made and received and so on and so forth. You could just drop everything and recreate; but then that means dropping something that may be in INT only and that will not be recreated.
As always, you can use Data Pump to achieve this in just one command. Just take an export of the metadata only. In this example, we assume that you want to refresh only the table ACCOUNTS. After the export, just import the table with the option table_exists_action set to “replace”, which drops the table and recreate it from the metadata contained in the dumpfile.
$ expdp tables=ACCOUNTS content=metadata_only
$ impdp full=y table_exists_action=replace
You can also use this technique to reinstate the copy of the table from the repository as of a certain date. While importing you may also want to modify the storage parameters of the table by using the TRANSFORM parameter as the storage parameters may not be adequate for the target database.
Changing Table’s Owner
This is not a everyday case but it does happen. Someone created a table in the wrong schema – SCOTT, instead of ACCMAIN. Subsequent operations have placed dependent objects on it – triggers, constraints, indexes, etc.; and SCOTT has granted privileges to others. When the mistake was realized, it was too late to make a quick change. What can you do to quickly change the owner.
Unfortunately, in Oracle there is no such thing called changing the owner of the table. The only option is a series of steps:
1. create the SQL to create table;
2. create the table in the new schema;
3. create all grants, triggers, constraints and so on;
4. export the table,
5. import into the old schema
6. drop the table in the old schema
Although feasible, this series of steps is very laborious and error-prone. Of course there are tools to do this; but again, we assume that we don’t have access to these tools. Wedo have access to, however, the Data Pump tool, which accomplishes the task in just one line, using the parameter “remap_schema”.
$ impdp remap_schema=“OLDU:NEWU” network_link=maindb directory=…
Here you changed the owner from OLDU to NEWU. Here is an important point to note here – the use of the parameter “network_link”. It’s a database link created pointing to the same database. The use of this parameter saves us from running an expdp to create a dumpfile. The parameter lets impdp get the metadata across the database link and bypass the dumpfile creation step.
External Tables
External tables are not new in the database; they were first introduced in Oracle 9i. In essence, they are text files outside the database, but are visible to the database users as simple tables. When users select from the table, the actual text file is read and the rows returned as if they are rows of a regular table. The table can only be queried; not updated.
You can also create an ASCII file from a table or set of tables and create an external table on that file to make it visible to the applications. This comes handy while sending some data form one system to another. Since the external tables do not need to be loaded in the database to be visible, this technique makes the data immediately available to the end users and relives stress on the database. The real challenge is how to make this file that will be eventually be used as an external table. You can use the simple SQL*Plus to select all the columns and spool to a file. This approach is the simplest but also the slowest. The other option is to use OCI or Pro*C program to create these files. This makes the file creation quicker; but also adds a lot of time to the process.
Many people are not aware of the fact that you can use Data Pump to create the external table file. This file is not ASCII; it’s binary; but it’s readable across platforms. Let’s see an example: Here we are creating a file trans_ext.dmp to be used as an en external table from the table TRANS.
create table trans_ext (
trans_id,
trans_dt,
product_code,
store_id,
trans_amount
)
organization external
(
type oracle_datapump
default directory tmp_dir
location ('trans_ext.dmp')
)
as
select * from trans
order by trans_id;
Once the table is created, you can use the file as an external table – trans_external – as:
create table trans_external (
trans_id number,
trans_dt date,
product_code number,
store_id number,
trans_amount number(12,2)
)
organization external
(
type oracle_datapump
default directory tmp_dir
location ('trans_ext.dmp')
);
And that’s it; the file is now available as an external table. You would select from it as you would select from a regular table:
select *
from trans_external;
So, how does all this make it any better? The user can always select from the original table TRANS, instead of TRANS_EXTERNAL; so there does not seem to be any advantage in using this approach.
The advantage comes when the user does not have direct access to the database. In order to send the data to a remote user, you have to send it using Data Pump Export. At the other end, the user must import it into the database before he can start using it, which requires some technical expertise. On the other hand, the external table approach takes no technical expertise; the user can simply make the file available to the same server as the database runs on, using ftp, copying the file or something as trivial as mounting the CD on the CD-ROM. As soon as the server sees the file, the external table is immediately visible.
Uses of External Tables
Offline Receiver – in the cases where the receiver of the data is not online with the database, this is the best way to send data without strong technical expertise. The common example is a publication industry, which sends out period updates to the data it sells. Instead of importing the new data, the publisher merely creates an external table file, burns a CD with the file an sends it to the readers. The application on the database reads the external table. When the reader pops in the new CD, the application immediately sees the new data; no data manipulation need occur. Since the data in the external table can’t be written to, the system provides a read only mechanism to the process.
Ease of Creation – You don’t have to write any code to create the files.
Ordering and Grouping – While creating the external table files, you can use WHERE, ORDER BY and GROUP BY clauses, thereby sorting and grouping data. This approach can be used in ETL processes where the data can be loaded without an intermediate sorting/grouping step. One of the best uses of this technique is the creation of Index Organized Tables, described in a sub-section below.
Faster Loading – The regular Data Pump Import still goes through the SQL layer, making it somewhat slower. The fastest way to load a table is still the APPEND method. So, you could use Data Pump export to create the external table file, create external table and load into the main table using the APPEND hint:
insert /*+ append */ into trans
select * from trans_external;
This allows the Direct Path Insert, bypassing the SQL processing layer and minimizing undo and redo and significantly improving the processing time.
Creating IOTs – An Index Organized Table is a table built on a primary key index. In a normal PK index, only the PK columns and the pointer to the corresponding row in the table are stored. In the IOT, the entire row is stored along with the PK index columns. When a user queries a normal table using the PK columns in the WHERE condition, Oracle first gets the rowid from the PK index and gets the row from the table – a two step operation. In an IOT, since the entire row is stored along with the PK, the second step is eliminated.
When you create an IOT, the rows can be loaded in any manner; but if the rows are already in a sorted format, the sorting from the IOT is considerably faster. This can be easily done in Data Pump using the external table approach. While creating the external table files, just use the ORDER BY clause on the PK columns and then load them; the rows will be in sorted order.
General Tips ‘n Tricks
Parallelizing
To make DP Exports run faster, you can use the parallel option to execute the job in parallel. However, if you do use the parallel option, make sure you have that many files as parallel degree; otherwise the parallelism will not take effect.
$ expdp tables=CASES directory=DPDATA1 parallel=4 dumpfile=\(expCASES1.dmp,epCASES2.dmp,expCASES3.dmp,expCASES4.dmp\)
If you do not wan to name the files individually, you can use the %U wildcard.
$ expdp tables=CASES directory=DPDATA1 dumpfile=expCASES_%U.dmp parallel=4
Monitoring
One of the often asked questions is how to monitor the performance of the DP jobs that are running now. There are a few ways you can do it. One is identifying and monitoring the Oracle sessions of the DP jobs. The information about the Data Pump jobs are found in the view DBA_DATAPUMP_SESSIONS.
select sid, serial#
from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;
Once you identify the session, you can see the parallel query slave sessions spawned off by the main DP session by querying the V$PX_SESSION view.
select sid from v$px_session
where qcsid =;
When DP jobs run, they post important runtime information in the view V$SESSION_LONGOPS. You can view all that by issuing:
select sid, serial#, sofar, totalwork
from v$session_longops
where opname = ''
and sofar != totalwork;
Troubleshooting
Like anything else, DP sometimes does not work the way it is supposed to, or perceived to –leading to the next section – Troubleshooting. Many problems can be easily diagnosed using the TRACE on the tool’s innards. There are two different types of trace on Data Pump. The trace built into the tool itself; and the SQL trace on the sessions of DP. Let’s see how each one works.
Data Pump TRACE
The trace is given as a parameter in the command line of the tool in the following format:
TRACE=0300
Theis the component to trace and accepts the following values:
1FF – Full Tracing
048 – Standard Tracing
In many cases the Standard tracing provides all the information you want for a detailed troubleshooting; but sometimes, and usually at the direction of the Oracle Support Services, you may want to enable the Full tracing. Here is an example of the standard tracing.
$ impdp trace=0480300 schema=... and so on ...
This produces a trace file in the user_dump_dest directory, which shows in detail different commands and where they are failing, if at all they are failing.
SQL Trace
This is the usual SQL Tracing you may have been familiar with; however there is a small challenge. Since DP jobs kick off the sessions themselves, you don’t have an opportunity to set the trace in the session by specifying “alter session set sql_trace = true”. Instead, you must enable an event in the session from outside using the dbms_system.set_ev procedure.
First, get the SID and Serial# of the Data Pump session:
select sid,serial#,username,program
from v$session
where upper(program) like '%(DW%)'
or upper(program) like '%(DM%)';
Then trace the session (place the SID and Serial# below)
SQL> exec dbms_system.set_ev(, , 10046, 12, '')
This will produce a trace file in the user_dump_dest directory of the database server.
Conclusion
Data Pump, although originally designed to be a tool to move a lot of data quickly and across multiple database versions and platforms, has some quite useful functionalities much beyond that. In this article you learned a few of those non-traditional uses of the tool that prove extremely useful in the long and short term objectives, without the use of any other expensive tool, whether from Oracle or another third party. The capabilities of the tool are not limited to those listed in the article; but only to your imagination
Data Pump – introduced in Oracle Database 10g Release 1 – has been described in many different ways: from “Export/Import from Steroids” to the “Ultimate Data Mover”. Perhaps the use of superlatives creates a perception that this is only for data movements. Numerous presentations, articles, books and even Oracle manuals permeate that perception.
However, Data Pump is much more than just moving data. While that was probably the original and primary intent and it still remains the principal usage, the other subtle but significant capabilities of Data Pump remains under represented if not undiscovered. In this article you will learn about those lesser known capabilities and how to use them to achieve your strategic and tactical objectives. Put into good use Data Pump can ease a lot of daily and longer term non-data movement related tasks and provide a viable platform for alternate use.
This article assumes that the attendee is familiar with the Data Pump tool. This is not a introduction to the tool itself.
A Data Pump Primer
Even though the audience is assumed to be fully conversant with Data Pump, a quick primer will cement that understanding and pave way for a better understanding of the material to come. If you are familiar with the basics of the tool, you may want to skip this section in its entirety.
Data Pump is an evolution of the original export and import tools; and in fact, it’s also named Data Pump Export and Import. The purpose is similar – to export data out of the Oracle database and import back. The exported data is put in a dumpfile, which is portable across Operating Systems and even across Oracle versions (10g Release 1 and above). This export works by selecting the data from the tables using the SQL interface, similar to the original export; but at a much faster rate. My tests have shown Data Pump Export to be about 10 times faster than original export when conditions are right – with high degrees of parallelism, multiple spindles and so on. Similarly Data Pump Import is also faster than the original import, albeit at a lower scale.
The dumpfile produced can only be ready by Data Pump Import, not the original import. The same holds true for the reverse process as well, i.e. the dumpfile produced by the original export is not readable by DP Import. A major difference in the two tools is the location where the dumpfiles are produced. In the original export, the dumpfiles are produced in the same server the tool is called; so if you issue the exp command at a client machine, such as your laptop, the dumpfile is produced there. In contrast, the DP Export always produces the dumpfile on the database server regardless where it is called. You can specify a location by creating a directory object and specifying that in the command line.
Finally, for the sake of completeness, the commands are “expdp” for DP Export and “impdp” for DP Import.
Now that the introduction part is complete, let’s jump to the main topic – the usefulness of this tool beyond the data movement.
Regulatory Compliance
Would you want to store the source codes of the database codes such as stored procedures and DDLs of the objects? It would have been nice; but probably not strictly needed, just a fe years ago. But thanks to the myriads of laws and regulations covering the corporate America today, this has now become a necessity; not just a nice to have. For instance, laws like Sarbanes-Oxley (SOX) actually require that you have to have a system where you can track the changes to object definitions – commonly referred to as “metadata” in Oracle technical parlance. Please note, the word metadata has many different meanings in technical discussions. For instance, the original meaning of the term is “data about the data”, i.e. the meaning of the data elements such as columns and tables. For example, the metadata on the column STATUS in the table EMP could be: it refers to the status of the employee in the company at this time and it can have values such as ACTIVE, INACTIVE, SUSPENDED, etc. This description is commonly referred to as “metadata”. However, in Oracle-speak, we usually refer to the definition of the objects, i.e. the DDL statement to create the table, the source code of the procedures and packages as metadata. I have used the latter definition of the term.
Most regulations and internal controls in organizations require that the source code be stores in a repository which can be controlled and tracked. If the definitions changed, the system should be able to track it and report changes. The historical records of the metadata should be persevered. How do you accomplish this? One way to achieve this is constructing the sources and DDL from the data dictionary in regular intervals. For tables, you would select from DBA_TAB_COLUMNS (for columns), DBA_CONSTRAINTS, and DBA_CONS_COLUMNS (for constraints), DBA_TAB_PRIVS (for privileges granted to the table) and many others. For source code, you will need to select from DBA_SOURCES; te view DBA_TRIGGERS for triggers, DBA_VIEWS for view definitions and so on. The other option is extracting the DDL from the dictionary using DBMS_METADATA.GET_DDL packaged function. The solutions are technically possible; but may seem infeasible considering the effort. So, to address that deficiency, several tool manufactures have jumped into the fray. Needless to say, the tools do their job; but at a cost.
Enter Data Pump, to accomplish the same objective. Let’s see how.
Metadata Management
One of the least used parameters of Data Pump Export is CONTENT, which controls what is exported. It accepts three parameters:
DATA_ONLY – this option exports the data from tables.
METADATA_ONLY – this option does not export the data; merely exports the metadata of the database objects.
ALL –this is the default; both the metadata and the data (where applicable) are exported.
This parameter is the one you can use to create a baseline repository of the database sources and DDLs very quickly. All you have to do is to use:
expdp directory=dpump_dir dumpfile=metadata.dmp content=metadata_only
This creates the file metadata.dmp in the directory specified by the directlory object dpump_dir containing all the DDLs and source codes of the database objects. The file does not contain any data. This is your baseline repository of the source. If you ever need to reinstate the source, simply drop it from the database and import back the source from this baseline:
impdp directory=dpump_dir dumpfile=metadata.dmp
This will import the objects that are not present in the database now and skip the ones that are there.
In many cases, your database as a whole may not be subject to the baseline repository building; you may want to store objects of specific schemas in the repository; not all. For instance you may want to include the ACCMAIN and HRMAIN schemas but not the SYSTEM schema. The above command would be slightly modified in that case:
expdp directory=dpump_dir dumpfile=metadata.dmp content=metadata_only schemas=ACCMAIN, HRMAIN
By default, all the database objects in the schema are exported – tables, views, triggers, procedures, and so on. What if you want to store each object separately in its individual files? For instance, you may want to store the stored code such as triggers, procedures, etc. on a file named metadata_code.dmp and tables, views, etc. on a file named metadata_tabs.dmp. In this case, you can use the INCLUDE parameter to include only certain objects or types of objects in the export file; not all. The above command will then turn to:
expdp directory=dpump_dir dumpfile=metadata_code.dmp content=metadata_only schemas=ACCMAIN, HRMAIN include=PROCEDURE,FUNCTION,PACKAGE,TRIGGER
Here the dumpfile has only the code related metadata, not all.
You can also specify a specific procedure or finction to export. Suppose you want to keep a very crucial function – GET_RATE and a table – RATES, in a separate file – metadata_imp.dmp, you can use:
expdp directory=dump_dir dumpfile=metadata_imp.dmp schemas=ACCMAIN include=FUNCTION:\"=\'GET_RATE\'\",TABLE:\"=\'RATES\'\"
Note, I have added the backslash (“\”) before the double quotes (“) and the single quotes (‘), just so that the unix shell will not interpret the characters as commands. If you use Windows, you will need to remove the back slashes. Alternatively, a better way is to use a parameter file where you can list all the parameter, one per line and call the parameter using the PARFILE parameter. In this case, just create a file called exp.par with the following as the contents:
directory=dump_dir
dumpfile=metadata_imp.dmp
schemas=accman
include=FUNCTION:"='GET_RATE'"
include=TABLE:"='RATES'"
Then you can call the Data Pump Export as
$ expdp parfile=exp.par
In the parfile, you can see that I have placed two lines with the INCLUDE parameter; that’s allowed. I can place as many INCLUDE parameters as I need. The include parameter takes the values as
For instance, to include the functions FN1 and FN2, you would use:
include=function:"in ('FN1','FN2')"
or
include=function:"like 'FN%'"
Of course, you can also use this to get all but some:
include=function:"not in ('FN1','FN2')"
However, a more convenient alternative may be to use the EXCLUDE option. This is the inverse of INCLUDE and instructs the expdp command to exclude the objects. For example, you want to export the meta data for all objects excluding the functions FN1, FN2 and procedures PROC1 and PROC2. You will use:
exclude=function:"in ('FN1','FN2')"
exclude=procedure:"in ('PROC1','PROC2')"
To include all objects except all the tables in the export dump:
exclude=table
The syntax is the same as the include option. Note a very important difference in the usage of the parameters. Include is used to include a specific objects of a type; exclude is used to exclude a specific object of a type. They are not necessarily interchangeable. Consider these two options:
exclude=function:"in ('FN1','FN2')"
and
include=function:"not in ('FN1','FN2')"
They are not the same. The first option (the exclude one) exports all the objects – tables, views, triggers, etc. and leaves out just two functions – FN1 and FN2. The second option (the include one), on the other hand, only exports all the functions except FN1 and FN2. Only the functions are exported, not tables, views, etc. They are fundamentally different in their behavior. You have to consider this carefully when you construct your parameter file.
Creating an SQL File
Earlier you learned how to create a baseline and also selective exports of the database objects for safe keeping. However, the dumpfiles are not human readable, i.e. you can’t just glean over the files and get the metadata. You need another step – to extract the SQL out of the dumpfile. You can do that using the parameter SQLFILE in the Data Pump Import. Here is an example:
$ impdp directory=dump_dir dumpfile=metadata_full.dmp sqlfile=metadata_full.sql
This operation does not actually import the objects into the database; but creates an SQL script called metadata_full.sql containing all the objects in the dumpfile. Here is an excerpt from the file:
-- CONNECT ARUP
-- new object type path is: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'+NDSDATA7/undotbs101.dbf' SIZE 17179869184,
'+NDSDATA8/undotbs102.dbf' SIZE 17179869184,
'+NDSDATA9/undotbs103.dbf' SIZE 17179869184
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TABLESPACE "SYSAUX" DATAFILE
'+NDSDATA1/sysaux01.dbf' SIZE 4294967296,
'+NDSDATA1/sysaux02.dbf' SIZE 4194304000
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE
'+NDSDATA1/temp1_01.dbf' SIZE 1073741824
......... and so on ........
You can also apply the filters to extract the SQL statements for specific objects as well. For instance, you can extract only the procedures, functions and packages; nothing else by using the INCLUDE parameter
include=procedure, package
You can include specific objects as well; not just specific types.
include=procedure:"='PROC1'"
Here is the file that is eventually produced:
-- CONNECT SYS
-- new object type path is: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
-- CONNECT ARUP
CREATE PROCEDURE "ARUP"."PROC1"
as
begin
dbms_output.put_line ('Some text');
end;
/
-- new object type path is: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ALTER PROCEDURE "ARUP"."PROC1"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED
PLSQL_DEBUG= FALSE
REUSE SETTINGS TIMESTAMP '2006-08-11 13:27:55'
As with the export, you can exclude some objects as well:
exclude=procedure:"='PROC1'"
Building a Repository
Now that you learned how to extract the database objects into SQL files, you can build a system or a rudimentary tool to make the process resemble more like, well, a process. This can be effected somehow using the a shell script and creating the dumpfile and the SQL file named based on the date of creation. Here is how the shell script would look like:
DATEVAR=`date +"%m%d%y"`
DMPFILE=metadata_${DATEVAR}.dmp
SQLFILE=metadata_${DATEVAR}.sql
echo "arup"
expdp arup directory=tmp_dir dumpfile=${DMPFILE}
echo "arup"
impdp arup directory=tmp_dir dumpfile=${DMPFILE} sqlfile=${SQLFILE}
This script will create the file in the format metadata_
As a part of the maintenance, you may want to move the created files periodically. Here is a simple command that moves the files to a name
find . -name "*.dmp" -ctime +30 -exec mv {} {}.old\;
Or, you can just remove it
find . -name "*.dmp" -ctime +30 -exec rm {}\;
Using this, you can build a full version control and repository system in just hours; not months and without using any tool from outside of what you already has. This is one of the most powerful yet underutilized capabilities of the Data Pump.
Create a User Like …
Here is a request you may have received several times – create a user just like JOE. The requestor doesn’t provide any other details – no tablespace quotas, no grants and so on. All the requestor knows that JOE userid works; so another one like JOE will work as well. How do you take care of the request?
Of course, there are tools that can do this quickly; but you may not have them handy. Your only option at that point is to painstakingly create an SQL file caturing the user’s properties for data dictionary, all the objects and so on. While it’s possible, it’s tedious, prone to errors and sunstantilly time consuming.
Enter Data Pump to the rescue. You can create the user in just two commands:
$ expdp schemas=JOE content=metadata_only
$ impdp remap_schema=JOE:NEWUSER
The first command (export) is necessary only if you don’t have a full export dump or baseline now. If you have been using DP to take your metadata baselines, you don’t need to perform that step. The second command (the import) imports the metadata about the user JOE, but transforms it so that instead of JOE, the user NEWUSER is created. All grants, privileges, quotas, objects, etc. of JOE now goes to NEWUSER. In just a matter of minutes you created a user like another accurately.
If you have been using the repository as several versions with timestamps as shown in the previous section, you can even use that to create a user like JOE as of a certain date. For instance, JOE has DBA privileges now; but didn’t have them yesterday. Could we have NEWUSER sans DBA role? Sure; just use the dumpfile as of that day.
Create Tablespaces in Test as in Production
This is another of the challenges you may face – you have been asked to create a small test database, of the fraction of the size of the main database; but the structural elements must be identical. i.e. the tablespaces should be same, laid out in the same manner, across the same mount points and so on. How do you quickly do it?
Sure, you could write a PL/SQL loop to get all the datafile properties from the data dictionary (dba_data_files and dba_tablespaces) and construct the SQL script to create the tablespaces. It will work; but will take a significant amount of time, and, needless to say, is highly susceptible to errors.
Again, Data Pump comes to rescue. All you have to do is import the dumpfile and use the INCLUDE option.
$ expdp content=metadata_only
$ impdp include=tablespace
Like the previous case, the first command (expdp) is not necessary if you already have a dump. Instead of creating the tablespace directly, you may also want to see the script by using the SQLFILE option:
$ impdp include=tablespace sqlfile=c.sql
This will create a file called c.sql, which will have the tablespace creation scripts. You ca edit them, if needed and run them from SQL*Plus.
A slight variation of the above may be more practical. In many cases, the test database may not have or need the size of the production database tablespaces. While creating the tablespaces, you may want to reduce the size of the datafiles by a certain percentage, say 90%. One option is to create a SQL file, edit it and manually change the sizes of all datafiles to 10% of the production size. While it’s doable, it’s not elegant as it adds time and risk of error. You can achieve that in a simple option – TRANSFORM.
$ impdp include=tablespace sqlfile=c.sql transform=pctspace:10
The parameter transform=pctspace:10 instructs the IMPDP job to change the size of the datafiles to 10% of the original size. Here is the excerpt from the SQL file created without this parameter:
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u01/undotbs101.dbf' SIZE 17179869184,
And here is the excerpt when the parameter was applied:
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u01/undotbs101.dbf' SIZE 1717986918,
As you can see, the size with the parameter (1,717,986,918) is about 10% of the original size (17,179,869,184). You will learn more about the TRANSFORM parameter later in this article.
Data File Name Change
You are moving some tables from another database and the tablespace in which these tables exist is also new in the target database. Importing those tables will create the tablespaces in the target database too; but here is a small problem – the filesystems are different. For example, the datafile for tablespace was “/u01/data1.dbf” but the target database does not have a mount point called /u01; instead it has “/u02”. Ordinarily you would have to create the tablespace manually and then import the table into the tablespace.
Data Pump eliminates the extra step in one elegant motion. All you have to do is use the REMAP_DATAFILE option as follows:
$ impdp remap_datafile=‘/u01/data1.dbf':'/u02/data1.dbf'
This creates the same datafile as /u02/data1.dbf. you can use this option to recreate the test data even across different platforms. For instance, the following option in the option converts datafiles from a unix to Windows format.
remap_datafile='/u01/data1.dbf':'c:\oradata\data1.dbf'
Segment Transforms
This is also a common problem in creating a test database from production. The test database may be very small compared to the production volume. Therefore, when you export and import the tables, they may fail if the initial extents are defined too large to fit in the test database. Instead of creating the table manually prior to importing, you will find Data Pump doing that work for you. You can remove the storage clauses of the tables, MVs etc. using a simple parameter TRANSFORM to impdp. The parameter has the following format:
transform=segment_attributes:[N/Y]:
transform=segment_attributes:n:table
When put it in impdp, the table creation does not include physical attributes, storage attributes, tablespaces and logging features. Here is a small example to illustrate the concept. First do a normal import:
$ impdp arup/arup directory=tmp_dir dumpfile=metadata_full.dmp sqlfile=trans.sql include=table:\"=\'TRANS\'\"
Here is the DDL as shown in the SQL file trans.sql:
CREATE TABLE "ARUP"."TRANS"
( "TRANS_ID" NUMBER,
"TRANS_DT" DATE,
"PRODUCT_CODE" NUMBER,
"STORE_ID" NUMBER,
"TRANS_AMOUNT" NUMBER(12,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("TRANS_DT")
(PARTITION "Y05Q1" VALUES LESS THAN (TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "Y05Q2" VALUES LESS THAN (TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "Y05Q3" VALUES LESS THAN (TO_DATE(' 2005-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "Y05Q4" VALUES LESS THAN (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)
Now, use the same command using the transform option:
$ impdp arup/arup directory=tmp_dir dumpfile=metadata_full.dmp sqlfile=metadata_full.sql include=table:\"=\'TRANS\'\" transform=segment_attributes:n:table
If you see the SQL file:
CREATE TABLE "ARUP"."TRANS"
( "TRANS_ID" NUMBER,
"TRANS_DT" DATE,
"PRODUCT_CODE" NUMBER,
"STORE_ID" NUMBER,
"TRANS_AMOUNT" NUMBER(12,2)
)
PARTITION BY RANGE ("TRANS_DT")
(PARTITION "Y05Q1" VALUES LESS THAN (TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOCOMPRESS ,
PARTITION "Y05Q2" VALUES LESS THAN (TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) N
OCOMPRESS ,
PARTITION "Y05Q3" VALUES LESS THAN (TO_DATE(' 2005-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) N
OCOMPRESS ,
PARTITION "Y05Q4" VALUES LESS THAN (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) N
OCOMPRESS ,
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE) NOCOMPRESS ) ;
Note how the storage options such as initial/next/max extents, buffer pool, etc. have been removed, even from the partitions. This way there is no conflict with the target database’s storage parameters and the table can be created easily.
If you want to remove storage parameters from all objects, not just tables, leave the third part of the value in the TRANSFORM option:
transform=segment_attributes:n
Reducing Size
On the same line as the above, suppose you want to still keep the initial extent clause in the CREATE TABLE script but want to make it much smaller than it’s in production database. Ordinarily you would have done it using the more laborious manual approach, i.e. creating the SQL file, calculating the desired initial extent and editing the SQL file to plug in those numbers. This may be technically possible but may be infeasible.
A special modifier “PCTSPACE” in the TRANSFORM parameter shown above does the trick. Specifying a PCTSPACE puts the initial extent size as that much percentage of the production database. Here is how you can specify a 10% size of the initial extent:
$ expdp transform=pctspace:30 tables=accounts
In the above case, if the original INITIAL extent of the table ACCOUNTS was 100M, the import creates the table with INITIAL as 10M, or 10% of the original.
Sub-setting a Table
This is also a very common request – create a sub-set of a table in production in the test database so that the tests can be done against it. There could be several reasons why the full table couldn’t be used – the most common being lack of space in test database. So, how can you create a smaller portion of the table, say, 10% of the original rows?
There are two options while creating a sub-set of the table:
1. Take a random sample of 10% of the rows
2. Take the specific rows that satisfy some criteria
Random Sample
To export a random sample of 10% of the rows of all the tables, use the SAMPLE parameter in expdp command as shown below:
$ expdp SAMPLE=10
This takes a sample from all the tables. Suppose you want to take a sample from the table ACCOUNTS in ARUP schema only:
$ expdp SAMPLE=ARUP.ACCOUNTS:10
Rows Satisfying Some Condition
Suppose you want to create a subset of the table based on some criteria, e.g. “SALARY>10000”, you would issue
$ expdp query=employees:"where salary>10000" tables=employees
This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the EMPLOYEES table order by SALARY, here is how the complete command looks like (with the unix required escape characters – backslahes):
$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp query=employees:\"where salary\>10000\ order by salary" tables=employees
If you already have a full dump, you may want to import from that instead of creating a subset of the table in export. You can also use the QUERY parameter in impdp as well.
$ impdp QUERY=CUSTOMERS:“WHERE TOTAL_SPENT > 10"
This can also take the ORDER BY clause. This approach can be used to quickly load parts of the production table to test database. Please note that this does not take care of referential integrity constraints, i.e. the loaded rows do not necessarily satisfy all the foreign key relationships. For instance, if you import 10% of the tables DEPARTMENTS and EMPLOYEES, there is no guarantee that all the department number of the employees in the 10% of the rows of the EMPLOYEES table will be in the 10% of the DEPARTMENTS table. So, the import will fail. In such a case, you can either disable the constraint (not a very useful option) or use the QUERY parameter to select specific rows.
Refresh a Table Definition
You maintain two different databases, say, Development and Integration. From time to time you want to make sure the definitions of the tables are in sync between the two databases. And suddenly you realize that the databases may be out of sync in the structure of the objects. How can you refresh the table in INT so it matches the copy in DEV? Of course you can drop and recreate all the objects; but that’s a task easier said than done. You need to create the SQL script of all the objects – tables, views, procedures, triggers, grants made and received and so on and so forth. You could just drop everything and recreate; but then that means dropping something that may be in INT only and that will not be recreated.
As always, you can use Data Pump to achieve this in just one command. Just take an export of the metadata only. In this example, we assume that you want to refresh only the table ACCOUNTS. After the export, just import the table with the option table_exists_action set to “replace”, which drops the table and recreate it from the metadata contained in the dumpfile.
$ expdp tables=ACCOUNTS content=metadata_only
$ impdp full=y table_exists_action=replace
You can also use this technique to reinstate the copy of the table from the repository as of a certain date. While importing you may also want to modify the storage parameters of the table by using the TRANSFORM parameter as the storage parameters may not be adequate for the target database.
Changing Table’s Owner
This is not a everyday case but it does happen. Someone created a table in the wrong schema – SCOTT, instead of ACCMAIN. Subsequent operations have placed dependent objects on it – triggers, constraints, indexes, etc.; and SCOTT has granted privileges to others. When the mistake was realized, it was too late to make a quick change. What can you do to quickly change the owner.
Unfortunately, in Oracle there is no such thing called changing the owner of the table. The only option is a series of steps:
1. create the SQL to create table;
2. create the table in the new schema;
3. create all grants, triggers, constraints and so on;
4. export the table,
5. import into the old schema
6. drop the table in the old schema
Although feasible, this series of steps is very laborious and error-prone. Of course there are tools to do this; but again, we assume that we don’t have access to these tools. Wedo have access to, however, the Data Pump tool, which accomplishes the task in just one line, using the parameter “remap_schema”.
$ impdp remap_schema=“OLDU:NEWU” network_link=maindb directory=…
Here you changed the owner from OLDU to NEWU. Here is an important point to note here – the use of the parameter “network_link”. It’s a database link created pointing to the same database. The use of this parameter saves us from running an expdp to create a dumpfile. The parameter lets impdp get the metadata across the database link and bypass the dumpfile creation step.
External Tables
External tables are not new in the database; they were first introduced in Oracle 9i. In essence, they are text files outside the database, but are visible to the database users as simple tables. When users select from the table, the actual text file is read and the rows returned as if they are rows of a regular table. The table can only be queried; not updated.
You can also create an ASCII file from a table or set of tables and create an external table on that file to make it visible to the applications. This comes handy while sending some data form one system to another. Since the external tables do not need to be loaded in the database to be visible, this technique makes the data immediately available to the end users and relives stress on the database. The real challenge is how to make this file that will be eventually be used as an external table. You can use the simple SQL*Plus to select all the columns and spool to a file. This approach is the simplest but also the slowest. The other option is to use OCI or Pro*C program to create these files. This makes the file creation quicker; but also adds a lot of time to the process.
Many people are not aware of the fact that you can use Data Pump to create the external table file. This file is not ASCII; it’s binary; but it’s readable across platforms. Let’s see an example: Here we are creating a file trans_ext.dmp to be used as an en external table from the table TRANS.
create table trans_ext (
trans_id,
trans_dt,
product_code,
store_id,
trans_amount
)
organization external
(
type oracle_datapump
default directory tmp_dir
location ('trans_ext.dmp')
)
as
select * from trans
order by trans_id;
Once the table is created, you can use the file as an external table – trans_external – as:
create table trans_external (
trans_id number,
trans_dt date,
product_code number,
store_id number,
trans_amount number(12,2)
)
organization external
(
type oracle_datapump
default directory tmp_dir
location ('trans_ext.dmp')
);
And that’s it; the file is now available as an external table. You would select from it as you would select from a regular table:
select *
from trans_external;
So, how does all this make it any better? The user can always select from the original table TRANS, instead of TRANS_EXTERNAL; so there does not seem to be any advantage in using this approach.
The advantage comes when the user does not have direct access to the database. In order to send the data to a remote user, you have to send it using Data Pump Export. At the other end, the user must import it into the database before he can start using it, which requires some technical expertise. On the other hand, the external table approach takes no technical expertise; the user can simply make the file available to the same server as the database runs on, using ftp, copying the file or something as trivial as mounting the CD on the CD-ROM. As soon as the server sees the file, the external table is immediately visible.
Uses of External Tables
Offline Receiver – in the cases where the receiver of the data is not online with the database, this is the best way to send data without strong technical expertise. The common example is a publication industry, which sends out period updates to the data it sells. Instead of importing the new data, the publisher merely creates an external table file, burns a CD with the file an sends it to the readers. The application on the database reads the external table. When the reader pops in the new CD, the application immediately sees the new data; no data manipulation need occur. Since the data in the external table can’t be written to, the system provides a read only mechanism to the process.
Ease of Creation – You don’t have to write any code to create the files.
Ordering and Grouping – While creating the external table files, you can use WHERE, ORDER BY and GROUP BY clauses, thereby sorting and grouping data. This approach can be used in ETL processes where the data can be loaded without an intermediate sorting/grouping step. One of the best uses of this technique is the creation of Index Organized Tables, described in a sub-section below.
Faster Loading – The regular Data Pump Import still goes through the SQL layer, making it somewhat slower. The fastest way to load a table is still the APPEND method. So, you could use Data Pump export to create the external table file, create external table and load into the main table using the APPEND hint:
insert /*+ append */ into trans
select * from trans_external;
This allows the Direct Path Insert, bypassing the SQL processing layer and minimizing undo and redo and significantly improving the processing time.
Creating IOTs – An Index Organized Table is a table built on a primary key index. In a normal PK index, only the PK columns and the pointer to the corresponding row in the table are stored. In the IOT, the entire row is stored along with the PK index columns. When a user queries a normal table using the PK columns in the WHERE condition, Oracle first gets the rowid from the PK index and gets the row from the table – a two step operation. In an IOT, since the entire row is stored along with the PK, the second step is eliminated.
When you create an IOT, the rows can be loaded in any manner; but if the rows are already in a sorted format, the sorting from the IOT is considerably faster. This can be easily done in Data Pump using the external table approach. While creating the external table files, just use the ORDER BY clause on the PK columns and then load them; the rows will be in sorted order.
General Tips ‘n Tricks
Parallelizing
To make DP Exports run faster, you can use the parallel option to execute the job in parallel. However, if you do use the parallel option, make sure you have that many files as parallel degree; otherwise the parallelism will not take effect.
$ expdp tables=CASES directory=DPDATA1 parallel=4 dumpfile=\(expCASES1.dmp,epCASES2.dmp,expCASES3.dmp,expCASES4.dmp\)
If you do not wan to name the files individually, you can use the %U wildcard.
$ expdp tables=CASES directory=DPDATA1 dumpfile=expCASES_%U.dmp parallel=4
Monitoring
One of the often asked questions is how to monitor the performance of the DP jobs that are running now. There are a few ways you can do it. One is identifying and monitoring the Oracle sessions of the DP jobs. The information about the Data Pump jobs are found in the view DBA_DATAPUMP_SESSIONS.
select sid, serial#
from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;
Once you identify the session, you can see the parallel query slave sessions spawned off by the main DP session by querying the V$PX_SESSION view.
select sid from v$px_session
where qcsid =
When DP jobs run, they post important runtime information in the view V$SESSION_LONGOPS. You can view all that by issuing:
select sid, serial#, sofar, totalwork
from v$session_longops
where opname = '
and sofar != totalwork;
Troubleshooting
Like anything else, DP sometimes does not work the way it is supposed to, or perceived to –leading to the next section – Troubleshooting. Many problems can be easily diagnosed using the TRACE on the tool’s innards. There are two different types of trace on Data Pump. The trace built into the tool itself; and the SQL trace on the sessions of DP. Let’s see how each one works.
Data Pump TRACE
The trace is given as a parameter in the command line of the tool in the following format:
TRACE=
The
1FF – Full Tracing
048 – Standard Tracing
In many cases the Standard tracing provides all the information you want for a detailed troubleshooting; but sometimes, and usually at the direction of the Oracle Support Services, you may want to enable the Full tracing. Here is an example of the standard tracing.
$ impdp trace=0480300 schema=... and so on ...
This produces a trace file in the user_dump_dest directory, which shows in detail different commands and where they are failing, if at all they are failing.
SQL Trace
This is the usual SQL Tracing you may have been familiar with; however there is a small challenge. Since DP jobs kick off the sessions themselves, you don’t have an opportunity to set the trace in the session by specifying “alter session set sql_trace = true”. Instead, you must enable an event in the session from outside using the dbms_system.set_ev procedure.
First, get the SID and Serial# of the Data Pump session:
select sid,serial#,username,program
from v$session
where upper(program) like '%(DW%)'
or upper(program) like '%(DM%)';
Then trace the session (place the SID and Serial# below)
SQL> exec dbms_system.set_ev(
This will produce a trace file in the user_dump_dest directory of the database server.
Conclusion
Data Pump, although originally designed to be a tool to move a lot of data quickly and across multiple database versions and platforms, has some quite useful functionalities much beyond that. In this article you learned a few of those non-traditional uses of the tool that prove extremely useful in the long and short term objectives, without the use of any other expensive tool, whether from Oracle or another third party. The capabilities of the tool are not limited to those listed in the article; but only to your imagination
Monday, August 25, 2008
Oracle RAC installation on Solaris SPARC 64 bit
Keyword:Oracle RAC Installation RAC Installation
Few weeks back i did a 2 node oracle RAC installation
The Machines were Soalris 10 SPARC 64 bit (Sun-Fire-T2000).
The Shared storage was NAS
Even though Solaris 10 uses resource control, the kernel parameters were added in the /etc/system (#Metalink: Note:367442.1)
The group OINSTALL and user ORACLE were created on both nodes
Few parameters were tuned in /etc/rc2.d/S99nettune
bash-3.00# more /etc/rc2.d/S99nettune
#!/bin/sh
ndd -set /dev/ip ip_forward_src_routed 0
ndd -set /dev/ip ip_forwarding 0
ndd -set /dev/tcp tcp_conn_req_max_q 16384
ndd -set /dev/tcp tcp_conn_req_max_q0 16384
ndd -set /dev/tcp tcp_xmit_hiwat 400000
ndd -set /dev/tcp tcp_recv_hiwat 400000
ndd -set /dev/tcp tcp_cwnd_max 2097152
ndd -set /dev/tcp tcp_ip_abort_interval 60000
ndd -set /dev/tcp tcp_rexmit_interval_initial 4000
ndd -set /dev/tcp tcp_rexmit_interval_max 10000
ndd -set /dev/tcp tcp_rexmit_interval_min 3000
ndd -set /dev/tcp tcp_max_buf 4194304
ndd -set /dev/tcp tcp_maxpsz_multiplier 10
#Oracle Required
ndd -set /dev/udp udp_recv_hiwat 65535
ndd -set /dev/udp udp_xmit_hiwat 65535
Check /etc/system is readable by ORACLE (else RDBMS installation will fail)
-rw-r–r– 1 root root 2561 Apr 17 16:03 /etc/system
Checked the system config on both nodes
For RAM
/usr/sbin/prtconf | grep “Memory size”
Memory size: 8064 Megabytes
For SWAP
/usr/sbin/swap -s
total: 4875568k bytes allocated + 135976k reserved = 5011544k used, 9800072k available
For /tmp
df -h /tmp
Filesystem size used avail capacity Mounted on
swap 9.4G 31M 9.4G 1% /tmp
For OS
/bin/isainfo -kv
64-bit sparcv9 kernel modules
For user
id -a #both UID and GID of user ORACLE should be same on both nodes
uid=300(ORACLE) gid=300(oinstall) groups=300(oinstall),301(dba),503(tms),504(mscat),102(dwh)
User nobody should exist
id -a nobody
uid=60001(nobody) gid=60001(nobody) groups=60001(nobody)
I had the below entries in the /etc/hosts on both nodes
[]cat /etc/hosts
#Public:
3.208.169.203 ownserver01 ownserver01ipmp0 loghost
3.208.169.207 ownserver02 ownserver02ipmp0 loghost
#Private:
10.47.2.82 ownserver01ipmp1 # e1000g1 -Used this while installing cluster
10.47.2.85 ownserver02ipmp1 # e1000g1 -Used this while installing cluster
10.47.2.76 ownserver01ipmp2 # e1000g0
10.47.2.79 ownserver02ipmp2 # e1000g0
#Vip:
3.208.169.202 ownserverv01
3.208.169.206 ownserverv02
All the interfaces had their ipmp groups.
Confirmed that the interface names of both Private and Public are same across the nodes.
e1000g3 was the Public Interface on both nodes.
e1000g0 and e1000g1 were the Private Interface on both nodes.
-I had 2 interfaces for Private Interconnect, of which i used e1000g1 during the cluster installation.
-The interface names for e1000g1 on each node were ownserver01ipmp1 and ownserver02ipmp1
Below is the ‘ifconfig -a‘ from Ownserver01
ownserver01 [SHCL1DR1]$ ifconfig -a
lo0: flags=2001000849 mtu 8232 index 1
inet 127.0.0.1 netmask ff000000
e1000g0: flags=1000843 mtu 1500 index 2
inet 10.47.2.76 netmask ffffffe0 broadcast 10.47.2.95
groupname ipmp2
e1000g0:1: flags=9040843 mtu 1500 index 2
inet 10.47.2.77 netmask ffffffe0 broadcast 10.47.2.95
e1000g0:2: flags=9040842 mtu 1500 index 2
inet 10.47.2.77 netmask ff000000 broadcast 10.255.255.255
e1000g1: flags=1000843 mtu 1500 index 3
inet 10.47.2.82 netmask ffffffe0 broadcast 10.47.2.95
groupname ipmp1
e1000g1:1: flags=9040843 mtu 1500 index 3
inet 10.47.2.83 netmask ffffffe0 broadcast 10.47.2.95
e1000g1:2: flags=9040842 mtu 1500 index 3
inet 10.47.2.83 netmask ff000000 broadcast 10.255.255.255
e1000g2: flags=1000843 mtu 1500 index 4
inet 10.47.2.11 netmask ffffffc0 broadcast 10.47.2.63
groupname ipmp3
e1000g2:1: flags=9040843 mtu 1500 index 4
inet 10.47.2.12 netmask ffffffc0 broadcast 10.47.2.63
e1000g2:2: flags=9040842 mtu 1500 index 4
inet 10.47.2.12 netmask ff000000 broadcast 10.255.255.255
e1000g3: flags=1000843 mtu 1500 index 5
inet 3.208.169.203 netmask ffffffc0 broadcast 3.208.169.255
groupname ipmp0
e1000g3:1: flags=9040843 mtu 1500 index 5
inet 3.208.169.204 netmask ffffffc0 broadcast 3.208.169.255
e1000g3:2: flags=9040842 mtu 1500 index 5
inet 3.208.169.204 netmask ff000000 broadcast 3.255.255.255
nxge0: flags=69040843 mtu 1500 index 6
inet 10.47.2.13 netmask ffffffc0 broadcast 10.47.2.63
groupname ipmp3
nxge1: flags=69040843 mtu 1500 index 7
inet 3.208.169.205 netmask ffffffc0 broadcast 3.208.169.255
groupname ipmp0
nxge2: flags=69040843 mtu 1500 index 8
inet 10.47.2.78 netmask ffffffe0 broadcast 10.47.2.95
groupname ipmp2
nxge3: flags=69040843 mtu 1500 index 9
inet 10.47.2.84 netmask ffffffe0 broadcast 10.47.2.95
groupname ipmp1
From the above output its clear that On Ownserver01
e1000g0 is 10.47.2.76 and e1000g1 is 10.47.2.82 - For Private Interconnect
e1000g2 is 10.47.2.11 - For Shared Storage (NAS)
e1000g3 is 3.208.169.203 - For Public
On Ownserver02
e1000g0 is 10.47.2.79 and e1000g1 is 10.47.2.85 - For Private Interconnect
e1000g2 is 10.47.2.14 - For Shared Storage (NAS)
e1000g3 is 3.208.169.207 - For Public
Checked for SSH and SCP in /usr/local/bin/
The cluster verification utility(runcluvfy.sh ) checks for scp and ssh in /usr/local/bin/.
Create soft links of ssh and scp in /usr/local/bin/ if they are not there.
cd /usr/local/bin/
ls -l
lrwxrwxrwx 1 root root 12 Apr 25 16:57 /usr/local/bin/scp -> /usr/bin/scp
lrwxrwxrwx 1 root root 12 Apr 25 16:57 /usr/local/bin/ssh -> /usr/bin/ssh
Checked SSH equivalency between the nodes
ownserver01 [SHCL1DR1]$ ssh ownserver01 date
ssh_exchange_identification: Connection closed by remote host
ownserver01 [SHCL1DR1]$
mkdir ~/.ssh
chmod 700 ~/.ssh
/usr/bin/ssh-keygen -t rsa
usr/bin/ssh-keygen -t dsa
touch ~/.ssh/authorized_keys
ssh ownserver01 cat /home/oracle/.ssh/id_rsa.pub >>authorized_keys
Passowrd: *****
ssh ownserver01 cat /home/oracle/.ssh/id_dsa.pub >>authorized_keys
Password: *****
ssh ownserver02 cat /home/oracle/.ssh/id_rsa.pub >>authorized_keys
Password: *****
ssh ownserver02 cat /home/oracle/.ssh/id_dsa.pub>>authorized_keys
Password:*****
From Ownserver01
ownserver01 [SHCL1DR1]$ ssh OWNSERVER01 date
ownserver01 [SHCL1DR1]$ ssh OWNSERVER02 date
From Ownserver02
ownserver02 [SHCL1DR2]$ ssh OWNSERVER01 date
ownserver02 [SHCL1DR2]$ ssh OWNSERVER02 date
The time on both nodes were same at any time.
I made sure that from Ownserver01 i could SSH to ownserver02 and also to ownserver01 itself and the same from Ownserver02 too.
Note: also mv /etc/issue to /etc/issue.bak or user equivalence will fail – This clears the login banner. (this may be for Solaris only)
Checked for file /usr/lib/libdce.so [ Metalink Note Note:333348.1]
The 10gR2 installer on Soalris 64 bit fails if the file /usr/lib/libdce.so is present.
Check Metalink Note Note:333348.1 for the workaround.
Configure the .profile of user ORACLE
stty cs8 -istrip -parenb
PATH=/usr/bin:/usr/local/bin
EDITOR=/usr/bin/vi
#umask 077
umask 022
ulimit -c 0
export PATH EDITOR
set -o vi
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export OH=$ORACLE_HOME
export ORA_CRS_HOME=$ORACLE_BASE/product/crs
export CH=$ORA_CRS_HOME
export ORACLE_SID=SHCL1DR1
#export NLS_LANG=Japanese_Japan.UTF8
export NLS_LANG=AMERICAN_AMERICA.UTF8
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/sbin:/usr/bin:/usr/ccs/bin:/usr/ucb:/etc:/usr/X/bin:/usr/openwin/bin:/usr/local/bin:/usr/sbin
export PS1=`hostname`” [$ORACLE_SID]\$ “
####
alias bdump=’cd /u01/app/oracle/admin/SHCL1/bdump/’
alias talert=’tail -f $ORACLE_BASE/admin/SHCL1/bdump/alert_$ORACLE_SID.log’
alias tns=’cd $ORACLE_HOME/network/admin’
alias udump=’cd /u01/app/oracle/admin/SHCL1/bdump/’
alias valert=’view $ORACLE_BASE/admin/SHCL1/bdump/alert_$ORACLE_SID.log’
alias home=’cd $ORACLE_HOME’
Created the directories on both nodes
For ORACLE_BASE
mkdir -p /u01/app/oracle
chown -R ORACLE:oinstall /u01/app/oracle
chmod -R 770 /u01/app/oracle
For ORA_CRS_HOME
mkdir -p /u01/app/oracle/product/crs
chown -R root:oinstall /u01/app/oracle/product/crs
For ORACLE_HOME [RDBMS]
mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R ORACLE:oinstall /u01/app/oracle/product/10.2.0/db_1
For OCR and Voting disks
mkdir -p /u02/oracle/crs/
mkdir -p /u03/oracle/crs/
mkdir -p /u04/oracle/crs/
Check the privileges on the directories [should be ORACLE:oinstall]
Created OCR and Voting Disk files
In Linux, the oracle provided cluster file system OCSF2 is used on the shared disk,so ‘touch’ ing ocr_disk1 and vote_disk1would do.
But since i used NAS as the shared storage (which is mounted on each nodes), i had to create raw files for ocr and voting disk.
OCR
chown root:oinstall /u02/oracle/crs/ocr_disk1
chown root:oinstall /u03/oracle/crs/ocr_disk2
chmod 660 /u02/oracle/crs/ocr_disk1
chmod 660 /u03/oracle/crs/ocr_disk2
VOTING DISK
chown ORACLE:oinstall /u02/oracle/crs/vote_disk1
chown ORACLE:oinstall /u03/oracle/crs/vote_disk2
chown ORACLE:oinstall /u04/oracle/crs/vote_disk3
chmod 660 /u02/oracle/crs/vote_disk1
chmod 660 /u03/oracle/crs/vote_disk2
chmod 660 /u04/oracle/crs/vote_disk3
Downloaded and unzipped Oracle 10.2.0.1 installation files
10gr2_cluster_sol.cpio
10gr2_companion_sol.cpio
10gr2_db_sol.cpio
Run the Cluster Verification Utility available in
10gr2_cluster_sol.cpio
ownserver01 [SHCL1DR1]$ ./runcluvfy.sh stage -pre crsinst -n OWNSERVER01,OWNSERVER02 -verbose
Performing pre-checks for cluster services setup
Checking node reachability…
Check: Node reachability from node “ownserver01″
Destination Node Reachable?
———————————— ————————
OWNSERVER01 yes
OWNSERVER02 yes
Result: Node reachability check passed from node “ownserver01″.
Checking user equivalence…
Check: User equivalence for user “ORACLE”
Node Name Comment
———————————— ————————
OWNSERVER02 passed
OWNSERVER01 passed
Result: User equivalence check passed for user “ORACLE”.
Checking administrative privileges…
Check: Existence of user “ORACLE”
Node Name User Exists Comment
———— ———————— ————————
OWNSERVER02 yes passed
OWNSERVER01 yes passed
Result: User existence check passed for “ORACLE”.
Check: Existence of group “oinstall”
Node Name Status Group ID
———— ———————— ————————
OWNSERVER02 exists 300
OWNSERVER01 exists 300
Result: Group existence check passed for “oinstall”.
Check: Membership of user “ORACLE” in group “oinstall” [as Primary]
Node Name User Exists Group Exists User in Group Primary Comment
—————- ———— ———— ———— ———— ————
OWNSERVER02 yes yes yes yes passed
OWNSERVER01 yes yes yes yes passed
Result: Membership check for user “ORACLE” in group “oinstall” [as Primary] passed.
Administrative privileges check passed.
Checking node connectivity…
Interface information for node “OWNSERVER02″
Interface Name IP Address Subnet
—————————— —————————— —————-
e1000g0 10.47.2.79 10.47.2.64
e1000g0 10.47.2.80 10.47.2.64
e1000g0 10.47.2.80 10.0.0.0
e1000g1 10.47.2.85 10.47.2.64
e1000g1 10.47.2.86 10.47.2.64
e1000g1 10.47.2.86 10.0.0.0
e1000g2 10.47.2.14 10.47.2.0
e1000g2 10.47.2.15 10.47.2.0
e1000g2 10.47.2.15 10.0.0.0
e1000g3 3.208.169.207 3.208.169.192
e1000g3 3.208.169.208 3.208.169.192
e1000g3 3.208.169.208 3.0.0.0
nxge0 10.47.2.16 10.47.2.0
nxge1 3.208.169.209 3.208.169.192
nxge2 10.47.2.81 10.47.2.64
nxge3 10.47.2.87 10.47.2.64
Interface information for node “OWNSERVER01″
Interface Name IP Address Subnet
—————————— —————————— —————-
e1000g0 10.47.2.76 10.47.2.64
e1000g0 10.47.2.77 10.47.2.64
e1000g0 10.47.2.77 10.0.0.0
e1000g1 10.47.2.82 10.47.2.64
e1000g1 10.47.2.83 10.47.2.64
e1000g1 10.47.2.83 10.0.0.0
e1000g2 10.47.2.11 10.47.2.0
e1000g2 10.47.2.12 10.47.2.0
e1000g2 10.47.2.12 10.0.0.0
e1000g3 3.208.169.203 3.208.169.192
e1000g3 3.208.169.204 3.208.169.192
e1000g3 3.208.169.204 3.0.0.0
nxge0 10.47.2.13 10.47.2.0
nxge1 3.208.169.205 3.208.169.192
nxge2 10.47.2.78 10.47.2.64
nxge3 10.47.2.84 10.47.2.64
Check: Node connectivity of subnet “10.47.2.64″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g0 OWNSERVER02:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:nxge2 yes
OWNSERVER02:e1000g0 OWNSERVER02:nxge3 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:nxge2 yes
OWNSERVER02:e1000g0 OWNSERVER01:nxge3 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:nxge2 yes
OWNSERVER02:e1000g0 OWNSERVER02:nxge3 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:nxge2 yes
OWNSERVER02:e1000g0 OWNSERVER01:nxge3 yes
OWNSERVER02:e1000g1 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER02:nxge2 yes
OWNSERVER02:e1000g1 OWNSERVER02:nxge3 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:nxge2 yes
OWNSERVER02:e1000g1 OWNSERVER01:nxge3 yes
OWNSERVER02:e1000g1 OWNSERVER02:nxge2 yes
OWNSERVER02:e1000g1 OWNSERVER02:nxge3 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:nxge2 yes
OWNSERVER02:e1000g1 OWNSERVER01:nxge3 yes
OWNSERVER02:nxge2 OWNSERVER02:nxge3 yes
OWNSERVER02:nxge2 OWNSERVER01:e1000g0 yes
OWNSERVER02:nxge2 OWNSERVER01:e1000g0 yes
OWNSERVER02:nxge2 OWNSERVER01:e1000g1 yes
OWNSERVER02:nxge2 OWNSERVER01:e1000g1 yes
OWNSERVER02:nxge2 OWNSERVER01:nxge2 yes
OWNSERVER02:nxge2 OWNSERVER01:nxge3 yes
OWNSERVER02:nxge3 OWNSERVER01:e1000g0 yes
OWNSERVER02:nxge3 OWNSERVER01:e1000g0 yes
OWNSERVER02:nxge3 OWNSERVER01:e1000g1 yes
OWNSERVER02:nxge3 OWNSERVER01:e1000g1 yes
OWNSERVER02:nxge3 OWNSERVER01:nxge2 yes
OWNSERVER02:nxge3 OWNSERVER01:nxge3 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:nxge2 yes
OWNSERVER01:e1000g0 OWNSERVER01:nxge3 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:nxge2 yes
OWNSERVER01:e1000g0 OWNSERVER01:nxge3 yes
OWNSERVER01:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g1 OWNSERVER01:nxge2 yes
OWNSERVER01:e1000g1 OWNSERVER01:nxge3 yes
OWNSERVER01:e1000g1 OWNSERVER01:nxge2 yes
OWNSERVER01:e1000g1 OWNSERVER01:nxge3 yes
OWNSERVER01:nxge2 OWNSERVER01:nxge3 yes
Result: Node connectivity check passed for subnet “10.47.2.64″ with node(s) OWNSERVER02,OWNSERVER01.
Check: Node connectivity of subnet “10.0.0.0″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g2 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g1 OWNSERVER02:e1000g2 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g2 yes
OWNSERVER01:e1000g1 OWNSERVER01:e1000g2 yes
Result: Node connectivity check passed for subnet “10.0.0.0″ with node(s) OWNSERVER02,OWNSERVER01.
Check: Node connectivity of subnet “10.47.2.0″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g2 OWNSERVER02:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER02:nxge0 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:nxge0 yes
OWNSERVER02:e1000g2 OWNSERVER02:nxge0 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:nxge0 yes
OWNSERVER02:nxge0 OWNSERVER01:e1000g2 yes
OWNSERVER02:nxge0 OWNSERVER01:e1000g2 yes
OWNSERVER02:nxge0 OWNSERVER01:nxge0 yes
OWNSERVER01:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER01:e1000g2 OWNSERVER01:nxge0 yes
OWNSERVER01:e1000g2 OWNSERVER01:nxge0 yes
Result: Node connectivity check passed for subnet “10.47.2.0″ with node(s) OWNSERVER02,OWNSERVER01.
Check: Node connectivity of subnet “3.208.169.192″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g3 OWNSERVER02:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER02:nxge1 yes
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER01:nxge1 yes
OWNSERVER02:e1000g3 OWNSERVER02:nxge1 yes
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER01:nxge1 yes
OWNSERVER02:nxge1 OWNSERVER01:e1000g3 yes
OWNSERVER02:nxge1 OWNSERVER01:e1000g3 yes
OWNSERVER02:nxge1 OWNSERVER01:nxge1 yes
OWNSERVER01:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER01:e1000g3 OWNSERVER01:nxge1 yes
OWNSERVER01:e1000g3 OWNSERVER01:nxge1 yes
Result: Node connectivity check passed for subnet “3.208.169.192″ with node(s) OWNSERVER02,OWNSERVER01.
Check: Node connectivity of subnet “3.0.0.0″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
Result: Node connectivity check passed for subnet “3.0.0.0″ with node(s) OWNSERVER02,OWNSERVER01.
Suitable interfaces for VIP on subnet “3.208.169.192″:
OWNSERVER02 e1000g3:3.208.169.207 e1000g3:3.208.169.208
OWNSERVER01 e1000g3:3.208.169.203 e1000g3:3.208.169.204
Suitable interfaces for VIP on subnet “3.208.169.192″:
OWNSERVER02 nxge1:3.208.169.209
OWNSERVER01 nxge1:3.208.169.205
I ignored the last messages about the VIP, as i knew there wasn’t any problem.
Click “Add” to add the second node
Public Node Name :ownserver02
Private Node Name:ownserver02ipmp1
Virtual Host Name:ownserverv02
On Ownserver01
bash-3.00# /u01/app/oracle/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oracle/oraInventory to 770.
Changing groupname of /u01/app/oracle/oraInventory to oinstall.
The execution of the script is complete
bash-3.00# ls -l /u01/app/oracle/product/crs/root.sh
-rwxr-xr-x 1 ORACLE oinstall 105 Apr 30 11:14 /u01/app/oracle/product/crs/root.sh
bash-3.00# /u01/app/oracle/product/crs/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node:
node 1: ownserver01 ownserver01ipmp1 ownserver01
node 2: ownserver02 ownserver02ipmp1 ownserver02
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Now formatting voting device: /u02/oracle/crs/vote_disk1
Now formatting voting device: /u03/oracle/crs/vote_disk2
Now formatting voting device: /u04/oracle/crs/vote_disk3
Format of 3 voting devices complete.
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
ownserver01
CSS is inactive on these nodes.
ownserver02
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.
bash-3.00# /u01/app/oracle/product/crs/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node:
node 1: ownserver01 ownserver01ipmp1 ownserver01
node 2: ownserver02 ownserver02ipmp1 ownserver02
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
ownserver01
ownserver02
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps
Creating VIP application resource on (2) nodes…
Creating GSD application resource on (2) nodes…
Creating ONS application resource on (2) nodes…
Starting VIP application resource on (2) nodes…
Starting GSD application resource on (2) nodes…
Starting ONS application resource on (2) nodes…
Done.
Oracle RAC Installation RAC Installation
Installing Oracle RAC Installing
Oracle RAC on Solaris
Few weeks back i did a 2 node oracle RAC installation
The Machines were Soalris 10 SPARC 64 bit (Sun-Fire-T2000).
The Shared storage was NAS
Even though Solaris 10 uses resource control, the kernel parameters were added in the /etc/system (#Metalink: Note:367442.1)
The group OINSTALL and user ORACLE were created on both nodes
Few parameters were tuned in /etc/rc2.d/S99nettune
bash-3.00# more /etc/rc2.d/S99nettune
#!/bin/sh
ndd -set /dev/ip ip_forward_src_routed 0
ndd -set /dev/ip ip_forwarding 0
ndd -set /dev/tcp tcp_conn_req_max_q 16384
ndd -set /dev/tcp tcp_conn_req_max_q0 16384
ndd -set /dev/tcp tcp_xmit_hiwat 400000
ndd -set /dev/tcp tcp_recv_hiwat 400000
ndd -set /dev/tcp tcp_cwnd_max 2097152
ndd -set /dev/tcp tcp_ip_abort_interval 60000
ndd -set /dev/tcp tcp_rexmit_interval_initial 4000
ndd -set /dev/tcp tcp_rexmit_interval_max 10000
ndd -set /dev/tcp tcp_rexmit_interval_min 3000
ndd -set /dev/tcp tcp_max_buf 4194304
ndd -set /dev/tcp tcp_maxpsz_multiplier 10
#Oracle Required
ndd -set /dev/udp udp_recv_hiwat 65535
ndd -set /dev/udp udp_xmit_hiwat 65535
Check /etc/system is readable by ORACLE (else RDBMS installation will fail)
-rw-r–r– 1 root root 2561 Apr 17 16:03 /etc/system
Checked the system config on both nodes
For RAM
/usr/sbin/prtconf | grep “Memory size”
Memory size: 8064 Megabytes
For SWAP
/usr/sbin/swap -s
total: 4875568k bytes allocated + 135976k reserved = 5011544k used, 9800072k available
For /tmp
df -h /tmp
Filesystem size used avail capacity Mounted on
swap 9.4G 31M 9.4G 1% /tmp
For OS
/bin/isainfo -kv
64-bit sparcv9 kernel modules
For user
id -a #both UID and GID of user ORACLE should be same on both nodes
uid=300(ORACLE) gid=300(oinstall) groups=300(oinstall),301(dba),503(tms),504(mscat),102(dwh)
User nobody should exist
id -a nobody
uid=60001(nobody) gid=60001(nobody) groups=60001(nobody)
I had the below entries in the /etc/hosts on both nodes
[]cat /etc/hosts
#Public:
3.208.169.203 ownserver01 ownserver01ipmp0 loghost
3.208.169.207 ownserver02 ownserver02ipmp0 loghost
#Private:
10.47.2.82 ownserver01ipmp1 # e1000g1 -Used this while installing cluster
10.47.2.85 ownserver02ipmp1 # e1000g1 -Used this while installing cluster
10.47.2.76 ownserver01ipmp2 # e1000g0
10.47.2.79 ownserver02ipmp2 # e1000g0
#Vip:
3.208.169.202 ownserverv01
3.208.169.206 ownserverv02
All the interfaces had their ipmp groups.
Confirmed that the interface names of both Private and Public are same across the nodes.
e1000g3 was the Public Interface on both nodes.
e1000g0 and e1000g1 were the Private Interface on both nodes.
-I had 2 interfaces for Private Interconnect, of which i used e1000g1 during the cluster installation.
-The interface names for e1000g1 on each node were ownserver01ipmp1 and ownserver02ipmp1
Below is the ‘ifconfig -a‘ from Ownserver01
ownserver01 [SHCL1DR1]$ ifconfig -a
lo0: flags=2001000849
inet 127.0.0.1 netmask ff000000
e1000g0: flags=1000843
inet 10.47.2.76 netmask ffffffe0 broadcast 10.47.2.95
groupname ipmp2
e1000g0:1: flags=9040843
inet 10.47.2.77 netmask ffffffe0 broadcast 10.47.2.95
e1000g0:2: flags=9040842
inet 10.47.2.77 netmask ff000000 broadcast 10.255.255.255
e1000g1: flags=1000843
inet 10.47.2.82 netmask ffffffe0 broadcast 10.47.2.95
groupname ipmp1
e1000g1:1: flags=9040843
inet 10.47.2.83 netmask ffffffe0 broadcast 10.47.2.95
e1000g1:2: flags=9040842
inet 10.47.2.83 netmask ff000000 broadcast 10.255.255.255
e1000g2: flags=1000843
inet 10.47.2.11 netmask ffffffc0 broadcast 10.47.2.63
groupname ipmp3
e1000g2:1: flags=9040843
inet 10.47.2.12 netmask ffffffc0 broadcast 10.47.2.63
e1000g2:2: flags=9040842
inet 10.47.2.12 netmask ff000000 broadcast 10.255.255.255
e1000g3: flags=1000843
inet 3.208.169.203 netmask ffffffc0 broadcast 3.208.169.255
groupname ipmp0
e1000g3:1: flags=9040843
inet 3.208.169.204 netmask ffffffc0 broadcast 3.208.169.255
e1000g3:2: flags=9040842
inet 3.208.169.204 netmask ff000000 broadcast 3.255.255.255
nxge0: flags=69040843
inet 10.47.2.13 netmask ffffffc0 broadcast 10.47.2.63
groupname ipmp3
nxge1: flags=69040843
inet 3.208.169.205 netmask ffffffc0 broadcast 3.208.169.255
groupname ipmp0
nxge2: flags=69040843
inet 10.47.2.78 netmask ffffffe0 broadcast 10.47.2.95
groupname ipmp2
nxge3: flags=69040843
inet 10.47.2.84 netmask ffffffe0 broadcast 10.47.2.95
groupname ipmp1
From the above output its clear that On Ownserver01
e1000g0 is 10.47.2.76 and e1000g1 is 10.47.2.82 - For Private Interconnect
e1000g2 is 10.47.2.11 - For Shared Storage (NAS)
e1000g3 is 3.208.169.203 - For Public
On Ownserver02
e1000g0 is 10.47.2.79 and e1000g1 is 10.47.2.85 - For Private Interconnect
e1000g2 is 10.47.2.14 - For Shared Storage (NAS)
e1000g3 is 3.208.169.207 - For Public
Checked for SSH and SCP in /usr/local/bin/
The cluster verification utility(runcluvfy.sh ) checks for scp and ssh in /usr/local/bin/.
Create soft links of ssh and scp in /usr/local/bin/ if they are not there.
cd /usr/local/bin/
ls -l
lrwxrwxrwx 1 root root 12 Apr 25 16:57 /usr/local/bin/scp -> /usr/bin/scp
lrwxrwxrwx 1 root root 12 Apr 25 16:57 /usr/local/bin/ssh -> /usr/bin/ssh
Checked SSH equivalency between the nodes
ownserver01 [SHCL1DR1]$ ssh ownserver01 date
ssh_exchange_identification: Connection closed by remote host
ownserver01 [SHCL1DR1]$
mkdir ~/.ssh
chmod 700 ~/.ssh
/usr/bin/ssh-keygen -t rsa
usr/bin/ssh-keygen -t dsa
touch ~/.ssh/authorized_keys
ssh ownserver01 cat /home/oracle/.ssh/id_rsa.pub >>authorized_keys
Passowrd: *****
ssh ownserver01 cat /home/oracle/.ssh/id_dsa.pub >>authorized_keys
Password: *****
ssh ownserver02 cat /home/oracle/.ssh/id_rsa.pub >>authorized_keys
Password: *****
ssh ownserver02 cat /home/oracle/.ssh/id_dsa.pub>>authorized_keys
Password:*****
From Ownserver01
ownserver01 [SHCL1DR1]$ ssh OWNSERVER01 date
ownserver01 [SHCL1DR1]$ ssh OWNSERVER02 date
From Ownserver02
ownserver02 [SHCL1DR2]$ ssh OWNSERVER01 date
ownserver02 [SHCL1DR2]$ ssh OWNSERVER02 date
The time on both nodes were same at any time.
I made sure that from Ownserver01 i could SSH to ownserver02 and also to ownserver01 itself and the same from Ownserver02 too.
Note: also mv /etc/issue to /etc/issue.bak or user equivalence will fail – This clears the login banner. (this may be for Solaris only)
Checked for file /usr/lib/libdce.so [ Metalink Note Note:333348.1]
The 10gR2 installer on Soalris 64 bit fails if the file /usr/lib/libdce.so is present.
Check Metalink Note Note:333348.1 for the workaround.
Configure the .profile of user ORACLE
stty cs8 -istrip -parenb
PATH=/usr/bin:/usr/local/bin
EDITOR=/usr/bin/vi
#umask 077
umask 022
ulimit -c 0
export PATH EDITOR
set -o vi
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export OH=$ORACLE_HOME
export ORA_CRS_HOME=$ORACLE_BASE/product/crs
export CH=$ORA_CRS_HOME
export ORACLE_SID=SHCL1DR1
#export NLS_LANG=Japanese_Japan.UTF8
export NLS_LANG=AMERICAN_AMERICA.UTF8
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/sbin:/usr/bin:/usr/ccs/bin:/usr/ucb:/etc:/usr/X/bin:/usr/openwin/bin:/usr/local/bin:/usr/sbin
export PS1=`hostname`” [$ORACLE_SID]\$ “
####
alias bdump=’cd /u01/app/oracle/admin/SHCL1/bdump/’
alias talert=’tail -f $ORACLE_BASE/admin/SHCL1/bdump/alert_$ORACLE_SID.log’
alias tns=’cd $ORACLE_HOME/network/admin’
alias udump=’cd /u01/app/oracle/admin/SHCL1/bdump/’
alias valert=’view $ORACLE_BASE/admin/SHCL1/bdump/alert_$ORACLE_SID.log’
alias home=’cd $ORACLE_HOME’
Created the directories on both nodes
For ORACLE_BASE
mkdir -p /u01/app/oracle
chown -R ORACLE:oinstall /u01/app/oracle
chmod -R 770 /u01/app/oracle
For ORA_CRS_HOME
mkdir -p /u01/app/oracle/product/crs
chown -R root:oinstall /u01/app/oracle/product/crs
For ORACLE_HOME [RDBMS]
mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R ORACLE:oinstall /u01/app/oracle/product/10.2.0/db_1
For OCR and Voting disks
mkdir -p /u02/oracle/crs/
mkdir -p /u03/oracle/crs/
mkdir -p /u04/oracle/crs/
Check the privileges on the directories [should be ORACLE:oinstall]
Created OCR and Voting Disk files
In Linux, the oracle provided cluster file system OCSF2 is used on the shared disk,so ‘touch’ ing ocr_disk1 and vote_disk1would do.
But since i used NAS as the shared storage (which is mounted on each nodes), i had to create raw files for ocr and voting disk.
OCR
chown root:oinstall /u02/oracle/crs/ocr_disk1
chown root:oinstall /u03/oracle/crs/ocr_disk2
chmod 660 /u02/oracle/crs/ocr_disk1
chmod 660 /u03/oracle/crs/ocr_disk2
VOTING DISK
chown ORACLE:oinstall /u02/oracle/crs/vote_disk1
chown ORACLE:oinstall /u03/oracle/crs/vote_disk2
chown ORACLE:oinstall /u04/oracle/crs/vote_disk3
chmod 660 /u02/oracle/crs/vote_disk1
chmod 660 /u03/oracle/crs/vote_disk2
chmod 660 /u04/oracle/crs/vote_disk3
Downloaded and unzipped Oracle 10.2.0.1 installation files
10gr2_cluster_sol.cpio
10gr2_companion_sol.cpio
10gr2_db_sol.cpio
Run the Cluster Verification Utility available in
10gr2_cluster_sol.cpio
ownserver01 [SHCL1DR1]$ ./runcluvfy.sh stage -pre crsinst -n OWNSERVER01,OWNSERVER02 -verbose
Performing pre-checks for cluster services setup
Checking node reachability…
Check: Node reachability from node “ownserver01″
Destination Node Reachable?
———————————— ————————
OWNSERVER01 yes
OWNSERVER02 yes
Result: Node reachability check passed from node “ownserver01″.
Checking user equivalence…
Check: User equivalence for user “ORACLE”
Node Name Comment
———————————— ————————
OWNSERVER02 passed
OWNSERVER01 passed
Result: User equivalence check passed for user “ORACLE”.
Checking administrative privileges…
Check: Existence of user “ORACLE”
Node Name User Exists Comment
———— ———————— ————————
OWNSERVER02 yes passed
OWNSERVER01 yes passed
Result: User existence check passed for “ORACLE”.
Check: Existence of group “oinstall”
Node Name Status Group ID
———— ———————— ————————
OWNSERVER02 exists 300
OWNSERVER01 exists 300
Result: Group existence check passed for “oinstall”.
Check: Membership of user “ORACLE” in group “oinstall” [as Primary]
Node Name User Exists Group Exists User in Group Primary Comment
—————- ———— ———— ———— ———— ————
OWNSERVER02 yes yes yes yes passed
OWNSERVER01 yes yes yes yes passed
Result: Membership check for user “ORACLE” in group “oinstall” [as Primary] passed.
Administrative privileges check passed.
Checking node connectivity…
Interface information for node “OWNSERVER02″
Interface Name IP Address Subnet
—————————— —————————— —————-
e1000g0 10.47.2.79 10.47.2.64
e1000g0 10.47.2.80 10.47.2.64
e1000g0 10.47.2.80 10.0.0.0
e1000g1 10.47.2.85 10.47.2.64
e1000g1 10.47.2.86 10.47.2.64
e1000g1 10.47.2.86 10.0.0.0
e1000g2 10.47.2.14 10.47.2.0
e1000g2 10.47.2.15 10.47.2.0
e1000g2 10.47.2.15 10.0.0.0
e1000g3 3.208.169.207 3.208.169.192
e1000g3 3.208.169.208 3.208.169.192
e1000g3 3.208.169.208 3.0.0.0
nxge0 10.47.2.16 10.47.2.0
nxge1 3.208.169.209 3.208.169.192
nxge2 10.47.2.81 10.47.2.64
nxge3 10.47.2.87 10.47.2.64
Interface information for node “OWNSERVER01″
Interface Name IP Address Subnet
—————————— —————————— —————-
e1000g0 10.47.2.76 10.47.2.64
e1000g0 10.47.2.77 10.47.2.64
e1000g0 10.47.2.77 10.0.0.0
e1000g1 10.47.2.82 10.47.2.64
e1000g1 10.47.2.83 10.47.2.64
e1000g1 10.47.2.83 10.0.0.0
e1000g2 10.47.2.11 10.47.2.0
e1000g2 10.47.2.12 10.47.2.0
e1000g2 10.47.2.12 10.0.0.0
e1000g3 3.208.169.203 3.208.169.192
e1000g3 3.208.169.204 3.208.169.192
e1000g3 3.208.169.204 3.0.0.0
nxge0 10.47.2.13 10.47.2.0
nxge1 3.208.169.205 3.208.169.192
nxge2 10.47.2.78 10.47.2.64
nxge3 10.47.2.84 10.47.2.64
Check: Node connectivity of subnet “10.47.2.64″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g0 OWNSERVER02:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:nxge2 yes
OWNSERVER02:e1000g0 OWNSERVER02:nxge3 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:nxge2 yes
OWNSERVER02:e1000g0 OWNSERVER01:nxge3 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:nxge2 yes
OWNSERVER02:e1000g0 OWNSERVER02:nxge3 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:nxge2 yes
OWNSERVER02:e1000g0 OWNSERVER01:nxge3 yes
OWNSERVER02:e1000g1 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER02:nxge2 yes
OWNSERVER02:e1000g1 OWNSERVER02:nxge3 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:nxge2 yes
OWNSERVER02:e1000g1 OWNSERVER01:nxge3 yes
OWNSERVER02:e1000g1 OWNSERVER02:nxge2 yes
OWNSERVER02:e1000g1 OWNSERVER02:nxge3 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:nxge2 yes
OWNSERVER02:e1000g1 OWNSERVER01:nxge3 yes
OWNSERVER02:nxge2 OWNSERVER02:nxge3 yes
OWNSERVER02:nxge2 OWNSERVER01:e1000g0 yes
OWNSERVER02:nxge2 OWNSERVER01:e1000g0 yes
OWNSERVER02:nxge2 OWNSERVER01:e1000g1 yes
OWNSERVER02:nxge2 OWNSERVER01:e1000g1 yes
OWNSERVER02:nxge2 OWNSERVER01:nxge2 yes
OWNSERVER02:nxge2 OWNSERVER01:nxge3 yes
OWNSERVER02:nxge3 OWNSERVER01:e1000g0 yes
OWNSERVER02:nxge3 OWNSERVER01:e1000g0 yes
OWNSERVER02:nxge3 OWNSERVER01:e1000g1 yes
OWNSERVER02:nxge3 OWNSERVER01:e1000g1 yes
OWNSERVER02:nxge3 OWNSERVER01:nxge2 yes
OWNSERVER02:nxge3 OWNSERVER01:nxge3 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:nxge2 yes
OWNSERVER01:e1000g0 OWNSERVER01:nxge3 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:nxge2 yes
OWNSERVER01:e1000g0 OWNSERVER01:nxge3 yes
OWNSERVER01:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g1 OWNSERVER01:nxge2 yes
OWNSERVER01:e1000g1 OWNSERVER01:nxge3 yes
OWNSERVER01:e1000g1 OWNSERVER01:nxge2 yes
OWNSERVER01:e1000g1 OWNSERVER01:nxge3 yes
OWNSERVER01:nxge2 OWNSERVER01:nxge3 yes
Result: Node connectivity check passed for subnet “10.47.2.64″ with node(s) OWNSERVER02,OWNSERVER01.
Check: Node connectivity of subnet “10.0.0.0″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g0 OWNSERVER02:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER02:e1000g2 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g0 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g1 OWNSERVER02:e1000g2 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g1 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g0 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g1 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g1 yes
OWNSERVER01:e1000g0 OWNSERVER01:e1000g2 yes
OWNSERVER01:e1000g1 OWNSERVER01:e1000g2 yes
Result: Node connectivity check passed for subnet “10.0.0.0″ with node(s) OWNSERVER02,OWNSERVER01.
Check: Node connectivity of subnet “10.47.2.0″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g2 OWNSERVER02:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER02:nxge0 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:nxge0 yes
OWNSERVER02:e1000g2 OWNSERVER02:nxge0 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER02:e1000g2 OWNSERVER01:nxge0 yes
OWNSERVER02:nxge0 OWNSERVER01:e1000g2 yes
OWNSERVER02:nxge0 OWNSERVER01:e1000g2 yes
OWNSERVER02:nxge0 OWNSERVER01:nxge0 yes
OWNSERVER01:e1000g2 OWNSERVER01:e1000g2 yes
OWNSERVER01:e1000g2 OWNSERVER01:nxge0 yes
OWNSERVER01:e1000g2 OWNSERVER01:nxge0 yes
Result: Node connectivity check passed for subnet “10.47.2.0″ with node(s) OWNSERVER02,OWNSERVER01.
Check: Node connectivity of subnet “3.208.169.192″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g3 OWNSERVER02:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER02:nxge1 yes
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER01:nxge1 yes
OWNSERVER02:e1000g3 OWNSERVER02:nxge1 yes
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER02:e1000g3 OWNSERVER01:nxge1 yes
OWNSERVER02:nxge1 OWNSERVER01:e1000g3 yes
OWNSERVER02:nxge1 OWNSERVER01:e1000g3 yes
OWNSERVER02:nxge1 OWNSERVER01:nxge1 yes
OWNSERVER01:e1000g3 OWNSERVER01:e1000g3 yes
OWNSERVER01:e1000g3 OWNSERVER01:nxge1 yes
OWNSERVER01:e1000g3 OWNSERVER01:nxge1 yes
Result: Node connectivity check passed for subnet “3.208.169.192″ with node(s) OWNSERVER02,OWNSERVER01.
Check: Node connectivity of subnet “3.0.0.0″
Source Destination Connected?
—————————— —————————— —————-
OWNSERVER02:e1000g3 OWNSERVER01:e1000g3 yes
Result: Node connectivity check passed for subnet “3.0.0.0″ with node(s) OWNSERVER02,OWNSERVER01.
Suitable interfaces for VIP on subnet “3.208.169.192″:
OWNSERVER02 e1000g3:3.208.169.207 e1000g3:3.208.169.208
OWNSERVER01 e1000g3:3.208.169.203 e1000g3:3.208.169.204
Suitable interfaces for VIP on subnet “3.208.169.192″:
OWNSERVER02 nxge1:3.208.169.209
OWNSERVER01 nxge1:3.208.169.205
I ignored the last messages about the VIP, as i knew there wasn’t any problem.
Click “Add” to add the second node
Public Node Name :ownserver02
Private Node Name:ownserver02ipmp1
Virtual Host Name:ownserverv02
On Ownserver01
bash-3.00# /u01/app/oracle/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oracle/oraInventory to 770.
Changing groupname of /u01/app/oracle/oraInventory to oinstall.
The execution of the script is complete
bash-3.00# ls -l /u01/app/oracle/product/crs/root.sh
-rwxr-xr-x 1 ORACLE oinstall 105 Apr 30 11:14 /u01/app/oracle/product/crs/root.sh
bash-3.00# /u01/app/oracle/product/crs/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node
node 1: ownserver01 ownserver01ipmp1 ownserver01
node 2: ownserver02 ownserver02ipmp1 ownserver02
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Now formatting voting device: /u02/oracle/crs/vote_disk1
Now formatting voting device: /u03/oracle/crs/vote_disk2
Now formatting voting device: /u04/oracle/crs/vote_disk3
Format of 3 voting devices complete.
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
ownserver01
CSS is inactive on these nodes.
ownserver02
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.
bash-3.00# /u01/app/oracle/product/crs/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node
node 1: ownserver01 ownserver01ipmp1 ownserver01
node 2: ownserver02 ownserver02ipmp1 ownserver02
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
ownserver01
ownserver02
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps
Creating VIP application resource on (2) nodes…
Creating GSD application resource on (2) nodes…
Creating ONS application resource on (2) nodes…
Starting VIP application resource on (2) nodes…
Starting GSD application resource on (2) nodes…
Starting ONS application resource on (2) nodes…
Done.
Friday, April 25, 2008
Knowing when to Rebuild Indexes
Abstract
Indexes provide a fast and efficient method of retrieving selected data from a table. By pointing to the blocks that contain the selected data, the entire table does not have to read in order to extract the required information. Most indexes in Oracle databases are built using the B-tree data structure. Contrary to some widely accepted beliefs, indexes in Oracle are not self-balancing. After a table experiences a large number of inserts, updates, and deletes, the index can become unbalanced and fragmented and can hinder query performance. Knowing when to rebuild the indexes is a topic of some confusion. This paper hopes to shine some light on the subject.
Where is the index now?
In order to understand what we must do with the index, we must first get an idea of the current state of the index. This can be accomplished by using the ANALYZE INDEX VALIDATE STRUCTURE command. Normally, the ANALYZE INDEX command creates either computed or estimated statistics for the index that can be seen in the DBA_INDEXES view. Analyzing the index changes the optimizer’s execution plans for queries that potentially use that index. This action may produce unintentional side effects, especially if the index has not previously been analyzed. The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.
Below is an example of ANALYZE INDEX VALIDATE STRUCTURE and sample output from INDEX_STATS:
SQLWKS> ANALYZE INDEX shopping_basket_pk VALIDATE STRUCTURE;
Statement processed.
SQLWKS> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space
2> FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW DISTINCT_K USED_SPACE------------------------- --------- ---------- ---------- ---------- ---------- ----------SHOPPING_BASKET_PK 2 1 3 1 1 65
1 row selected.
I have the information, now what?
There are two rules of thumb to help determine if the index needs to be rebuilt. If it is determined that the index needs to be rebuilt, this can easily be accomplished by the ALTER INDEX REBUILD command. Although not necessarily recommended, this command could be executed during normal operating hours. Rebuilding the index uses the existing index as a basis. The alternative is to drop and re-create the index. Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation.
First rule of thumb is if the index has height greater than four, rebuild the index. For most indexes, the height of the index will be quite low, i.e. one or two. I have seen an index on a 3 million-row table that had height three. An index with height greater than four may need to be rebuilt as this might indicate a skewed tree structure. This can lead to unnecessary database block reads of the index. It is helpful to know the data structure for the table and index. Most times, the index height should be two or less, but there are exceptions.
The second rule of thumb is that the deleted leaf rows should be less than 20% of the total number of leaf rows. An excessive number of deleted leaf rows indicates that a high number of deletes or updates have occurred to the index column(s). The index should be rebuilt to better balance the tree. The INDEX_STATS table can be queried to determine if there are excessive deleted leaf rows in relation to the total number of leaf rows. Let’s look at an example:
SQLWKS> ANALYZE INDEX item_basket_pk VALIDATE STRUCTURE;
Statement processed.
SQLWKS> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio 2> FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO
------------------------------ ---------- ---------- ---------- ----------ITEM_BASKET_PK 1 235 74 31.4893617
1 row selected.
In this example, the ratio of deleted leaf rows to total leaf rows is clearly above 20%. This is a good candidate for rebuilding. Let’s rebuild the index and examine the results.
SQLWKS> ALTER INDEX item_basket_pk REBUILD;
Statement processed.
SQLWKS> ANALYZE INDEX item_basket_pk VALIDATE STRUCTURE;
Statement processed.
SQLWKS> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
2> FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO ------------------------------ ---------- ---------- ---------- ----------ITEM_BASKET_PK 1 161 0 0
1 row selected.
The index is rebuilt and validated once again. Examining the INDEX_STATS table shows that the 74 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 235 to 161, which is a difference of 74 leaf rows. This index should provide better performance for the application.
A Case Study
To illustrate the ideas in this paper, a test case was created and various queries were executed against a large table before and after rebuilding the primary key index. It should be noted that this test case was created specifically for this paper and results in the field may vary.
The test table only contains two columns, ID and NAME. The table is described below:
REQ SQL> desc analyze;
Name Null? Type
------------------------------- -------- ----
ID NUMBER NAME VARCHAR2(20)
The test table has a primary key, ANALYZE_PK on the ID field. The test table was populated with 1 million rows of data. After populating the table, over 270,000 rows were selectively deleted from the table. This method ensured that the primary key index would have a sufficient number of deleted leaf rows. After populating and deleting rows from the table, the index was validated as below:
REQ SQL> analyze index analyze_pk validate structure;
Index analyzed.
REQ SQL> select name,height,lf_rows,del_lf_rows,del_lf_rows/lf_rows from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS DEL_LF_ROWS/LF_ROWS------------------------------ ---------- ---------- ----------- -------------------ANALYZE_PK 3 990206 277353 .280096263
It is now clear that the deleted leaf rows comprise approximately 28% of the total leaf rows.
Four different queries were executed against the table. All of these queries will utilize the primary key index as verified by the explain plan. These queries are executed against the table with the current index. The index is then rebuilt. These queries are re-executed and the results are compared. The four queries are:
1. select count(*) from analyze where id between 250000 and 600000;
2. select count(*) from analyze where id in (50000,4321,698754);
3. select count(*) from analyze where id > 500000;
4. select count(*) from analyze where id = 4321;
With the current index, these three queries are executed against the test table. SQL Trace is enabled and the queries are shown in the following excerpt from TKPROF: ********************************************************************************
select count(*) from analyze where id between 250000 and 600000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.47 0.47 0 293 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.47 0.47 0 293 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
138614 INDEX (RANGE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id in (50000,4321,698754)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 9 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 9 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 CONCATENATION
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id > 500000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.11 1.11 0 1611 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.11 1.11 0 1611 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
445545 INDEX (RANGE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id = 4321
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
After these results have been obtained, the index is rebuilt. The index is then validated and the results are show below:
REQ SQL> alter index analyze_pk rebuild;
Index altered.
REQ SQL> analyze index analyze_pk validate structure;
Index analyzed.
REQ SQL> select name,height,lf_rows,del_lf_rows,del_lf_rows/lf_rows from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS DEL_LF_ROWS/LF_ROWS------------------------------ ---------- ---------- ----------- -------------------ANALYZE_PK 3 712853 0 0
It is clear that the deleted leaf rows have been removed from the index. The four queries are run against the table once again. Again, SQL Trace is enabled and the queries are shown in the following excerpt from TKPROF:
********************************************************************************
select count(*) from analyze where id between 250000 and 600000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.23 0.23 0 679 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.23 0.23 0 679 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
138614 INDEX (RANGE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id in (50000,4321,698754)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 9 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 9 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 CONCATENATION
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id > 500000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.96 0.96 0 933 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.96 0.96 0 933 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
445545 INDEX (RANGE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id = 4321
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
The same queries have now been executed against the test table before and after rebuilding the index. The results are summarized in the following table:
Before index rebuild After index rebuild % Change
CPU Elapsed CPU Elapsed CPU Elapsed
Query 1 0.47 0.47 0.23 0.23 51% 51%
Query 2 0.00 0.01 0.00 0.00 0% 0%
Query 3 1.11 1.11 0.96 0.96 13.5% 13.5%
Query 4 0.00 0.00 0.00 0.00 0% 0%
The results in the above table show that queries 1 and 3 experienced up to 51% improvement in execution time. It should be noted that the queries were executed at least once prior to tracing the queries so that the data would reside in the buffer cache and first time executions of the queries would not skew the results. This fact explains why the CPU and elapsed times for a query are nearly identical. The data was read from the buffer cache. Queries 2 and 4 took minimal time to execute. These queries searched for specific values. Since the data was cached, the CPU and Elapsed times are minimal. Queries 1 and 3 took significantly longer. These queries returned a range of values. This paper does not attempt to generate any findings on the improvement of different types of queries when rebuilding an index. Rather it gives a broader statement that query execution times will improve after rebuilding substandard indexes.
Script to find indexes to rebuild
Below is a sample script that can be run to determine which indexes need to be rebuilt. For those indexes that need to be rebuilt, the ALTER INDEX REBUILD command is dynamically generated as output. The user can tailor the height and percentage of deleted leaf rows by altering the vMaxHeight and vMaxDel variables. The output of this script can be spooled to a file. This file can then be run to rebuild the indexes.
-- validate_idx.sql
-- by Arun Mahendran
-- Database Administrator
-- 26 April 2008
--- This script will check indexes to find candidates for rebuilding.
-- Run this script in SQL*Plus as a user with SELECT ANY TABLE
-- privileges.
---- This script can be used and modified without permission. Run this
-- script at your own risk! The script author is not responsible for
-- any problems that may arise from running this script.
set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner
*/ vIdxName dba_indexes.index_name%TYPE; /* Index Name
*/ vAnalyze VARCHAR2(100); /* String of Analyze Stmt
*/ vCursor NUMBER; /* DBMS_SQL cursor
*/ vNumRows INTEGER; /* DBMS_SQL return rows
*/ vHeight index_stats.height%TYPE; /* Height of index tree
*/ vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows
*/ vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows
*/ vDLfPerc NUMBER; /* Del lf Percentage
*/ vMaxHeight NUMBER; /* Max tree height
*/ vMaxDel NUMBER; /* Max del lf percentage
*/
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' vOwner '.' vIdxName '
VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN
/* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' vOwner '.'
vIdxName ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
/
Summary
One of the areas lacking in Oracle documentation pertains to rebuilding an index. Oracle’s ANALYZE INDEX VALIDATE STRUCTURE command provides a nice way to check an index to see if it is a candidate for rebuilding. This command does not affect the Oracle optimizer’s execution plan for queries that may use the index. The results in the INDEX_STATS are checked after issuing the VALIDATE STRUCTURE command. If an index has excessive height (greater than four) or a high number of deleted leaf rows (over 20% of the total), we rebuild the index.
A test case was studied to show the potential for improvements after rebuilding an index. In one example, the query executed 51% faster after rebuilding the index. While this example is specific to the test case, it does show the potential for improvement after rebuilding an index.
Keywords:
Indexes provide a fast and efficient method of retrieving selected data from a table. By pointing to the blocks that contain the selected data, the entire table does not have to read in order to extract the required information. Most indexes in Oracle databases are built using the B-tree data structure. Contrary to some widely accepted beliefs, indexes in Oracle are not self-balancing. After a table experiences a large number of inserts, updates, and deletes, the index can become unbalanced and fragmented and can hinder query performance. Knowing when to rebuild the indexes is a topic of some confusion. This paper hopes to shine some light on the subject.
Where is the index now?
In order to understand what we must do with the index, we must first get an idea of the current state of the index. This can be accomplished by using the ANALYZE INDEX VALIDATE STRUCTURE command. Normally, the ANALYZE INDEX command creates either computed or estimated statistics for the index that can be seen in the DBA_INDEXES view. Analyzing the index changes the optimizer’s execution plans for queries that potentially use that index. This action may produce unintentional side effects, especially if the index has not previously been analyzed. The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.
Below is an example of ANALYZE INDEX VALIDATE STRUCTURE and sample output from INDEX_STATS:
SQLWKS> ANALYZE INDEX shopping_basket_pk VALIDATE STRUCTURE;
Statement processed.
SQLWKS> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space
2> FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW DISTINCT_K USED_SPACE------------------------- --------- ---------- ---------- ---------- ---------- ----------SHOPPING_BASKET_PK 2 1 3 1 1 65
1 row selected.
I have the information, now what?
There are two rules of thumb to help determine if the index needs to be rebuilt. If it is determined that the index needs to be rebuilt, this can easily be accomplished by the ALTER INDEX REBUILD command. Although not necessarily recommended, this command could be executed during normal operating hours. Rebuilding the index uses the existing index as a basis. The alternative is to drop and re-create the index. Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation.
First rule of thumb is if the index has height greater than four, rebuild the index. For most indexes, the height of the index will be quite low, i.e. one or two. I have seen an index on a 3 million-row table that had height three. An index with height greater than four may need to be rebuilt as this might indicate a skewed tree structure. This can lead to unnecessary database block reads of the index. It is helpful to know the data structure for the table and index. Most times, the index height should be two or less, but there are exceptions.
The second rule of thumb is that the deleted leaf rows should be less than 20% of the total number of leaf rows. An excessive number of deleted leaf rows indicates that a high number of deletes or updates have occurred to the index column(s). The index should be rebuilt to better balance the tree. The INDEX_STATS table can be queried to determine if there are excessive deleted leaf rows in relation to the total number of leaf rows. Let’s look at an example:
SQLWKS> ANALYZE INDEX item_basket_pk VALIDATE STRUCTURE;
Statement processed.
SQLWKS> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio 2> FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO
------------------------------ ---------- ---------- ---------- ----------ITEM_BASKET_PK 1 235 74 31.4893617
1 row selected.
In this example, the ratio of deleted leaf rows to total leaf rows is clearly above 20%. This is a good candidate for rebuilding. Let’s rebuild the index and examine the results.
SQLWKS> ALTER INDEX item_basket_pk REBUILD;
Statement processed.
SQLWKS> ANALYZE INDEX item_basket_pk VALIDATE STRUCTURE;
Statement processed.
SQLWKS> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
2> FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO ------------------------------ ---------- ---------- ---------- ----------ITEM_BASKET_PK 1 161 0 0
1 row selected.
The index is rebuilt and validated once again. Examining the INDEX_STATS table shows that the 74 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 235 to 161, which is a difference of 74 leaf rows. This index should provide better performance for the application.
A Case Study
To illustrate the ideas in this paper, a test case was created and various queries were executed against a large table before and after rebuilding the primary key index. It should be noted that this test case was created specifically for this paper and results in the field may vary.
The test table only contains two columns, ID and NAME. The table is described below:
REQ SQL> desc analyze;
Name Null? Type
------------------------------- -------- ----
ID NUMBER NAME VARCHAR2(20)
The test table has a primary key, ANALYZE_PK on the ID field. The test table was populated with 1 million rows of data. After populating the table, over 270,000 rows were selectively deleted from the table. This method ensured that the primary key index would have a sufficient number of deleted leaf rows. After populating and deleting rows from the table, the index was validated as below:
REQ SQL> analyze index analyze_pk validate structure;
Index analyzed.
REQ SQL> select name,height,lf_rows,del_lf_rows,del_lf_rows/lf_rows from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS DEL_LF_ROWS/LF_ROWS------------------------------ ---------- ---------- ----------- -------------------ANALYZE_PK 3 990206 277353 .280096263
It is now clear that the deleted leaf rows comprise approximately 28% of the total leaf rows.
Four different queries were executed against the table. All of these queries will utilize the primary key index as verified by the explain plan. These queries are executed against the table with the current index. The index is then rebuilt. These queries are re-executed and the results are compared. The four queries are:
1. select count(*) from analyze where id between 250000 and 600000;
2. select count(*) from analyze where id in (50000,4321,698754);
3. select count(*) from analyze where id > 500000;
4. select count(*) from analyze where id = 4321;
With the current index, these three queries are executed against the test table. SQL Trace is enabled and the queries are shown in the following excerpt from TKPROF: ********************************************************************************
select count(*) from analyze where id between 250000 and 600000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.47 0.47 0 293 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.47 0.47 0 293 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
138614 INDEX (RANGE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id in (50000,4321,698754)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 9 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 9 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 CONCATENATION
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id > 500000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.11 1.11 0 1611 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.11 1.11 0 1611 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
445545 INDEX (RANGE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id = 4321
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
After these results have been obtained, the index is rebuilt. The index is then validated and the results are show below:
REQ SQL> alter index analyze_pk rebuild;
Index altered.
REQ SQL> analyze index analyze_pk validate structure;
Index analyzed.
REQ SQL> select name,height,lf_rows,del_lf_rows,del_lf_rows/lf_rows from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS DEL_LF_ROWS/LF_ROWS------------------------------ ---------- ---------- ----------- -------------------ANALYZE_PK 3 712853 0 0
It is clear that the deleted leaf rows have been removed from the index. The four queries are run against the table once again. Again, SQL Trace is enabled and the queries are shown in the following excerpt from TKPROF:
********************************************************************************
select count(*) from analyze where id between 250000 and 600000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.23 0.23 0 679 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.23 0.23 0 679 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
138614 INDEX (RANGE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id in (50000,4321,698754)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 9 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 9 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 CONCATENATION
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id > 500000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.96 0.96 0 933 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.96 0.96 0 933 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
445545 INDEX (RANGE SCAN) OF 'ANALYZE_PK' (UNIQUE)
********************************************************************************
select count(*) from analyze where id = 4321
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSEParsing user id: 5 (SYSTEM)
Rows Execution Plan------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
1 INDEX (UNIQUE SCAN) OF 'ANALYZE_PK' (UNIQUE)
The same queries have now been executed against the test table before and after rebuilding the index. The results are summarized in the following table:
Before index rebuild After index rebuild % Change
CPU Elapsed CPU Elapsed CPU Elapsed
Query 1 0.47 0.47 0.23 0.23 51% 51%
Query 2 0.00 0.01 0.00 0.00 0% 0%
Query 3 1.11 1.11 0.96 0.96 13.5% 13.5%
Query 4 0.00 0.00 0.00 0.00 0% 0%
The results in the above table show that queries 1 and 3 experienced up to 51% improvement in execution time. It should be noted that the queries were executed at least once prior to tracing the queries so that the data would reside in the buffer cache and first time executions of the queries would not skew the results. This fact explains why the CPU and elapsed times for a query are nearly identical. The data was read from the buffer cache. Queries 2 and 4 took minimal time to execute. These queries searched for specific values. Since the data was cached, the CPU and Elapsed times are minimal. Queries 1 and 3 took significantly longer. These queries returned a range of values. This paper does not attempt to generate any findings on the improvement of different types of queries when rebuilding an index. Rather it gives a broader statement that query execution times will improve after rebuilding substandard indexes.
Script to find indexes to rebuild
Below is a sample script that can be run to determine which indexes need to be rebuilt. For those indexes that need to be rebuilt, the ALTER INDEX REBUILD command is dynamically generated as output. The user can tailor the height and percentage of deleted leaf rows by altering the vMaxHeight and vMaxDel variables. The output of this script can be spooled to a file. This file can then be run to rebuild the indexes.
-- validate_idx.sql
-- by Arun Mahendran
-- Database Administrator
-- 26 April 2008
--- This script will check indexes to find candidates for rebuilding.
-- Run this script in SQL*Plus as a user with SELECT ANY TABLE
-- privileges.
---- This script can be used and modified without permission. Run this
-- script at your own risk! The script author is not responsible for
-- any problems that may arise from running this script.
set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner
*/ vIdxName dba_indexes.index_name%TYPE; /* Index Name
*/ vAnalyze VARCHAR2(100); /* String of Analyze Stmt
*/ vCursor NUMBER; /* DBMS_SQL cursor
*/ vNumRows INTEGER; /* DBMS_SQL return rows
*/ vHeight index_stats.height%TYPE; /* Height of index tree
*/ vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows
*/ vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows
*/ vDLfPerc NUMBER; /* Del lf Percentage
*/ vMaxHeight NUMBER; /* Max tree height
*/ vMaxDel NUMBER; /* Max del lf percentage
*/
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' vOwner '.' vIdxName '
VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN
/* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' vOwner '.'
vIdxName ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
/
Summary
One of the areas lacking in Oracle documentation pertains to rebuilding an index. Oracle’s ANALYZE INDEX VALIDATE STRUCTURE command provides a nice way to check an index to see if it is a candidate for rebuilding. This command does not affect the Oracle optimizer’s execution plan for queries that may use the index. The results in the INDEX_STATS are checked after issuing the VALIDATE STRUCTURE command. If an index has excessive height (greater than four) or a high number of deleted leaf rows (over 20% of the total), we rebuild the index.
A test case was studied to show the potential for improvements after rebuilding an index. In one example, the query executed 51% faster after rebuilding the index. While this example is specific to the test case, it does show the potential for improvement after rebuilding an index.
Keywords:
Index Index Rebuild Rebuild Index Online ALTER INDEX REBUILD ANALYZE INDEX VALIDATE STRUCTURE ANALYZE INDEX LF_ROWS DEL_LF_ROW
Subscribe to:
Posts (Atom)