ORA-00600 collection

Another ORA-00600 error:

This error was recently reported to me,

ORA-00600: internal error code, arguments: [kgiinb_invalid_obj], [0x102BE8B38],
[0x12CF3EED8], [], [], [], [], [], [], [], [], []at
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode,
OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object
src, String procedure ….

Oracle Server – Enterprise Edition – Version: 11.1.0.7
This problem can occur on any platform.
Oracle Server Enterprise Edition – Version: 11.1.0.7

After some research I found that basically this is due to a bug (7420394) which can be fixed by applying the patch for it, or upgrading to 11.2.0.

ORA-00700: soft internal error, arguments: [kesqsMakeSql-invstat:cpuTime], [], [], [], [], [], [], [
] … hmmm … it looks impressive, overall considering that an ORA-00600 is not one that probably is going to make your DBA day.

Actually it is not as tragic and epic as it could seem to be at first glance, it has to do with a bug. In this particular case it showed up in an Oracle 11g Rel.1 (11.1.0.7.0) version on a x64 windows platform. This error is triggered when the SYS_AUTO_SQL_TUNING_TASK runs, and it may spontaneously show up and keep on showing up for a while. It may happen to be annoying.

The root reason is the Bug 7025700 , and some other related bugs, Bug 7757533, Bug 8224438, Bug 7643188, the arguments of the ORA-00700 may be slightly different: [KESQSMAKESQL-INVSTAT:CPUTIME], or [KESQSMAKESQL-INVSTAT:ELPSTIME], and the solution is to apply the patch 7643188 or apply the PSU patch to upgrade to 11.1.0.7.2.

Applying the patch will fix the problem from occurring in the future, but it won’t fix the current corrupted data at the internal statistics tables, but eventually those corrupt rows will be flushed away, so in the mean time you’ll have jut to get used to acknowledge it and manually clear the errrors at your E.M. Console.

here are some ORA-600 which can be easily googled, and there are others which are pretty difficult to find. This is the case. M.O.S reports this ORA600 to be related to a bug (9929660) on the 11.2 platform and which is fixed on 12.0.

Recently this error showed up on a Windows 2003 platform with Oracle 11.1.0.7.0. After deciphering the almost cryptic related trace and dump file and diagnosing after the possible environment causes. I found out this error was triggered at one of the internal performance maintenance task, and the root cause was related to a lack of processes in the database.

This error was fixed in this particular case and platform by increasing the value of the PROCESSES instance parameter.

The only one who has the authority to decode and understand the trace files related to the Oracle internals is Oracle support … unless you are willing to take the quest of reading them and trying to understand them as your last resource.

The information provided in this post is only for information purposes, and to share a very particular experience in my local environment. The only one authorized to provide a diagnose about an ORA-00600 is Oracle Support Services.

ORA-00600 [ARG1] [ARG2] [ARG3] [ARG4] [ARG5]
This is the kind of things a production DBA does not want to read at the alert.log file, particularly when the production database availability is compromised. At this time the only thing we would like to have is a magic wand that could make this dreaded ORA600 errors magically disappear. The truth is that so far there is no such magic wand and we all have to go through a troubleshooting procedure.

This is not a troubleshooting guide, and if you are reading this now it means either you are following my blog or you are googling after a problem you currently face and you have no clue on what is going on.

The basic troubleshooting procedure
Make sure you are at the latest patchset available for your current installed Oracle Home.
Collect and pack all the trace, and log files and raise a Service Request (SR) at Oracle Support Services (OSS).

In the mean time, try to read through the quasi-encrypted text trace and log files to find out if there is a known pattern, a readable entry point function, a particular environmental circumstance that precluded the ORA600, any thing that can help you out answer to the very basic question … What’s the root problem?

In my particular environment I had an ORA-00600 Stellium (a group of planets that from the observer’s perspective they are linked together in a series of continuous conjunctions) It was a cascade of ORA-00600 errors that used to periodically flood my alert.log, trace files and incident library.

The ORA-00600 Stellium
ORA-00600 [KKPAMRFGET0]
ORA-00600 [kkdcacr: ptn_kxcp]
ORA-00600 [qerrmOFbu:invalid rowcount]
ORA-00600 [kkpamRFGet()+369]

The ORA-07445 companions
ORA-07445: exception encountered: core dump [kxccexi()+1219] [ACCESS_VIOLATION] [ADDR:0xC45] [PC:0x263EFAF] [UNABLE_TO_READ] [].

The technical environment
Oracle 11g Rel. 1 (11.1.0.6.0)
MS Windows 2003 x64 R2 Server 5.2 SP 2
PowerEdge 2950
4 CPU’s
16 Gb RAM

Symptoms
The pattern found was, during a huge transactional activity on several partitioned tables containing spatial data columns suddenly all connections were broken and in order to continue with the production environment a the Oracle instance had to be restarted.

There was no specific time for this error to show up, it could be at night, at noon, on weekends, so there was actually not a specific time pattern. The key to solve this issue lied on the fact that there was too much transactional activity. After digging into the trace files I found out that there were memory issues, not related with the amount, but rather with the allocation. After the Oracle instance was running for a while (several days) on a stressful transactional activity it started to cause instability withe the memory allocation policies.

The SR Feedback
So far OSS told us the recurrence of the Ora600 errors documented at the production environment are due to a bug not yet identified so far by Oracle. The only tasks that can be done so far are to upgrade to 11.1.7.0 and wait for a patch that would be released on May 2010 that probably fixes this issue. The main argument OSS provided was the fact that the error could not be reproduced on their environment.

At this point Oracle does not have a patch or a direct procedure to avoid the kind of ORA600 errors seen at the production environment. The notes and white papers documented so far point to some issues found on the Intel x64 platform based environments. So it can happen both at Linux or Windows.

Troubleshooting
The troubleshooting path I followed in this case was:

• Compile and pack the facts (trace and log files in an incident pack)
• Raise a Service Request Informing Oracle about the issues
• Track with the assigned Oracle Support Analyst
• Downgrade the memory parameters behavior from 11g to 10g
• Upgrade from 11.1.0.6.0 to 11.1.0.7.0
• Upgrade Client Machines
• Upgrade each client machine where an oracle client is installed
• Upgrade the middle tier client that connects to the Oracle database.
• Revert back the Oracle 11g memory allocation policy to 10g.

Three key factors
After struggling for months with these issues I was able to identify three key factors:
Upgrade to 11.1.0.7.0. Upgrading provides three advantageous points, first you don’t have to wait for OSS to ask for an upgrade when raising a SR, second you prevent the root cause to be due after some known bugs, and third it is part of the best practices.
Revert back Memory allocation policy to 10g. 11g introduced a new instance parameter memory_target, which defines the total SGA + PGA memory allocated for the current instance, this way you don’t have to worry about the way memory is individually distributed among the different memory component, meanwhile in 10g there were two different parameters, one named sga_target and the second pga_aggregate_target, this divided the memory allocation in two parts. In my personal belief I am strongly convinced that there was an issue with this new allocation policy on this particular Windows 2003 x64 platform. 11g for windows is the last port released by Oracle Corp., not too many big companies use Windows as the main production O.S. platform, and the new features have to go through a testing that sometimes cannot thoroughly reproduce a heavy weight production environment, so it is very likely that at the extreme conditions systems are sometimes exposed a very hidden in the deep internals bug is hit. This was the case in this environment, so I suggested to revert back to the 10g memory allocation policy.
Rewrite the application. Leaning on the original development team is great, you can manipulate the source code and have it tailored in case it is required. In this case there were some applications that wildly hit the production environment with a huge amount of transactions. In this particular point the credit goes to Anil R. who knew exactly how to rewrite the code that was still triggering an ORA-07455 error after the DB was upgraded.

After one month of activity no errors have shown up, and even though O.S.S. was not able to issue a conclusive reason why these errors showed up, the troubleshooting procedure was so far successful.

Conclusion
An ORA-00600 is still an error that lies within the Oracle jurisdiction, don’t try to rewrite the oracle.exe code in case this kind of errors show up, but at least try to do your very best to abstract the 600 Black Box by identifying the key factors that trigger the error, and reading through the log and trace files. Answer the question Why this error showed up? and absolutely the very first troubleshooting point, raise a Service Request at Oracle Support Services.

An ORA-00600 is something I don’t like to see, particularly when I am in the just in time. And it is particularly boring when it is displayed once per each datafile to be renamed.

The Error
ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [], [], []
This error shows up under this circumstances:

The Environment

1. RDBMS version 10.2.0.4.0
2. Any Platform
3. Oracle DB Restore from an RMAN backup at the Standby Site
4. The original Database is in ASM.
5. The target Database is in File System.

Diagnostics
While googling i found a recommendation that suggsted to either use ASM or File System, but not both. In my case this is not an Option (and in my personal opinion not a good answer either), my primary site is a Real Application Cluster on Linux x64, so ASM is mandatory since we have no Cluster file system on the primary, and the stanby site is a File System based site.
According to Oracle Support Servivcivec they declared this as a fixable Bug.

Bug 7207932 OERI [kgeade_is_0] when renaming a file from ASM to a filesystem

The funny part of the diagnostics
The regular procedure to have an ORA600 error fixed is by diagnosing this at at the alert.log file, and by querying it at the ORA600 error lookup tool from Oracle Support Services; however, while using this last one to diagnose i found out that it didn’t recognize the first argument as a valid argument.

The Correcting Procedure
There are basically two ways to have this problem solved on the standby site, and it is by applying a patch that fixes this issue, the other one is to upgrade to 10.2.0.5.0 or greater, where this bug has already been fixed.

References

• ORA-00600 [KGEADE_IS_0] When Renaming A File From ASM TO FS [ID 742289.1]
• Bug 7207932 – OERI [kgeade_is_0] when renaming a file from ASM to a filesystem [ID 7207932.8]

 

 

Tuning: How to determine an index needs to be rebuild

Summary

An Oracle server index is a schema object that can speed up the retrieval of rows by using a pointer.

You can create indexes on one or more columns of a table to speed SQL statement execution on that table. If you do not have an index on the column, then a full table scan occurs.

You can reduce disk I/O by using a rapid path access method to locate data quickly. By default, Oracle creates B-tree indexes.

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 Indexes

We must first get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command.

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 a sample output from INDEX_STATS Table.

SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM
INDEX_STATS;
NAME                      HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROW
---------------------- -----------   ----------      ----------   ----------------
DX_GAM_ACCT           2             1                     3               6

1 row selected.

There are two rules of thumb to help determine if the index needs to be rebuilt.

1)     If the index has height greater than four, rebuild the index.

2)     The deleted leaf rows should be less than 20%.

If it is determined that the index needs to be rebuilt, this can easily be accomplished by the ALTER INDEX <INDEX_NAME> REBUILD | REBULID ONLINE command. It is not recommended, this command could be executed during normal operating hours. 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.

In this example, the HEIGH column is clearly showing the value 2. This is not a good candidate for rebuilding. For most indexes, the height of the index will be quite low, i.e. one or two. I have seen an index on a 2 million-row table that had height two or 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. Let’s take another example.

SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)
*100 as ratio FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- -------
IDX_GAM_FID                                  1          189         62        32.80

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

SQL> ANALYZE INDEX IDX_GAM_FID REBUILD;

Statement processed.

SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*
100 as ratio FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- -------
IDX_GAM_FID                                  1          127         0        0

1 row selected.

Examining the INDEX_STATS table shows that the 62 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 189 to 127, which is a difference of 62 leaf rows (189-127). This index should provide better performance for the application.

 

Imp/Exp Oracle Database Using Datapump Utility

INTRODUCTION

Oracle Data pump utility enables very fast bulk data and metadata movement between Oracle databases. It provides parallel Export and Import utilities (expdp and impdp) and Web-based Oracle Enterprise Manager Interface.

1. Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import.
2. Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.
3. Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.
4. Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export).

DATA PUMP ARCHITECTURE

Data Pump Components

 Data Pump provides four client utilities that execute the procedures provided in the DBMS_DATA_PUMP PL/SQL package. The client utilities accept input in the form of parameters which are passed to the DBMS_DATA_PUMP PL/SQL package to enable the exporting and importing of metadata and data into and out of an Oracle database.

A brief overview of the four client utilities is provided below:

1. EXPDP – Client interface to the Data Pump Export utility. The interface closely resembles its predecessor, Oracle Export.
2. IMPDP – Client interface to the Data Pump Import utility. The interface closely resembles its predecessor, Oracle Import.
3. Grid Control Interface – This is a web based interface, which provides access to the DBMS_DATA_PUMP PL/SQL package.
4. Custom Interface – Allows for the movement of complex data mining models.

Other Data pump components are described below.

Data Access Methods

Data Pump supports two access methods provided by Oracle Database 10g to load and unload data:

Direct Path and External Tables.

The Direct Path access method is the faster of the two, but does not support intrapartition parallelism. The External Tables access method does support this function, and therefore may be chosen to load or unload a very large table or partition

For example, a table being loaded with active referential constraints or global indexes must be loaded using the External Tables access method. A table with a column of data type LONG must be unloaded and loaded with the Direct Path access method. In most cases, we need not be concerned about choosing an access method; the Data Pump job will make the correct choice based on an array of job characteristics. Both methods write to the dumpfile set in a compact, binary stream format that is approximately 15 percent smaller than the original exp data representation.

Meta data API

Data Pump uses the DBMS_METADATA package to load and unload metadata. The Metadata API stores the metadata as XML documents. This differs from the original Export utility which stored the metadata as DDL. The Data Pump Import utility takes advantage of the flexibility provided by the XML format by allowing users to change object ownership, storage characteristics and tablespace destinations.

Process Structure

There are various processes that comprise a Data Pump job. They are described in the order of their creation.

Client Process – This is the process that makes calls to the Data Pump API. Oracle Database10g ships four client utilities of this API. This paper will discuss only the new export/import clients, expdp and impdp. Data Pump is integrated into Oracle Database 10g, a client is not required once a job is underway. Multiple clients may attach and detach from a job as necessary for monitoring and control.

Shadow Process – This is the standard Oracle shadow (or foreground) process created when a client logs in to Oracle Database. Upon receipt of a DBMS_DATAPUMP.OPEN request, the shadow process creates the job, which consists primarily of creating the master table, the Advanced Queuing (AQ) queues used for communication among the various processes, and the master Control Process. Once a job is running, the main task of the shadow process consists of servicing GET_STATUS requests from the client. If the client detaches, the shadow process also goes away.

Master Control Process (MCP) – As the name implies, the MCP controls the execution and sequencing of a Data Pump job. There is one MCP per Data Pump job, maintaining the job state, job description, restart, and dumpfile information in the master table. A job is divided into various phases of metadata and data unloading or loading, and the MCP hands out work requests to the worker processes appropriate for the current phase.

Worker Process – Upon receipt of a START_JOB request, the MCP creates a number of worker processes according to the value of the PARALLEL parameter. The worker processes perform the tasks requested by the MCP (primarily unloading and loading of metadata and data), and maintain the object rows that make up the bulk of the master table. As database objects are unloaded or loaded, these rows are written and updated with the current status of these objects: pending, completed, failed, and so on. The worker processes also maintain type completion rows, which describe the type of object currently being worked on: tables, indexes, views, and so on

Parallel Query (PQ) Process – If the External Tables data access method is chosen for loading or unloading a table or partition, some parallel query processes are created by the worker process that was given the load or unload assignment, and the worker process then acts as the query coordinator. These are standard parallel execution slaves that exploit the parallel execution architecture of Oracle Database, and enable intrapartition loading and unloading. In Real Application Clusters (RAC), parallel query processes may be created on an instance other than that on which the Data Pump job was initiated.

Status Queue

Oracle takes advantage of its Advanced Queuing (AQ) feature to provide communication paths between the different Data Pump processes. The Data Pump master control process writes work progress and error messages to the status queue. Shadow processes subscribe to this queue to retrieve the status information.

Master Table

The master table contains information about the details of the current Data Pump operation being performed.  the master table also records the parameters supplied by the calling program, worker status information and output file information. The table provides all of the information required to restart a Data Pump job that has stopped because of a planned, or unplanned, failure.

Command and Control Queue

The command and control queue provides an avenue of communications between the master control process and worker processes. All of the work requests created by the master process and the associated responses are passed through the command and control queue.

Dump Data file

Data Pump dump files are created by EXPDP and used as input by IMPDP. Since their format is incompatible with their predecessors, Export and Import, the utilities are not interchangeable. Import can only read a file created by Export and IMPDP can only read a file created by EXPDP.

The dumpfile contains data and/or metadata and the master table which is written to the dumpfile at the end of each EXPDP operation and read at the beginning of each IMPDP operation.

EXPORT/IMPORT USING DATA PUMP

Data Pump consists of two components: the Data Pump export utility called “expdp,” to Export the objects from a database, and the Data Pump Import utility called “impdp,” to load the objects into database.

The expdp and impdp tools support all the original exp and imp functionalities plus many new features. With previous release, we could only move the transportable tablespace across Oracle databases that were running on the same architecture and operating system. With Data Pump, you are able to transport data files from one platform to another.

Data Pump is a server-based utility, rather than client-based; dump files, log files, and SQL files are accessed relative to server-based directory paths.

Data Pump runs as a job inside the database, rather than as a stand-alone client application. This means that jobs are somewhat independent of the process that started the export or import. One machine (say a scheduled job) could start the export, while another machine (such as a DBA’s laptop) can check the status of the job. Since the job is inside the database, if you want to export to a file, the first thing that we must do is create a database DIRECTORY object for the output directory, and grant access to users who will be doing exports and imports.

New Concepts in Oracle Data Pump

There are two new concepts in Oracle Data Pump that are different from original Export and Import.

Directory Objects

 Data Pump differs from original Export and Import in that all jobs run primarily on the server using server processes. These server processes access files for the Data Pump jobs using directory objects that identify the location of the files.

Interactive Command-Line Mode

 Besides regular operating system command-line mode, there is now a very powerful interactive command-line mode which allows the user to monitor and control Data Pump Export and Import operations.

Changing From Original Export/Import To Oracle Data Pump

Creating Directory Objects

In order to use Data Pump, the database administrator must create a directory object and grant privileges to the user on that directory object. If a directory object is not specified, a default directory object called data_pump_dir is provided. The default Data_pump_dir is available only to privileged users unless access is granted by the DBA.

In the following example, the following SQL statement creates a directory object named test_dir that is mapped to a directory located at ‘/u01/app/oracle/oradata/’;

We would login to SQL*Plus as system and enter the following SQL command to create a directory. Before creating the directory object we have to unlock the SCOTT account and create a directory object it can access:

CONN sys/password@db10g AS SYSDBA

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

GRANT CREATE ANY DIRECTORY TO scott;

CREATE OR REPLACE DIRECTORY test_dir AS ‘/u01/app/oracle/oradata/’;

 After a directory is created, we need to grant READ and WRITE permission on the directory to other users. For example, to allow the Oracle database to read and to write to files on behalf of user scott in the directory named by test_dir, you must execute the following command:

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Note that READ or WRITE permission to a directory object means only that the Oracle database will read or write that file on your behalf. We are not given direct access to those files outside of the Oracle database unless we have the appropriate operating system privileges. Similarly, the Oracle database requires permission from the operating system to read and write files in the directories.

Once the directory is granted, we can export a user’s object with command arguments that are very similar to exp and imp:

$expdp scott/tiger directory=test_dir dumpfile=scott.dmp

While the export job is running, we can press [Ctrl]C (or the equivalent on your client) to “detach” from the export job. The messages will stop coming to the client, but it’s still running inside the database. The client will be placed in an interactive mode (with Export> prompt). To see which jobs are running, type status. If we run expdp attach=<jobname>, we can attach to a running job.

Expdp scott/tiger directory=dumpdir dumpfile=scott2.dmp job_name=scott2

 Parallelism

Data Pump is much faster than the old exp and imp client commands. One new feature that really helps make it faster is the “parallel” option. With this option, the Data Pump will pump data in four different threads.

Data pump performance can be significantly improved by using the PARALLEL parameter. This should be used in conjunction with the “%U” wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read.

$expdp scott/tiger directory=dumpdir dumpfile=scott2.dmp parallel=4 job_name=scott2

 $Impdp dpuser/dpuser@TDB10G  schemas=dbuser  directory=dumpdir dumpfile=expdp_TDB10G_dbuser.200470215_07.%U.dmp parallel=4

 Other Useful Features

TABLE_EXISTS_ACTION – The original imp would allow rows to be appended to existing tables if IGNORE=Y was specified. The TABLE_EXISTS_ACTION parameter for Data Pump impdp provides four options:

1. SKIP is the default: A table is skipped if it already exists.

2. APPEND will append rows if the target table’s geometry is compatible.

3. TRUNCATE will truncate the table, then load rows from the source if the geometries     are compatible and truncation is possible. For example, it is not possible to truncate a table if it is the target of referential constraints.

4. REPLACE will drop the existing table, then create and load it from the source.

CONTENT – This new parameter, applicable to both clients, allows the movement of DATA_ONLY, METADATA_ONLY, or BOTH (the default).

VERSION – expdp supports the VERSION parameter, which tells the server-based Data Pump to generate a dumpfile set compatible with the specified version. This will be used to perform downgrades in the future.

DATA PUMP VIEWS – Data Pump maintains a number of user- and DBA-accessible views to monitor the progress of jobs:

DBA_DATAPUMP_JOBS: This shows a summary of all active Data Pump jobs on the system.

USER_DATAPUMP_JOBS: This shows a summary of the current user’s active Data Pump jobs.

DBA_DATAPUMP_SESSIONS: This shows all sessions currently attached to Data Pump jobs.

V$SESSION_LONGOPS: A row is maintained in the view showing progress on each active Data Pump job. The OPNAME column displays the Data Pump job name.

Comparison Of Command-Line Parameters From Original Export and Import To Data Pump

Below are a few examples that demonstrate some of these differences.

Example, import of tables from scott’s account to jim’s account

 Original Import:

> imp username/password FILE=scott.dmp FROMUSER=scott TOUSER=jim TABLES=(*) COMMIT=y COMPILE=y CONSTRAINTS=y IGNORE=y STATISTICS=none LOG=importscott.log

 Data Pump Import:

> impdp username/password DIRECTORY=dpump_dir1 DUMPFILE=scott.dmp TABLES=scott.emp REMAP_SCHEMA=scott:jim

In Data pump utility  the FROMUSER/TOUSER syntax is replaced by the REMAP_SCHEMA Option.

Example, export of an entire database to a dump files with all GRANTS, INDEXES, and data

 > exp username/password FULL=y FILE=dba.dmp GRANTS=y INDEXES=y ROWS=y LOG=exportdba.log

> expdp username/password FULL=y INCLUDE=GRANT INCLUDE= INDEX DIRECTORY=dpump_dir1 DUMPFILE=dba.dmp CONTENT=ALL

Data Pump offers much greater metadata filtering than Original Export and Import. The INCLUDE parameter allows us to specify which object (and its dependent objects) you want to keep in the export job. The EXCLUDE parameter allows you to specify which object (and its dependent objects) you want to keep out of the export job. You cannot mix the two parameters in one job. Both parameters work with Data Pump Import as well, and you can use different INCLUDE and EXCLUDE options for different operations on the same dump file. We can get a quick summary of all parameters and commands by simply issuing

$expdp help=y

$impdp help=y

Following are few EXPDP and IMPDP commands with additional attributes.

$ expdp dpuser/dpuser@TDB10G schemas=dpuser include= TABLE:\”IN (\’EMP\’, \’DEPT\’)\”  directory=dpump_dir1 dumpfile=dpuser.dmp log=dpuser.log

 $expdp dpuser/dpuser@TDB10G schemas=dpuser exclude=TABLE:\”= \’EMP_DETAILS\’\”  directory=dpump_dir1 dumpfile=dpuser2.dmp logfile=dpuser.log

 $Impdp dpuser/dpuser@TDB10G  schemas=dbuser  directory=dumpdir dumpfile=expdp_TDB10G_dbuser.200470215_07.%U.dmp parallel=4

 $impdp dpuser/dpuser@TDB10G  schemas=dbuser dumpfile=DATA_PUMP_DIR:expdp_TDB10G_dbuser.200507_165936.%U.dmp logfile= DATA_PUMP_DIR:expdp_TDB10G_dbuser.200507_165936.out

 $expdp dpuser/dpuser@TDB10G schemas=dpuser   directory=dpump_dir1 parallel=4 dumpfile=dpuser_%U.dmp logfile=dpuser.log

 CREATING ORACLE DATABASE USING IMPDP and EXPDP

Steps for creating the Oracle database:

 Before creating the database we have to take Backup/ Export the entire existing Schema.

Syntax: > expdp username/password FULL=y INCLUDE=GRANT INCLUDE= INDEX   DIRECTORY=dpump_dir1 DUMPFILE=dba.dmp CONTENT=ALL

Delete Database – Drops all objects and public synonyms for objects owned by the schema

Create the Database again using the Oracle Configuration Assistant and clone of deleted database.

Connect to the Oracle Database using system user and sysdba user and runs scripts for tablespace creation, creation of users – roles, granting of privileges.

Syntax:

create tablespace Tablespace_name

datafile ‘/&oradata_dir/oradata/&orasid/tablespace_name_datafile01.dbf’ size 24M

 autoextend on next 24M maxsize 15000M

extent management local

segment space management auto

/

Create the dump directory using CREATE OR REPLACE DIRECTORY (dump_dir1) and grants read-write priviledges to dump directory object for the user.

The following SQL statements creates a user, a directory object named dpump_dir1 and     grants the permissions to the user.

 Syntax:

SQLPLUS system/manager@TDB10G as sysdba

SQL> create user dpuser identified by dpuser;

SQL> grant connect, resource to dpuser;

SQL> CREATE DIRECTORY dpump_dir1 AS ‘/opt/app/oracle’;

SQL> grant read, write on directory dpump_dir1 to dpuser

Connect to the newly created schema to test the connection.

SQL> dpuser/ dpuser@TDB10G;

Run the IMPDP command from the command prompt

 Syntax:

 

$impdp dpuser/dpuser@TDB10G schemas=dbuser dumpfile=DATA_PUMP_DIR:expdp_TDB10G_dbuser.200507_165936.%U.dmp

logfile= DATA_PUMP_DIR:expdp_TDB10G_dbuser.200507_165936.out

 

Verify the log file for errors or discrepancies.

We need to copy the .DMP files in default database directory (DATA_PUMP_DIR) path.

I:\oracle\product\10.2.0\admin\TDB10G\dpdump\

We can also place these .DMP files in database directory path where we have created.

Table Level Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

 

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Level Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Level Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

pdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

CONCLUSION

Oracle Data Pump is a great tool for the fast movement of data between the databases and much of this performance improvement is derived from the use of parameter “parallelism.”  New export and import clients, expdp and impdp, that fully exploit the Data Pump Infrastructure. They are implemented as complete supersets of the original exp and imp, and will eventually replace them.

 

Oracle RAC Part IV: Install Oracle Clusterware and Cluster Database

In this part we will install oracle clusterware and rac database

Copy Oracle RAC and Oracle Database Software to RAC1

Create a directory software in $HOME and copy clusterware and database zip files downloaded

unzip 10201_clusterware_linux32.zip
unzip 10201_database_linux32.zip

This will create directory clusterware (for clusterware software) and database (for database software)

Verify all prereq. for clusterware installation
————————————————————

cd /home/oracle/softwar/clusterware/cluvfy

Make sure
–Second Machine/Node RAC2 is up
– You have configured and changes hostname and network IP of RAC2
– RAC1 can ping RAC2 and vice versa
– User equivalency is set for user oracle on RAC1 and RAC2
– You can connect to rac2 from rac1 as user oracle without prompting password and vice versa

./runcluvfy.sh stage –pre crsinst –n rac1,rac2

Make sure Node reachable and User Equivalency is met, rest other checks (related memory) can be ignored

Once RAC1 and RAC2 can ping each other and oracle user can connect to other machine’s oracle user without prompting password then start clusterware installation from user oracle.

Change directory to location where you have unzipped clusterware software and start clusterware installation by running runInstaller

./runInstaller

Ignore memory related prereq.

Add second node rac2 by clicking on Add link

Change subnet as per your network configuration as you did while configuring network card.

Make one public and another private (Private interconnect for clusterware)

Specify ocr location like /apps/oracle/oradata/ocr (or as per your naming convention)

Specify voting disk location like /apps/oracle/oradata/votingdisk (or as per your naming convention)

Click on Install to install clusterware.

After clusterware installation, Install Database

Install RAC Database

Go to location where you unzipped database disks

./runInstaller –ignoreSysPrereqs (this is required as our memory is less (below 900MB) required, if your machine spec is higher you can ignore using ignoreSysPrereqs)

Specify database oracle_home (make this different from clusterware oracle_home installed in previous step)

Enter database name of your choice

Select ASM

Ignore any warnings and click on OK

Define location of ASM as /dev/raw/raw3 and make redundancy as external

Click on Install

Why number of voting disks should always be odd number only ?

How to troubleshoot RAC database and clusterware issue ?

.. Coming in future posts

Orcale RAC Part III – Clone RAC1 to RAC2 and User Equivalency

In this post you will

–Clone RAC1 to RAC2 and change IP address of RAc2
–Configure User equivalency for Orale Clusterware installation

Clone RAC1 to RAC2
——————————-

- Make sure virtual linux machine is shutdown
-Copy folder rac1 to folder rac2 in c:\Atul\VMWARE (This is on windows laptop/machine)

–In both RAC1 and RAC2 folder open “Red Hat Enterprise Linux 3.vmx” (This is virtual machine configuration file) using notepad or wordpad

–Add following lines (This will avoid locking for shared files and two linux machines RAC1 and RAC2 can start together using shared files votingdisk, crs and asm)

disk.locking = “FALSE”
diskLib.dataCacheMaxSize = “0″
diskLib.dataCacheMaxReadAheadSize = “0″
diskLib.dataCacheMinReadAheadSize = “0″
diskLib.dataCachePageSize = “4096″
diskLib.maxUnsyncedWrites = “0″

–Now on vmx file of RAC2 “Red Hat Enterprise Linux 3.vmx” change following lines from displayName = “RAC1″ to displayName = “RAC2″

–On VMWare Server Console, Click on File and Open or “Ctrl + O”

–Click on Browse and select “red hat enterprise linux 3.vmx” from RAC2 directory

–Now you should see two linux machines RAC1 and RAC2 on VMWare Console

Start RAC1 and RAC2 and when you get below warning on RAC2, select create and click OK

Make sure you can start both machine at same time, if you get message like unable to lock files, check you have added following lines in both machines vmx files mentioned above (regarding disk locking)

 

 

Change Network Settings (IP) on RAC2
————————————————-

Login as ROOT user in RAC2 machine and click System Setting -> Network

Select eth0 and click on Edit

Change Address from 192.168.1.101 to 192.168.1.102

Click on Hardware Device Tab and Click on Probe button

–Repeat steps for second network card eth1 and change its IP from 192.168.0.101 to 192.168.0.102

Make sure you clicked probe on eth1 as well as shown above

Activate both network cards by clicking on Activate as shown below

If you get error message that eth0 has different MAC address expected as shown below; make sure you have clicked on probe on “Bind to Mac Address” on “Hardware Device” mentioned above.

Make sure both network cards are active now, as shown below

Add following entry in /etc/hosts file of both RAC1 and RAC2

####

127.0.0.1 loalhost.localdomain localhost
192.168.1.102 rac2.focusthread.com rac2 rac2-vip.focusthread.com rac2-vip
192.168.0.102 rac2-priv.focusthread.com rac2-priv
192.168.1.101 rac1.focusthread.com rac1 rac1-vip.focusthread.com rac1-vip
192.168.0.101 rac1-priv.focusthread.com rac1-priv

###

Now trying to ping RAC2 machine from RAC1 and RAC1 machine from RAC2

Change Host Name of newly cloned RAC2

————————————————-

Follow below step on how to change hostname on RAC2 (newly cloned) Linux machine

Open file /etc/sysconfig/network and edit line like : HOSTNAME=XXXXX i.e. from

HOSTNAME rac1.focusthread.com To HOSTNAME rac2.focusthread.com

Configure User Equivalency

———————————–

User equivalency for user oracle is required so that user oracle from RAC1 can communicate to user oracle on RAC2 machine without prompting for password for clusterware to work properly.

–Login as user oracle in machine RAC1

–Make sure sshd daemon is running (pgrep sshd), If not start it from root user like “service start sshd”

–Create RSA and DSA key on each node

-From oracle user go to user home directory by “cd $HOME” or type cd and press enter
-mkdir .ssh (note that there is dot before ssh which means this is hidden directory)
-chmod 700 .ssh
-ssh-keygen –t rsa
-ssh-keygen –t dsa

press enter when prompted for file to save the key and passphrase (check screenshot for more information)

“Repeat this on both the nodes i.e. RAC1 and RAC2”

Add keys to authorized key file (authorized_keys)

————————————————————

-cd .ssh

-touch authorized_keys
-ls (You should see authorized_keys, id_dsa, id_dsa.pub, id_rsa, id_rsa.pub. Here pub files are dsa and rsa public key file and id_dsa and id_rsa are private key files)

ssh rac1 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh rac1 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
ssh rac2 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh rac2 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys

Send authorized_keys from RAC1 to RAC2

scp authorized_keys rac2:/home/oracle/.ssh

From RAC1 machine as user oracle
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add

Test User Equivalency (User oracle can login to other machine without password prompt)

—————————-

ssh rac1 date

ssh rac2 date

Test this from both nodes RAC1 and RAC2 (You should not be prompted for oracle user password)

For more information on User Equivalency visit chapter 2 (Page 51 to 53) of clusterware and rac installation guide

Coming Next :

Installing clusterware on RAC1 and RAC2

Installing RAC database on RAC1 and RAC2 linux machines using ASM

Building Oracle RAC: Install virtual disk and network card (Part II)

his is Part II of Step by Step RAC configuration on your laptop/desktop without investing anything extra on hardware (Free) using virtual server, virtual hard disk and virtual network cards.

For first two posts visit
Try installing Orcale RAC on your laptop
RAC Part I – Install Virtual Machine and Linux

In this Part II, We will install required linux operating package for clusterware & database, additional hard disk (voting disk, ocr and ASM), network card for interconnect and configure newly added hard disk and network card.

Install rpm’s
————————

Install following rpm’s (These rpm’s are in Red Hat ES CD’s or download them from rhn.redhat.com ) and install them using root account

(compat-glibc-7.x-2.2.4.32.6.i386.rpm, gnome-libs-1.4.1.2.90-34.2.i386.rpm, sysstat-4.0.7-4.EL3.3.i386.rpm )

rpm –ivh compat-glibc-7.x-2.2.4.32.6.i386.rpm
rpm –ivh gnome-libs-1.4.1.2.90-34.2.i386.rpm
rpm –ivh sysstat-4.0.7-4.EL3.3.i386.rpm

Create Shared Disk on Virtual Machine for Cluster Registry, Voting Disk and ASM
————————————————————
–Shutdown linux machine RAC1 (installed in part I) as “shutdown now” (from root user)
–Create folder “shared” in location “c:\atul\VMWARE” (or any other location of your choice)
–From VM Ware Console Press Ctrl + D to see Virtual Machine Settings

Or from screen

Click on Edit Virtual Machine Setting

Click on Add

Click on Next

Select Hard Disk as shown here and click Next

Select Create a New Virtual Disk and click Next

Select SCSI and click Next

Select 5.0 GB for voting disk and 5.0 GB for ocr (add second disk of 5 GB for ocr using same method) and 8 GB for asm (add third disk of 8 GB for ASM using same method)

Enter location and disk file name in folder shared created above.

Note* Select votingDisk for disk one created , ocr for second disk created and asm for third disk created . This name doesn’t matter and this is for our understanding only.

Click Finish

Repeat above step of adding disk for ocr and asm. Finally you should see Hard Disk 2,3,4

Hard Disk 2 : for votingDisk (5 GB)

Hard Disk 3 : for ocr (5 GB)

Hard Disk 4 : for ASM (8GB)

This completes addition of hard disk (How to configure and share these hard disks coming next ..)

Adding Network Card for Interconnect

—————————-

Next step is to create network card, there is already an existing network card which is used as public connection for connecting to this machine and database isnatance on this machine and new card will be used oracle clusterware as private inerconnect between two nodes of RAC i.e. RAC1 and RAC2. This private interconnect is used for clusterware talking to all remote nodes in cluster.

From VMWare Console press Ctrl + D

Select Ethernet Adapter this time and click Next

Select Bridged and click Finish

Now Start your linux Virtual Machine

On start of linux Virtual Machine you linux machine will detect newly added network card which you can configure now (at boot time only) or you can configure later as well.

Press Any Key to enter into setup part of network card

Select Configure and click enetr or you can configure it later as well

Enter following details

IP Address —————–192.168.0.101
Netmask ——————- 255.255.255.0
Default Gateway ——— 192.168.0.254
Primary Nameserver —- 192.168.0.1

and select OK and press enter

Login as Root User when prompted to login

Above we configured newly added network card eth1 (used for interconnect).

With below method we will configure original network card (for public connection)

Syetem Setting -> Network

select eth0 and click Edit

Add entry like
———————-
Address 192.168.1.101
Subnet Mask 255.255.255.0
Default Gateway Address 192.168.0.254

Click OK and activate this netowrk card

Activate eth0 by clicking on Activate button

Click on Yes, when prompted to save network configuration

Now you should see both network card eth0 and eth1 as Active

ifconfig , from root should show you result as shown in screenshot

Format and Configure Virtual disks (voting disk, ocr and asm )
—————————————————-

In this step We will format and configure Virtual Disks created above.

From user root

fdisk –l (This is to see list of disks, You will see entry like /dev/sdb, /dev/sdc, /dev/sdd doesn’t contain valid partition table)

fdisk /dev/sdb (Means we are dealing with /dev/sdb)
n (Create new disk)
p (Of type primary partition)
1 (First Cylinder)
Enter (carriage return)
Enter (carriage return)
w (Save table to disk and exit)

(For more information check man fdisk or below screenshot)

Repeat above step for /dev/sdc and /dev/sdd

So now fdisk –l you should details of /dev/sdb, dev/sdc, dev/sdd

–Add These disks as raw devices in file /etc/sysconfig/rawdevices as
/dev/raw/raw1 /dev/sdb1
/dev/raw/raw2 /dev/sdc1
/dev/raw/raw3 /dev/sdd1
–Restart rawdevices service as service rawdevices restart

Create directory /apps/oracle/oradata (For storing voting disk, ocr and ASM) as

mkdir –p /apps/oracle/oradata (From Root Account)

–>Create symbolic link pointing to these raw devices so that we know which one is for what (Optional)

ln –s /dev/raw/raw1 /apps/oracle/oradata/ocr

ln –s /dev/raw/raw2 /apps/oracle/oradata/votingdisk

ln –s /dev/raw/raw3 /apps/oracle/oradata/asm

–Change ownership of these directories to oracle:oracle

chown oracle:oracle /dev/raw/raw1

chown oracle:oracle /dev/raw/raw2

chown oracle:oracle /dev/raw/raw3

Shutdown this machine as this is now ready to clone and to create RAC2 machine.

Coming Next

-Cloning RAC1 to RAC2

-Changing RAC2 network and hostname details

-Configuring user equivalency

-Installing clusterware 10.2.0.1

-Installing Oracle rac database 10.2.0.1

-Tips and Tricks of troubleshooting Oracle RAC Database and clusterware…..

Step by Step build RAC: Install VMWare and Linux (Part 1)

This is first step for step by step Installation of Oracle RAC Database on single machine using VMWare Server. for overview visit http://becomeappsdba.blogspot.com/2007/07/are-you-scared-of-oracle-rac-database.html

Broad level for Part I Install VMWare and Linux
- Download, Register and Install VMWare Server
- Download and Install Red Hat Linux ES 3
- Add virtual network card for interconnect
- Add virtual shared shared disk (voting disk, ocr and ASM)

1.1 Install VMWare Server on Windows Machine

Download and install VMWare Server from VMWare Site (click here) (Download windows version as we are installing on windows desktop/laptop). You have to register for VMware Server on site mentioned above to recieve licence key used in installation of VMWare Server

1.2 Create Virtual Machine

–Start VM Ware server console from Start Menu on your windows operating system

–Create a New Virtual Machine by “Ctrl + N” or File -> New -> Virtual Machine (From Console Tool Bar)

Since we are installing Red Hat Enterprise Linux 3 so I have selected Version Red Hat Enterprise Linux 3

Select location where you wish to keep this RAC Machine Configuration and Linux Disks

Create this Linux Machine of size 15 GB, we will add another 5GB each for voting disk, ocr and ASM for data files.

1.3 Download/Order and install Red Hat Linux E.S. 3

Download red hat enterprise server linux version 3 from http://rhn.redhat.com/ (You can install any other linux version or operating system depending on availability. For this step by step Oracle RAC installation, I used Red Hat ES 3)

If you have downloaded Red Hat ES burn them as iso images.

Insert Disk1 of Red Hat Linux ES3 in your desktop/laptop and start RAC1 virtual Machine created above

During Disk Setup select Automatic Disk Partition and when you get this screen edit by clicking edit button and change swap space to 1500 MB

During Package Selection, Select Customize Package list and Add KDE Desktop Environment and Development Tools from list

Select No Firewall

During hostname enter machine name as rac1.focusthread.com this you can change later as well

1.4 Create User oracle:oracle

Create operating system user oracle (By default it will create group oracle and assign this to user oracle). You can use GUI as shown below or command useradd

Coming Next

Adding Virtual Network Card , Virtual Disks, install RPMs, Cloning RAC1 to RAC2 Machine, Configure User Equivalency ……..

Short Guide to install Oracle 10 on Linux

Martin Zahn, 06.10.2008

Download Files

---

Overview

This guide is a short description how to install Oracle 10.2.0.x on Linux (32 or 64 Bit). The full Oracle installation guide can be found here: Oracle® Database Installation Guide 10g Release 2 (10.2) for Linux x86.

In this guide we use the following ORACLE_BASE, ORACLE_HOME and ORACLE_SID.

• ORACLE_BASE=/export/home/oracle
• export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
• export ORACLE_SID=GENTIC

Download

Download the following software from the Oracle Download or Metalink Site

• 10201_database_linux32.zip (Base Software)
• 10201_companion_linux32.zip (Companion Software)
• p6810189_10204_Linux-x86.zip (Patchset)

After downloading check the CKSUM / MD5SUM as described on Oracle’s Site.

cksum 10201_database_linux32.zip
md5sum p6810189_10204_Linux-x86.zip

Requirements

Checking Memory and Swap Space

Oracle says that the system must have at least 1GB of physical RAM and 1GB of swap space or twice the size of RAM. And for systems with more than 2 GB of RAM, the swap space can be between one and two times the size of RAM. You might also want to check out sizing swap space.

To check the size of physical memory, execute:

grep MemTotal /proc/meminfo
MemTotal: 515700 kB

To check the size of swap space, execute:

grep SwapTotal /proc/meminfo
SwapTotal: 1004052 kB

You also can add temporary swap space to your system by creating a temporary swap file instead of using a raw device. Here is the procedure:

su – root
cd /root
dd if=/dev/zero of=/root/tmpswp bs=1k count=500000
500000+0 records in
500000+0 records out
chmod 600 tmpswp
mkswap tmpswp
Setting up swapspace version 1, size = 511995 kB
swapon tmpswp
grep SwapTotal /proc/meminfo
SwapTotal: 1504044 kB

Checking /tmp Space

The Oracle Universal Installer requires up to 400 MB of free space in the /tmp directory. If you do not have enough space in the /tmp filesystem, you can temporarily create a tmp directory in another filesystem. Here is how you can do this (/u01 is another filesystem)

su – root
mkdir /u01/tmp
chown root:root /u01/tmp
chmod 1777 /u01/tmp
export TEMP=/u01
export TMPDIR=/u01

When you are done with the Oracle installation, shutdown Oracle and remove the temporary /tmp directory:

su – root
rmdir /u01/tmp
unset TEMP
unset TMPDIR

Checking Kernel Parameters (Values from Oracle Site for 10.2.0.x)

To see all kernel parameters, execute:

su – root
sysctl -a

Verify that the kernel parameters shown in the following table are set to values greater than or equal to the recommended value shown. Do not change the value of any kernel parameter on a system where it is already higher than listed as minimum requirement.

Parameter	Value	File
semmsl semmns semopm semmni	250 32000 100 128	/proc/sys/kernel/sem
shmall	2097152	/proc/sys/kernel/shmall
shmmax	Half the size of physical memory (in bytes)	/proc/sys/kernel/shmmax
shmmni	4096	/proc/sys/kernel/shmmni
file-max	65536	/proc/sys/fs/file-max
ip_local_port_range	Minimum:1024 Maximum: 65000	/proc/sys/net/ipv4/ip_local_port_range
rmem_default	1048576	/proc/sys/net/core/rmem_default
rmem_max	1048576	/proc/sys/net/core/rmem_max
wmem_default	262144	/proc/sys/net/core/wmem_default
wmem_max	262144	/proc/sys/net/core/wmem_max

We added the following lines to the /etc/sysctl.conf file which is used during the boot process:

# Kernel Parameters for Oracle 10.2.0
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Adding these lines to the /etc/sysctl.conf file will cause the system to change these kernel parameters after each boot using the /etc/init.d/bootmisc script which is invoked by /etc/inittab. But in order that these new added lines or settings in /etc/sysctl.conf become effective immediately, execute the following command:

Configuring kernel parameters:

su – root
sysctl -p

Creating Oracle User Account

To create the oracle account and group(s), execute the following commands:

su – root
groupadd -g 400 dba
useradd -u 400 -g 400 -d /export/home/oracle -s /bin/bash -c “Oracle Owner” oracle

Members of the dba group can administer Oracle databases, for example starting up and shutting down databases. In this guide, we are creating the oracle user account to have this responsibility.

Note on the «oinstall» Group

The oinstall group is often needed for those organizations who have separate groups that maintain the software and the database. In this scenario, the group membership prevents unauthorized access to the database by personnel who maintain the software, and it prevents the database administrators from making changes to the software and the installations inventory directory.

When using oinstall during the install of the software, one must set their default group to the one that will be maintaining the database before creating the database or permission problems will arise.

Oinstall is not needed when the same people will be maintaining the software and the database. In this situation, it will create more work and add an extra layer of complexity to the maintenance of the installation.

Recommendation: if you don’t need the oinstall group for added security,  just install using the dba group.

Setting Shell Limits for the Oracle User

Most shells like Bash provide control over various resources like the maximum allowable number of open file descriptors or the maximum number of processes available to a user.

To see all shell limits, run:

ulimit -a

Setting Limits for the Oracle User

After you changed and increased /proc/sys/fs/file-max, there is still a per user limit of open file descriptors which is set to 1024 by default:

su – oracle
ulimit -n
1024

To change this, you have to edit the file /etc/security/limits.conf as root and make the following changes or add the following lines, respectively:

# To increase the shell limits for Oracle 10
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

The «soft limit» in the first line defines the number of file handles or open files that the Oracle user will have after login. If the Oracle user gets error messages about running out of file handles, then the Oracle user can increase the number of file handles like in this example up to 63536 («hard limit») by running the following command:

ulimit -n 63536

Note that we do not recommend to set the «hard limit» for nofile for the oracle user equal to /proc/sys/fs/file-max. If you do that and the user uses up all the file handles, then the system would run out of file handles. This could mean that you won’t be able to initiate new remote logins any more since the system won’t be able to open any PAM modules which are required for performing a login. That’s why we set the hard limit to 63536 and not to 65536.

You also need to make sure that pam_limits is configured in the file /etc/pam.d/system-auth. This is the PAM module that will read the /etc/security/limits.conf file. The entry should read like:

session required pam_limits.so
session required pam_unix.so

Now login to the oracle account again since the changes will become effective for new login sessions only.

su – oracle
ulimit -n
4096

Make Settings Permanent

For the Bourne, Bash, or Korn shell, add the following lines to the $HOME/.bash_profile

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 63536
else
ulimit -u 16384 -n 63536
fi
fi

Setup Environment

Before installing the Oracle software, set the environment in $HOME/admin/.GENTIC, this file will be sourced each time after login. In the example below, gentic is the SID of the database which we will create.

cd $HOME
. ./.gentic

ls -l
drwxr-xr-x   4 oracle dba  4.0K Sep 26 14:51 admin
lrwxrwxrwx   1 oracle dba    15 Oct  2 10:18 .gentic -> admin/.gentic

cat .gentic

#!/bin/bash

unset TWO_TASK

export ORACLE_SID=GENTIC
export LISTENER_NAME=$ORACLE_SID
export ORACLE_BASE=/export/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_DOC=$ORACLE_HOME/doc
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=/export/home/oracle/admin/sqlnet

export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=american_america.al32utf8
export ORA_NLS10=$ORACLE_HOME/nls/data
export TEMP=/tmp
export TMPDIR=/tmp
export EDITOR=vi

ulimit -u 16384 -n 63536

Installing Oracle Base, Companion and Patch

This is only a brief description of the installation, the screenshots are not shown, however note the following important issues.

• Order of the Installation

1. Install Base Product (10.2.0.1)
2. Install Companion Disk to avoid NCOMP error
3. Install Patch (10.2.0.4)

• Do not create a Database using Oracle installer, this is an extra step, install software only.
• Create the Oracle Inventory in $ORACLE_BASE/product.
• Choose «Oracle Database 10g Products 10.2.0.1.0» option when installing companion

Create Oracle Home Directories

Oracle Installer does not create Oracle Base / Oracle Home directories.

mkdir -p /export/home/oracle/product/10.2.0

Start Installer

As Oracle10g is certified only on some Linux editions you may get the following error when you run Oracle’s runInstaller, and installation will stop:

Checking operating system version: must be redhat-2.1,
UnitedLinux-1.0 or redhat-3
Failed <<<<

Solution: Use the option -ignoreSysPrereqs or edit/create the file /etc/redhat-release.

cd <unpacked directory>
DISPLAY=<my X-Window Host>:0.0
export DISPLAY
./runInstaller

Create Inventory in: /export/home/oracle/product

The location of the Oracle Inventory can always be checked in the file /etc/oraInst.loc

cat /etc/oraInst.loc

inventory_loc=/export/home/oracle/product
inst_group=dba

Oracle Companion Products

Choose «Oracle Database 10g Products 10.2.0.1.0» option, to avoid NCOMP error.

Cleanup $ORACLE_HOME/bin

After the installation some non used files in $ORACLE_HOME/bin can be removed.

cd $ORACLE_HOME/bin
rm *O
rm *0

Create the Listener

You can generate the needed files or you can create them manually. Put them in $TNS_ADMIN, but create symbolic links from $ORACLE_HOME/network/admin to these files, because some tools do not consult $TNS_ADMIN environment variable.

Example of LISTNER.ORA

# ————————————————————————–
# File:        listener.ora
#
# Purpose:     Configuration file for Net Listener
#
# Location:    $TNS_ADMIN
#
# Certified:   Oracle 10.2.0.3 on Oracle Enterprise Linux 5
# ————————————————————————–

GENTIC =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBHOST.EXAMPLE.COM)(PORT = 25000))
(ADDRESS = (PROTOCOL = IPC)(KEY = GENTIC.EXAMPLE.COM))
)
)

SID_LIST_GENTIC  =
(SID_LIST =
(SID_DESC =
(SID_NAME = GENTIC)
(GLOBAL_DBNAME = GENTIC.EXAMPLE.COM)
(ORACLE_HOME = /export/home/oracle/product/10.2.0)
)
)

LOG_DIRECTORY_GENTIC = /export/home/oracle/admin/sqlnet
TRACE_FILE_GENTIC = gentic.trc

Example of TNSNAMES.ORA

# ————————————————————————–
# File:        tnsnames.ora
#
# Purpose:     Configuration File for all Net Clients
#
# Location:    $TNS_ADMIN
#
# Certified:   Oracle 10.2.0.3 on Oracle Enterprise Linux 5
# ————————————————————————–

GENTIC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBHOST.EXAMPLE.COM)(PORT = 25000))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = GENTIC.EXAMPLE.COM)
)
)

Example of SQLNET.ORA

# ————————————————————————–
# File:        sqlnet.ora
#
# Purpose:     Configuration File for all Net Clients
#
# Location:    $TNS_ADMIN
#
# Certified:   Oracle 10.1.0.3 on RedHat Enterprise 5
# ————————————————————————–

NAMES.DIRECTORY_PATH= (TNSNAMES)

Create Symbolic Links

cd $ORACLE_HOME/network/admin

lrwxrwxrwx 1 oracle dba listener.ora -> /export/home/oracle/admin/sqlnet/listener.ora
lrwxrwxrwx 1 oracle dba sqlnet.ora -> /export/home/oracle/admin/sqlnet/sqlnet.ora
lrwxrwxrwx 1 oracle dba tnsnames.ora -> /export/home/oracle/admin/sqlnet/tnsnames.ora

Start and show status of Listener GENTIC

lsnrctl start GENTIC
lsnrctl status GENTIC

Create Database

We recommend to generate the create scripts using Oracle DBCA. These scripts should be verified because they show man important issues – important to know for every DBA. Based on these scripts, we created our own initSID.sql and initSID.ora to create the database. In the subdirectory addons all optional parts of the database can be found and installed using install-addons.bash

Edit initSID.ora and initSID.sql for your needs

Adjust and check the parameters for your environment, for example all PATH must be changed, the value of SGA_TARGET and SGA_MAX_SIZE must be set according your physical memory, etc.

The files can be found in:

$ORACLE_BASE/admin/GENTIC/scripts/initGENTIC.sql
$ORACLE_BASE/admin/GENTIC/pfile/initGENTIC.ora

Enable Remote Administration with SYSDBA Privileges

If you want to manage all of your Oracle databases from one place and not have to log on to each host, you must do it via a network connection. For example to remotely administer RMAN through a network connection, you need such an environment. To enable remote administration you have to two things:

1. Create a password file on each target database.
2. Enable remote logins for password file users.

To create the password file, as the Oracle software owner or as a member of the DBA group:

cd $ORACLE_BASE/admin/GENTIC/pfile
orapwd file=orapwGENTIC password=xxxx entries=5 force=y

After you create a password file, you need to enable remote logins. To do this, set the instance’s REMOTE_LOGIN_PASSWORDFILE initialization parameter in INIT.ORA to exclusive, as shown:

remote_login_passwordfile = exclusive

Setting this parameter to exclusive signifies that only one database can use the password file and that users other than SYS can reside in it. You can now use a network connection to connect to your target database as SYSDBA.

sqlplus “sys/xxxx@<SERVICE> as sysdba”

Create Symbolic Links

Create symbolic links from $ORACLE_HOME/dbs as follows:

$ cd $ORACLE_HOME/dbs
lrwxrwxrwx  orapwGENTIC -> /export/home/oracle/admin/GENTIC/pfile/orapwGENTIC
lrwxrwxrwx  initGENTIC.ora -> /export/home/oracle/admin/GENTIC/pfile/initGENTIC.ora

Create the Database

First start the Instance, just to test your INIT file for correct syntax and system resources.

cd $ORACLE_BASE/admin/GENTIC/scripts
sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount
SQL> shutdown immediate

Now you can create the database

SQL> @initGENTIC.sql
SQL> @shutdown immediate
SQL> startup

Check the Logfile: initGENTIC.log

Create additional Products in the Database (optional)

If you do not want all additional products to be installed, uncomment them in install-addons.bash.

cd $ORACLE_BASE/admin/GENTIC/scripts/addons
./install-addons.bash

Create Server Parameter File

Server parameter files let you make persistent changes to individual parameters. When you use a server parameter file, you can specify in an ALTER SYSTEM SET parameter statement that the new parameter value should be persistent. This means that the new value applies not only in the current instance, but also to any instances that are started up subsequently. Traditional client-side parameter files do not let you make persistent changes to parameter values.

cd $ORACLE_HOME/dbs
rm initGENTIC.ora

sqlplus / as sysdba
SQL> create spfile = ‘/export/home/oracle/admin/GENTIC/pfile/spfileGENTIC.ora’
        from pfile = ‘/export/home/oracle/admin/GENTIC/pfile/initGENTIC.ora’;
File created.
SQL> exit;

ln -s /export/home/oracle/admin/GENTIC/pfile/spfileGENTIC.ora spfileGENTIC.ora
sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> exit;

Automatically Start / Stop the Database and Listener

If you want to start the database automatically on boot-time perform the following steps:

1. Copy standard oracle scripts dbstart and dbshut to $ORACLE_BASE/admin
2. Create start/stop script in /etc/init.d
3. Activate this start/stop script with chkconfig
4. Edit /etc/oratab to start/stop the desired Databases

Copy standard oracle scripts dbstart and dbshut to $ORACLE_BASE/admin

cd $ORACLE_HOME/bin
cp dbstart dbshut $ORACLE_BASE/admin

Create start/stop script in /etc/init.d as user root.

#!/bin/bash # # chkconfig: 12345 99 10 # description: Oracle auto start-stop script # -------------------------------------------------------------------------- # File: oracle # # Purpose: Oracle auto start-stop script # # Location: /etc/init.d # # Certified: Oracle 10.2.0.3 on Enterprise Linux 5 # -------------------------------------------------------------------------- # Variables ORACLE_OWNER=oracle; export ORACLE_OWNER ORACLE_BASE=/export/home/oracle; export ORACLE_BASE ORACLE_SCRIPTS_DIR=$ORACLE_BASE/admin; export ORACLE_SCRIPTS_DIR ORACLE_HOME=$ORACLE_BASE/product/10.2.0; export ORACLE_HOME # Source function library. . /etc/rc.d/init.d/functions # Source networking configuration. [ -f /etc/sysconfig/network ] && . /etc/sysconfig/network RETVAL=0 prog="oracle" start() { echo -n $"Starting $prog: " if [ ! -f $ORACLE_SCRIPTS_DIR/dbstart ] then echo "Oracle not started (no dbstart script)" else # Start RDBMS su - $ORACLE_OWNER -c $ORACLE_SCRIPTS_DIR/dbstart # Start Enterprise Manager Console # su - $ORACLE_OWNER $ORACLE_HOME/bin/emctl start dbconsole # Start iSQL*Plus # su - $ORACLE_OWNER $ORACLE_HOME/bin/isqlplusctl start fi RETVAL=$? [ $RETVAL -eq 0 ] && touch /var/lock/subsys/oracle return $RETVAL } stop() { echo -n $"Shutting down $prog: " if [ ! -f $ORACLE_SCRIPTS_DIR/dbshut ] then echo "Oracle not stopped (no dbshut script)" else # Stop iSQL*Plus # su - $ORACLE_OWNER $ORACLE_HOME/bin/isqlplusctl stop # Stop Enterprise Manager Console # su - $ORACLE_OWNER $ORACLE_HOME/bin/emctl stop dbconsole # Stop RDBMS su - $ORACLE_OWNER -c $ORACLE_SCRIPTS_DIR/dbshut fi RETVAL=$? [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/oracle return $RETVAL } # See how we were called. case "$1" in start) start ;; stop) stop ;; *) echo $"Usage: $0 {start|stop}" exit 1 esac exit $RETVAL

Activate this start/stop script with chkconfig as root as follows

su – root
chkconfig –add oracle
chkconfig –list oracle

Edit /etc/oratab to start/stop the desired databases

su – root
cat /etc/oratab

# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:

GENTIC:/export/home/oracle/product/10.2.0:Y

Activate Enterprise Manager Console

Create the repository for the enterprise manager console as follows:

emca -repos create
emca -config dbcontrol db

Try to connect to the database control as follows:

http://<db-host>:1158/em

Automatically start and stop the DB-console

emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

Oracle6i Forms/Reports Builder Cannot Connect To Any Database (ORA-12222)

Modified 12-DEC-2008     Type PROBLEM     Status MODERATED

In this Document
Symptoms
Cause
Solution
References

---

 

This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

 

Applies to:

Oracle Net Services – Version: 8.0.6.0.0 to 8.0.6.3.0
Microsoft Windows 2000
Microsoft Windows XP
Microsoft Windows Server 2003

Symptoms

– Problem Statement:
Oracle Forms6i Builder: 6.0.8.11.3 on Microsoft Windows XP.

Server: Oracle10g R2 (10.2.0.3.0)

When trying to connect to any database instance using Oracle6i Forms displays the following error:

ORA-12222: TNS:No such protocol adapter

There are no problems connecting to the database from SqlPlus, TOAD, etc that use the
same tnsnames.ora file entries.

Problem reported with any/all Net Service Name/aliases.

Cause

The ORA-12222 error is due to the ORACLE_HOME System environment variable being explicitly set to one of the installed Oracle Home locations causing the Oracle Forms/Reports products to look for supporting DLLs in the wrong path location.

When using multiple Oracle Homes on Windows platforms, the ORACLE_HOME variable should not be explicitly set in either the User or System Environment Variables.

To verify if the ORACLE_HOME variable is set in either the User or System Environment Variables:

- Right click My Computer from the Windows Desktop.
- Select ‘Properties’.
- Then click the ‘Advanced’ tab.
- Next select the ‘Environment Variables’ button.
- Search for the ORACLE_HOME variable in either the User or System Environment Variables sections.

Solution

– To implement the solution, please execute the following steps:

- Right click My Computer from the Windows Desktop.
- Select ‘Properties’.
- Then click the ‘Advanced’ tab.
- Next select the ‘Environment Variables’ button.
- Search for the ORACLE_HOME variable in either the User or System Environment Variables sections.
- If found, highlight/select the ORACLE_HOME variable and delete it using the ‘Delete’ button below the corresponding windowpane.

Important: On Windows 2003 Servers it is also important to cleanly reboot the Windows Server to clear the ORACLE_HOME variable.

Configuring the DR Databases using RMAN

Configuring the DR Databases

The Oracle DR configurations on both the primary and standby servers are identical.  Thus the alias TEST points to the primary server and STTEST points to the standby server on both environments.  Only the TNSNAMES.ORA file differs on each server defining the aliases TEST and STTEST.  For example on the primary server PRSERVER, TEST points to PRSERVER as the primary server and STSERVER as the standby; where as on the standby server STSERVER the alias TEST points to STSERVER as the primary server and PRSERVER as the standby (opposite to that on PRSERVER).

 

TNSNAMES.ORA File on the primary server PRSERVER:

TEST.DBSUPPORTSERVICES.CO.UK =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = PRSERVER)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TEST)

)

)

 

 

STTEST.DBSUPPORTSERVICES.CO.UK =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = STSERVER)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TEST)

)

)

 

 

TNSNAMES.ORA File on the standby server STSERVER:

TEST.DBSUPPORTSERVICES.CO.UK =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = STSERVER)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TEST)

)

)

 

 

STTEST.DBSUPPORTSERVICES.CO.UK =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = PRSERVER)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TEST)

)

)

 

 

Relevant Initialisation Parameters on both Production and DR Servers:

 

*.log_archive_dest_1=’LOCATION=M:\Oracle-Databases\TEST\archive’

*.log_archive_dest_2=’SERVICE=STTEST.DBSUPPORTSERVICES.CO.UK REOPEN=60′

*.log_archive_dest_state_2=’ENABLE’

*.standby_archive_dest=’P:\Oracle-Databases\TEST\archive’

*.standby_file_management=’AUTO’

*.fal_client=’TEST.DBSUPPORTSERVICES.CO.UK’

*.fal_server=’STTEST.DBSUPPORTSERVICES.CO.UK’

*.archive_lag_target=900

 

Configuring the environments as above will allow log replication to continue after a clean switchover so that it is easy to switch back and forth between servers.  For example PRSERVER can become a standby database while STSERVER is primary and vice Aversa.

Creating a Standby Database from the Primary via RMAN

On production server:

 

1. 1.       Launch RMAN to connect to the TEST database and check the current RMAN configuration:

 

C:\ >set ORACLE_SID=TEST

C:\ >rman target /

Recovery Manager: Release 9.2.0.6.0 – Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: TEST (DBID=1949209861)

 

RMAN> show all;

 

using target database controlfile instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘I:\Oracle_Backups\%d_%F.rman’;

CONFIGURE DEVICE TYPE DISK PARALLELISM 1;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘I:\Oracle_Backups\%d_%s_%p.rman’;

CONFIGURE MAXSETSIZE TO UNLIMITED;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\ORACLE\ORA92\DATABASE\SNCFTEST.ORA’;

 

1. 2.       Backup the current production database to create a standby database:

RMAN> backup database include current controlfile for standby plus archivelog;

 

1. 3.       Manually copy the backup sets from I:\Oracle_Backups on the production server to I:\Oracle_Backups on the DR Server (location of backups must match on both production and DR).

 

1. 4.       On the DR Server start up the TEST database in nomount mode:

C:\> set ORACLE_SID=TEST

C:\> sqlplus /nolog

SQL> connect / as sysdba

SQL> startup nomount

SQL> exit

 

1. 5.       Create the standby database using RMAN (This assumes the database file structures will be identical on both servers):

C:\> RMAN target ‘sys/fluffy@STTEST’ auxiliary /

RMAN> duplicate target database for standby nofilenamecheck dorecover;

 

1. 6.       Once the DR database is created; you will need to manually add a tempfile:

SQL> alter database open read only;

SQL> alter tablespace temp add tempfile ‘F:\Oracle-Databases\TEST\TEMP01.DBF’ size 500M;

 

1. 7.       Put the DR database into managed standby mode:

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

 

1. 8.       On the production database switch logs to initiate replication:

SQL> alter system switch logfile;

The configuration of Dataguard is now complete.

Invoking a DR Standby Database

Overview

There are different methods you can use to invoke a standby database depending on the typical DR scenario you are facing.

Method	Description	Scenario for usage.
Database Switchover	Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment.	This scenario can be used if both the Primary and Standby Servers are available so that you can connect to both environments simultaneously and perform a clean switchover.
Activating a Standby Database	This method will activate the Standby Database as the primary database recovering up to the point of the last log shipment.  After activating a standby database as the primary the original primary server becomes obsolete and will need to be rebuilt as a standby database (e.g. you can not just switch the servers back to act as a primary or standby database and will need to rebuild the data-guard environments).	This scenario can be used if the primary server is not available and you need to force the standby database to become the primary.
Opening the standby Database in Read-Only Mode	This method involves stopping data-guard replication and opening the standby database in read-only mode for querying.  The database can then be shutdown and reopened in standby mode so that data-guard replication can resume (assuming all required archive logs are still available)	This scenario can be used if you want to open the standby database temporarily for querying data and then wish to put the database back into standby mode.

 

The following section describes how to perform each of the above methods of invoking a standby database:

 

Database Switchover

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment:

 

On Primary Server:

SQL> alter database commit to switchover to standby;

 

This may cause the following error to be generated:

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

If this does occur then restart the database, as below, before retrying the above command:

SQL> shutdown immediate

SQL> startup

 

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

 

The primary server is now configured as a DR standby database.

 

On DR Server:

SQL> alter database recover managed standby database cancel;

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate

SQL> startup

 

The DR server is now configured as the primary database.

To switch back you just need to repeat the above process but the other way around (e.g. convert the DR database back to a standby and the primary database back to primary).

 

Activating a Standby Database

If the primary database is not available the standby database can be converted into the primary database as follows:

 

SQL> alter database recover managed standby database cancel;

SQL> alter database activate standby database;

SQL> shutdown immediate

SQL> startup

 

The original primary database is now obsolete and can be rebuilt as a standby database once it is available again.

Opening the Standby Database in Read Only Mode

The standby database can be opened in read only mode for querying and then converted back into a standby database without affecting the primary.

 

On standby server:

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

 

The standby database is now open and available for querying in read only mode.

To put the standby database back into standby mode:

 

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

 

How to check whether the Standby Database is in Sync

 

On the primary server:

SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history;

 

On the standby server:

SQL> SELECT max(sequence#) AS “STANDBY”, applied

FROM v$archived_log GROUP BY applied;

 

The standby database is in sync with the primary database if the above PRIMARY value matches the above STANDBY value where applied = ‘YES’.