Saturday, April 5, 2008

DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

With the export/import new technology data pump we can filter to load/unload certain objects. This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.
Incorrect usage of metadata filters, can result in errors such as:

ORA-39001: invalid argument value
ORA-31655: no data or metadata objects selected for job
UDE-00011: parameter include is incompatible with parameter exclude

How metadata can be filtered with the EXCLUDE and INCLUDE parameters.

1. Syntax of the INCLUDE and EXCLUDE Data Pump parameters.


EXCLUDE = object_type[:name_clause] [, ...]
INCLUDE = object_type[:name_clause] [, ...]

EXCLUDE=SEQUENCE, TABLE:"IN ('EMP', 'DEPT')"
INCLUDE=FUNCTION, PACKAGE, TABLE:"= 'EMP'"

2. SQL Operator usage.

EXCLUDE=TABLE:"IN ('EMP', 'DEPT')"

or:

EXCLUDE=INDEX:"= 'MY_INDX'"

or:

INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"

or:

INCLUDE=TABLE:"> 'E'"

3.Double quotes and single quotes usage.

The name clause is separated from the object type with a colon. The name clause must be enclosed in double quotation marks. The single-quotation marks are required to delimit the name strings. Using the INCLUDE or EXCLUDE parameter in a parameter file is the preferred method.
Parameter file: exp.par
-------------------------
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:"IN ('EMP', 'DEPT')"

> expdp system/manager parfile=exp.par

To run this job without a parameter file, you need to escape the special characters. Incorrect escaping can result in errors such as: ksh: syntax error: '(' unexpected.
expdp system/manager DIRECTORY=my_dir \
DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"

4. Using the same filter name for an object type more than once.

If multiple filters are specified for an object type, an implicit AND operation is applied to them. That is, the objects that are exported or imported during the job have passed all of the filters applied to their object types.

5. The EXCLUDE and INCLUDE parameters are mutually exclusive.

It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.
Parameter file - incorrect syntax (error: UDE-00011):

INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
EXCLUDE=INDEX:"= 'PK_EMP'"

Parameter file - correct syntax:

INCLUDE=TABLE:"IN ('EMP', 'DEPT')"


6. Specified object types depend on the export/import Data Pump mode.
During a TABLE level export/import, certain object types that are directly related to SCHEMA or DATABASE level jobs, cannot be specified. The same applies to a SCHEMA level export/import where no DATABASE level object types can be specified.

To determine the name of the object types can be specified with EXCLUDE and INCLUDE,

database_export_objects
schema_export_objects
table_export_objects

7. Only specific object types can be named with a Name clause.

The name clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT).
To determine which object types can be named, you can use,database_export_objects and ....

EXCLUDE = TRIGGER:"IN ('TRIG1', 'TRIG2')", INDEX:"= 'INDX1'", REF_CONSTRAINT

8. Excluding/Including an object, will also exclude/include it's dependent objects.

Dependent objects of an identified object are processed along with the identified object. For example, if a filter specifies that an index is to be included in an operation, then statistics from that index will also be included. Likewise, if a table is excluded by a filter, then indexes, constraints, grants, and triggers upon the table will also be excluded by the filter.

To determine which objects are dependent, e.g. for a TABLE, you can run the following query,
SELECT named, object_path, comments
FROM database_export_objects
WHERE object_path LIKE 'TABLE/%';

9. Exporting or Importing a large number of objects.


If a large number of objects needs to be exported (or imported), it is possible that an internal buffer limit is exceeded (e.g. for the parameters INCLUDE or TABLES). If that happens it will cause the Data Pump job to abort with an error such as: ORA-06502 (PL/SQL: numeric or value error: character string buffer too small). This happened especially in Oracle10g Release 1 where the value for the internal buffer was set to 3000 bytes. With the fix for Bug 4053129 "EXPDP fails with ORA-39125 ORA-6502 on large list of table names" (not a public bug; fixed in 10.1.0.5.0 and 10.2.0.x), this value was increased to 4000 bytes.
When exporting a large number of objects, we recommend to make use of a table inside the database that contains the names of the objects.
Example:
-- create a table that contains the names of the objects:

CONNECT scott/tiger
CREATE TABLE expdp_table (owner VARCHAR2(30),
object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_table VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_table VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_table VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;
-- run export DataPump job:

expdp system/manager DIRECTORY=my_dir \
DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(SELECT object_name FROM scott.expdp_table WHERE \
owner=\'SCOTT\' AND object_type=\'TABLE\'\)\"

On windows in one line following is valid.
expdp system/a SCHEMAS=aRJU INCLUDE=TABLE:\"IN \(SELECT object_name FROM dba_objects WHERE owner=\'ARJU\' AND object_type=\'TABLE\'\)\"

10. Other issues when excluding objects at a Data Pump job.

10.1. Excluding Constraints.
The following constraints cannot be excluded:
- NOT NULL constraints.
- Constraints needed for the table to be created and loaded successfully (for example, primary key constraints for index-organized tables or REF SCOPE and WITH ROWID constraints for tables with REF columns).

10.2. Excluding Grants.
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.

10.3. Excluding Users.
Specifying EXCLUDE=USER excludes only the definitions of users, not the objects contained within users' schemas. To exclude a specific user and all objects of that user, specify a filter such as the following (where SCOTT is the schema name of the user you want to exclude):
EXCLUDE=SCHEMA:"='SCOTT'"

If you try to exclude a user by using a statement such as EXCLUDE=USER:"= 'SCOTT'", only the CREATE USER scott DDL statement will be excluded, and you may not get the results you expect.
11. Other issues when including objects at a Data Pump job.

When specifying the INCLUDE parameter for en Export Data Pump or Import Data Pump job, only object types explicitly specified in INCLUDE statements (and their dependent objects) are exported/imported. No other object types, such as the schema definition information that is normally part of a schema-mode export when you have the EXP_FULL_DATABASE role, are exported/imported.

Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2009/02/how-to-do-data-pump-import-from-higher.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/how-to-take-data-pump-export-on-another.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html
Posted by Arju at 11:34 PM 12 comments Labels:
Reactions: 

How does one use the import/export utilities?

exp/imp allow to transfer the data across databases that reside on different hardware platforms and/or on different Oracle versions. If the data is exported on a system with a different Oracle version then on that on which it is imported, imp must be the newer version. That means, if something needs to be exported from 10g into 9i, it must be exported with 9i's exp.

In order to use exp and imp, the catexp.sql script must be run. catexp.sql basically creates the exp_full_database and imp_full_database roles. No need to run catexp.sql for every time. It is only one time work. It is already executed if you create your database by dbca. So , don't bother with it if you create database with dbca.
It is found under $ORACLE_HOME/rdbms/admin.

Prerequisites
--------
One must have the create session privilege for being able to use exp. If objects of another user's schema need to be exported, the EXP_FULL_DATABASE role is required.

Export/Import Modes:
--------------------------------------------

1)Full database export:
--------------------------
The EXP_FULL_DATABASE and IMP_FULL_DATABASE, respectively, are needed to perform a full export.
Use the full export parameter for a full export.

2)Tablespace:
--------------------
Use the tablespaces export parameter for a tablespace export. It is only apply to transportable tablespaces.

3)Schema:
--------------
This mode can be used to export and import all objects that belong to a user.Use the owner export parameter and the fromuser import parameter for a user (owner) export-import.

4)Table:
---------------
Specific tables (and partitions) can be exported/imported with table export mode.
Use the tables export parameter for a table export.


Example:
-----------
The following examples demonstrate how the imp/exp utilities can be used:

exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Using a parameter file:

exp userid=scott/tiger@orcl parfile=export.txt

... where export.txt contains:

BUFFER=100000
FILE=account.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y

To see DDL within Dump:
---------------------
To write ddl into a file,
imp userid/pass file=a.dmp indexfile=myfile.txt

or, to see ddl into screen,
imp userid/pass file=a.dmp show=y

Related Documents:
--------------------
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
Posted by Arju at 7:42 PM 0 comments Labels: , ,
Reactions: 

Thursday, April 3, 2008

Causes and Solutions on ORA-1113 Error File Needs Media Recovery

Error Explanation:
------------------
ORA-01113: "file %s needs media recovery"

This error is usually followed with ORA-1110 error which will indicate the
name of the datafile that needs media recovery.Like,

ORA-01110: data file 4: 'C:\ORACLE\ORADATA\BDMS\DRSYS01.DBF'

This error message indicates that a datafile that is not up-to-date with respect to the controlfile and other datafiles.

Oracle's architecture is tightly coupled in the sense that all database files i.e., datafiles, redolog files, and controlfiles -- must be in sync when the database is opened or at the end of a checkpoint.

This implies that the checkpoint SCN (System Commit Number) of all datafiles must be the same. If that is not the case for a particular datafile, an ORA-1113 error will be generated.

For example, when you put a tablespace in hot backup mode, the checkpoint SCN of all its datafiles is frozen at the current value until you issue the corresponding end backup. If the database crashes during a hot backup and you try to restart it without doing recovery, you will likely get ORA-1113 for at least one of the datafiles in the tablespace that was being backed up, since its SCN will probably be lower than that of the controlfile and the datafiles in other tablespaces.

Likewise, offlining a datafile causes its checkpoint SCN to freeze. If you simply attempt to online the file without recovering it first, its SCN will likely be much older than that of the online datafiles, and thus an ORA-1113 will result.


Ensure what you need to do?
-----------
1.startup mount;
2.conn / as sysdba
3.SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

This will list all your online redolog files and their respective sequence and
first change numbers.

The steps to take next depend on the scenario in which the ORA-1113 was issued.
This is discussed in the following sections.

Possible Causes and Corresponding Solutions:
----------------------------------------------

I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY

I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
----------------------
1.startup mount
2. Find out which datafiles were in hot backup mode when the database crashed or was shutdown abort or the machine was rebooted by running the query:
SELECT V1.FILE#, NAME
FROM V$BACKUP V1, V$DATAFILE V2
WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE# ;
3.alter database datafile 'file name' end backup;
4.alter database open;

II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
-------------------------
A)Database in Archivelog Mode

1.STARTUP MOUNT;
2.RECOVER DATAFILE 'file name';
3.Confirm each of the archived logs that you are prompted for until you receive the message "Media recovery complete".If you are prompted for an archived log that does not exist, Oracle probably needs one or more of the online logs to proceed with the recovery. Compare the sequence number referenced in the ORA-280 message with the sequence numbers of your online logs. Then enter the full path name of one of the members of the redo group whose sequence number matches the one you are being asked for.
4. ALTER DATABASE OPEN;

B)Database in noArchivelog Mode

In this case, you will only succeed in recovering the datafile or tablespace if the redo to be applied to it is within the range of your online logs.
Issue the query:
SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

Compare the change number you obtain with the FIRST_CHANGE# of your online logs.

If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, the datafile can be recovered. In this case, the procedure to be followed is analogous to that of scenario II.A above, except that you must always enter the appropriate online log when prompted, until recovery is finished.

If the CHANGE# is LESS than the minimum FIRST_CHANGE# of your logs, the file cannot be recovered.Your options at this point include:

- If the datafile is in a temporary or index tablespace, you may drop it with an
ALTER DATABASE DATAFILE '' OFFLINE DROP
statement and then open the database. Once the database is up, you must drop the tablespace to which the datafile belongs and recreate it.

- If the datafile is in the SYSTEM or in a rollback tablespace, restore an up-to-date copy of the datafile (if available) or your most recent full backup.In case you do not have either of this, then it might not be possible to recover the database fully.

III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
--------------------------------------
1.RECOVER DATAFILE 'file name';
2. Confirm each of the archived logs that you are prompted for until you receive the message "Media recovery complete".
3.ALTER DATABASE OPEN;

IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
-------------------------------
1.RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
2. Cancel recovery by issuing the "CANCEL" command.
3. ALTER DATABASE OPEN RESETLOGS;
Related Documents
Posted by Arju at 2:48 AM 1 comments Labels:
Reactions: 

When and How to Recreate the Controlfile

When to Create Controlfile
No one should not create control file until he is not suppose to do it. You should only need to recreate your control file under very special circumstances:

1)All current copies of the control file have been lost or are corrupted.

2)You need to change a "hard" database parameter such as MAXDATAFILES, MAXLOGFILES, MAXLOGHISTORY, etc. Though after 10.2g it is handled by database.

3)You are restoring a backup in which the control file is corrupted or missing.

4)If you are moving your database to another machine which is running the same operating system but the location of the datafiles, logfiles is not the same.

How I will create a new control file:

In this case I may have to face two scenarios.

Scenario 1:CREATING A NEW CONTROL FILE FROM THE EXISTING CONTROL FILE:
--------------

1.Create a control file trace
SQL> conn / as sysdba
SQL>startup mount;
SQL>alter database backup controlfile to trace as 'file.txt';

2.Modify the trace file 'file.txt' and change the required parameter in it.

3.Shutdown the database. shutdown immediate;

4.Take a full database backup.

5.Rename/move the existing database controlfiles to a backup.

6.Create the new controlfile.

7.Take backup of full database.


CREATING A NEW CONTROL FILE WITHOUT AN EXISTING CONTROL FILE:
-----------------------------------------------------------------
1. Take a full backup of the database, including all datafiles and redo log files.

2.Do a startup nomount.

3.Issue the create controlfile statement.

4. Perform media recovery on the database. recover database.

5.Open the database. Alter database open.

6. At the first opportunity, shut the database down and take a full cold backup.

Here is one script of how you can create a new controlfile.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "database_name_here" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 1
MAXLOGHISTORY 449
LOGFILE
GROUP 1 'path of redo log member here' SIZE 500K,
GROUP 2 'path of redo log member here' SIZE 500K
DATAFILE
'list of datafile name here',
'/path/oracle/dbs/data.dbf',
'/path/oracle/dbs/data02.f',
'/path/oracle/dbs/arju02.dbf',
'/path/oracle/dbs/arju.dbf
CHARACTER SET WE8DEC
;
Related Documents

How to Restore the Controlfile from Backup.
Posted by Arju at 12:16 AM 0 comments Labels:
Reactions: 

Wednesday, April 2, 2008

ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown

Problem Description:
ORA-00214: control file {name} version {num} inconsistent with file {name}.

Cause of The Problem:
Oracle detects an inconsistency between the mirrored copies of the control file.

All copies of the control file must have the same internal sequence number for oracle to start up the database or shut it down in normal or immediate mode.

If the database is running and the checkpoint in the file header could not be advanced the datafile will be taken offline.

Typical scenarios in which you may receive an ORA-00214 include:

1. You have restored the control file from backup, but forgot to copy it onto all of the mirrored copies of the control file as listed in the "CONTROL_FILES" parameter in the initialization parameter.

2. You have moved one or more copies of the control file to a different location while the database was up and running.

3. You accidentally overwrote one of the copies of the control file with an old copy.

4. The database or the system crashed while the mirrored copies of the control file were being updated, causing them to be out of sync.

5. You are restoring a database backup that was improperly taken with the database up and running ("fuzzy" backup).

Solution of the Problem:
To fix the error start your database with single copy of the control file and then shut the database down and then copy the version of good copy of control file onto the other mirror copies.


Step 01) If database is still up do a shutdown abort.

Step 02) If you use pfile then edit the CONTROL_FILES parameter from init.ora and modify it to include just one copy of control file.

If you use spfile then after issuing startup nomount use show control_files to see existing controlfiles inside spfile and then you can use ALTER SYSTEM SET CONTROL_FILES=file_name; in order to point just one copy of control file.

Step 03) Start the database in restricted mode. startup restrict
If it is fine go to step 04.

If instead you get ORA-1122, ORA-1110, and ORA-1207, go back to step 2 and try with another control file.

If you have already tried each and every one of the mirrored copies unsuccessfully, you must create a new control file for the database.

If you get ORA-1113 and ORA-1110 pointing to one of the datafiles, it means the copy of the control file you picked is good, but the referenced datafile must be recovered before the database can be opened.Then RECOVER DATBASE, apply the log it prompt and ALTER DATABASE OPEN.

Step 04) Shut the database down.
SQL>shutown

Step 05) Copy the good mirrored copy of the control file that you just used to bring the database up onto all other copies, as originally listed in the CONTROL_FILES parameter of your init.ora file.

Step 06) Edit the init.ora file's CONTROL_FILES parameter to include all mirror copy again.

Step 07) Start the database.

Related Documents:
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Posted by Arju at 10:57 PM 8 comments Labels:
Reactions: 

RMAN-06026, RMAN-06023 During Restore Using RMAN

Problem Description:
--------------------
When attempting to restore database the error stack follows.
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
<.......>
RMAN-06023: no backup or copy of datafile 1 found to restore


Solution Description
--------------------
Some common reasons why a file can not be restored are that,

1)There is no backup or copy of the file that is known to recovery manager, or there are no backups or copies that fall within the criteria specified on the RESTORE command, or

2)Some datafile copies have been made but not cataloged.

Scenario 1:
-------------
You have added a new tablespace since your last backup.Now you issued just RESTORE DATABASE command without "SET UNTIL" clause. The restore job fails because it is looking for a backup of the datafile that belongs to the the new tablespace that has been added since the last backup was taken.

Solution Description:
You need to specify the "SET UNTIL" time clause in the restore job to a time
before the new tablespace was added.

Explanation:
When a restore command is run from RMAN and if no "SET UNTIL" clause is defined, RMAN will try and restore backups for all the datafiles in the database at the present time. If a "SET UNTIL" clause is defined, then RMAN will restore only the datafiles that exist in the database that match the specified point of time in the "SET UNTIL" time clause.


Scenario 2:
--------------
You are attempting to restore a database using Oracle Recovery Manager (RMAN) using a 'set time' parameter to do a point-in-time recovery and the error stack came as above. Whenever you invoke 'list backupset of database' command shows there to be multiple backups of these files available.

Solution Description:
You have issued a 'resetlogs' prior to the last backup but before the 'Until Time' clause in the RMAN script. For instance, the last backup of the database was June 8, 2008.On June 9, you opened the database with resetlogs. Then, you decide to restore the database to a point in time on June 10. Because you cannot roll forward through the resetlogs, RMAN cannot find any legitimate backups to restore from within this incarnation.

The solution is to set the 'until time' clause to a time before the resetlogs.


Explanation:
You need to check the incarnation of the database:

rman>list incarnation of database;

If the current incarnation reset time falls between the last backup and the time specified for 'Set Time,' then the recovery catalog acknowledges that there are no backups that match the time criteria specified, and errors out with RMAN-6023.
Related Documents

RMAN-06172: no autobackup found
Posted by Arju at 9:02 PM 0 comments Labels: ,
Reactions: 

ORA-00257: archiver error. Connect internal only, until freed.

Cause of the Problem:
The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Solution of the Problem:

I try to demonstrate two types of Solution to this problem.

A)Without increasing DB_RECOVERY_FILE_DEST_SIZE.
B)By increasing DB_RECOVERY_FILE_DEST_SIZE.

A)Without increasing DB_RECOVERY_FILE_DEST_SIZE.
1. Check whether the database is in archive log mode and automatic archiving is enabled.
SQL> archive log list;

2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by :

SQL> show parameter db_recovery_file_dest;

Check what the value for db_recovery_file_dest_size.

3. Find the space used in flash recovery area by :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

4. If SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

5. Archive all the log files
SQL> alter system archive log all;

6. Just switch the logs to verify:

SQL> alter system switch logfile;

Another approach of solving this type of problem without increasing DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs are no longer necessary.

Like,
$rman target /
RMAN>delete archivelog until time 'SYSDATE-1';
or,
RMAN>delete archivelog all;

B)By increasing DB_RECOVERY_FILE_DEST_SIZE.
1. See the path of flash recovery area.

SQL> show parameter db_recovery_file_dest;

2. Disable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='';

3. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10g;

4. Enable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/oradata1';

Related Documents
Archiving not possible: No primary destinations
ORA-00313: open failed for members of log group
Posted by Arju at 5:10 AM 7 comments Labels: , , ,
Reactions: 

Oracle Background Process

To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional Oracle processes called background processes.

There are many types of Oracle background processes. Each performs a specific job in helping
to manage the instance. Five Oracle background processes are required, and several background processes are optional.

Required Oracle Background Processes
--------------------------------
1)System Monitor (SMON): Performs instance recovery following an instance crash, coalesces free space in the database, and manages space used for sorting.

2)Process Monitor (PMON): Cleans up failed user database connections.

3)Database Writer (DBWn): Writes modified database blocks from the SGA’s Database Buffer Cache to the datafiles on disk

4)Log Writer (LGWR): Writes transaction recovery information from the SGA’s Redo Log Buffer to the online Redo Log files on disk.

5)Checkpoint (CKPT): Updates the database files following a Checkpoint Event.

Optional Database Background Processes

1)Recoverer Process (RECO)

2)Job Queue Processes

3)Archiver Processes (ARCn)

4)Queue Monitor Processes (QMNn)

5)Other Background Processes
Posted by Arju at 4:41 AM 0 comments Labels: ,
Reactions: 

SGA Components in Oracle 10g

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

The SGA is made up of three required components and three optional components.

Required SGA Components:
---------------------
1)Shared Pool:Caches the most recently used SQL statements that have been issued by database users.

2)Database Buffer Cache: Caches the data that has been most recently accessed by database users.

3)Redo Log Buffer: Stores transaction information for recovery purposes

Optional SGA Components:
---------------------

1)Java Pool:Caches the most recently used Java objects and application code when
Oracle’s JVM option is used.

2)Large Pool:Caches data for large operations such as Recovery Manager (RMAN)
backup and restore activities and Shared Server components

3)Streams Pool:Caches the data associated with queued message requests when
Oracle’s Advanced Queuing option is used.
Posted by Arju at 4:26 AM 0 comments Labels: ,
Reactions: 

Pfile and Spfile in Oracle.

There are two types of initialization parameter files in oracle:

1)Parameter Files (PFILES)
2)Server Parameter Files (SPFILES).

Comparison Between Pfile and Spfile:

A)Pfile is the text file that can be edited using a text editor. Spfile is binary file that cannot be edited directly by text editor.If you edit spfile it will corrupt.

B)When changes are made to the PFILE, the instance must be shut down and restarted before it takes effect.Most changes to the SPFILE can be made dynamically, while the instance is open and running.

C)Pfile is by default named as init{instance_name}.ora. Spfile is by default named as spfile{instance_name}.ora where instance_name will be replaced by your database name. By default spfile is located under $ORACLE_HOME/dbs in unix and windows and pfile will be located under $ORACLE_HOME\database location on windows.

D)Pfile can be created from an SPFILE using the create pfile from spfile command.Spfile can be created from a PFILE using the create spfile from pfile command.

E)In order to modify any parameter value in pfile you must open the pfile with any text editor and manually edit it. And in order to modify any parameter value inside spfile you must issue ALTER SYSTEM SET with SCOPE=SPFILE or SCOPE=BOTH. Pfile can never be edited with ALTER SYSTEM SET.

F)Spfile can be backed up by RMAN. pfile can't be backed up by RMAN.
In order to have clear idea about ALTER SYSTEM SET have a look at How to set initialization parameter using Alter System

Related Documents:
------------------------
How to set initialization parameter using Alter System
Posted by Arju at 4:16 AM 5 comments Labels:
Reactions: 

Types of Metadata Views in Oracle 10g

Metadata indicates data about the database. As a DBA we need frequently access the metadata in oracle database. There are two types of metadata views.

1)Data dictionary views.
2)Dynamic performance views.

Comparison between these Two:

A)Data dictionary views have names that begin with DBA_,ALL_, and USER_.Dynamic performance views have names that begin with V$.

B)The DBA_ views are available only when the database is open and running.Some V$ views are available even when the database is not fully open and running.

C)The data contained in the DBA_ views is generally uppercase.The data contained in the V$ views is usually lowercase.

D)The data contained in the DBA_ views is static and is not cleared when the database is shut
down.The V$ views contain dynamic statistical data that is lost each time the database is shut
down.
Posted by Arju at 3:15 AM 0 comments Labels:
Reactions: 

Tuesday, April 1, 2008

How does one code a matrix/crosstab/pivot report in SQL?

Newbies frequently ask how one can display "rows as columns" or "columns as rows". Look at these example crosstab queries (also sometimes called transposed, matrix or pivot queries):


SELECT  *
FROM  (SELECT job,
              sum(decode(deptno,10,sal)) DEPT10,
              sum(decode(deptno,20,sal)) DEPT20,
              sum(decode(deptno,30,sal)) DEPT30,
              sum(decode(deptno,40,sal)) DEPT40
         FROM scott.emp
     GROUP BY job)
ORDER BY 1;
JOB           DEPT10     DEPT20     DEPT30     DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST                    6000
CLERK           1300       1900        950
MANAGER         2450       2975       2850
PRESIDENT       5000
SALESMAN                              5600

Here is the same query with some fancy headers and totals:


SQL> ttitle "Crosstab Report"
SQL> break on report;
SQL> compute sum of dept10 dept20 dept30 dept40 total on report;
SQL>
SQL> SELECT     *
2    FROM     (SELECT job,
3                  sum(decode(deptno,10,sal)) DEPT10,
4                  sum(decode(deptno,20,sal)) DEPT20,
5                  sum(decode(deptno,30,sal)) DEPT30,
6                  sum(decode(deptno,40,sal)) DEPT40,
7                  sum(sal)                   TOTAL
8             FROM emp
9            GROUP BY job)
10  ORDER BY 1;

Mon Aug 23                                                             page    1
                              Crosstab Report

JOB           DEPT10     DEPT20     DEPT30     DEPT40      TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST                    6000                             6000
CLERK           1300       1900        950                  4150
MANAGER         2450       2975       2850                  8275
PRESIDENT       5000                                        5000
SALESMAN                              5600                  5600
        ---------- ---------- ---------- ---------- ----------
sum             8750      10875       9400                 29025


Here's another variation on the theme:

SQL> SELECT DECODE(MOD(v.row#,3)
2                 ,1, 'Number: '  ||deptno
3                 ,2, 'Name: '    ||dname
4                 ,0, 'Location: '||loc
5                 ) AS "DATA"
6    FROM dept,
7         (SELECT rownum AS row# FROM user_objects WHERE rownum < deptno =" 30"

Related Documents
Posted by Arju at 11:38 PM 0 comments Labels:
Reactions: 

Map/ concatenate several rows to a column

Start by creating this function:

SQL> CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
 2    ret  VARCHAR2(4000);
 3    hold VARCHAR2(4000);
 4    cur  sys_refcursor;
 5  BEGIN
 6    OPEN cur FOR q;
 7    LOOP
 8      FETCH cur INTO hold;
 9      EXIT WHEN cur%NOTFOUND;
10      IF ret IS NULL THEN
11        ret := hold;
12      ELSE
13        ret := ret || ',' || hold;
14      END IF;
15    END LOOP;
16    RETURN ret;
17  END;
18  /
Function created.

This function returns a string result with the concatenated non-NULL values from a SQL statement. It returns NULL if there are no non-NULL values.

Here is an example of how to map several rows to a single concatenated column:


SQL> SELECT rowconcat('SELECT dname FROM dept') AS departments
 2    FROM dual;
DEPARTMENTS
--------------------------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS


This example is more interesting, it concatenates a column across several rows based on an aggregation:

SQL> col employees format a50
SQL> SELECT deptno,
 2         rowconcat('SELECT ename FROM emp a WHERE deptno='||deptno) AS Employees
 3    FROM emp
 4   GROUP BY deptno
 5  /
   DEPTNO EMPLOYEES
---------- --------------------------------------------------
       30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
       20 SMITH,JONES,SCOTT,ADAMS,FORD
       10 CLARK,KING,MILLER

Related Documents
Posted by Arju at 11:35 PM 0 comments Labels:
Reactions: 

What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:

CHAR

CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

SQL> CREATE TABLE char_test (col1 CHAR(10));
Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;
COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty               10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

Note: ASCII character 32 is a blank space.

VARCHAR

Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));
Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;
COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty                6 Typ=1 Len=6: 113,119,101,114,116,121

VARCHAR2

VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));
Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;
COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty                6 Typ=1 Len=6: 113,119,101,114,116,121
Related Documents
Posted by Arju at 11:33 PM 0 comments Labels:
Reactions: 

How does one select the TOP N rows from a table?

After Oracle 9i there is the RANK() and DENSE_RANK() functions which can be used to determine TOP N rows.

Below is the examples to find the top 5 employees based on their salary.

Way 1: Using RANK()

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rank
FROM employee )
WHERE salary_rank <= 5;

Way 2: Using Dense_Rank()

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rank
FROM employee )
WHERE salary_dense_rank <= 5;

Way3: Using inner query
This is an example of using an inner-query with an ORDER BY clause:

SELECT *
FROM (SELECT * FROM employee ORDER BY salary DESC)
WHERE ROWNUM < 5;

Way 4: Using count distinct combination

SELECT *
FROM employee e
WHERE 5 >= (SELECT COUNT(DISTINCT salary)
FROM employee b
WHERE b.salary >= e.salary)
ORDER BY salary DESC;


Related Documents
How does one select EVERY Nth row from a table?

Can one retrieve only the Nth row from a table?

Can one retrieve only rows X to Y from a table?
Posted by Arju at 11:32 PM 2 comments Labels:
Reactions: 

How does one select the LAST N rows from a table?

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows. Examples:

Get the bottom 10 employees based on their salary

SELECT ename, sal 
  FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank
           FROM emp ) 
 WHERE sal_rank <= 10;

Select the employees getting the lowest 10 salaries


SELECT ename, sal 
  FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
           FROM emp ) 
 WHERE sal_dense_rank <= 10;

For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:


SELECT *
  FROM (SELECT * FROM my_table ORDER BY col_name_1)
 WHERE ROWNUM < 10;

Use this workaround for older (8.0 and prior) releases:


SELECT *
  FROM my_table a
 WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                FROM my_table b
               WHERE b.maxcol <= a.maxcol)
 ORDER BY maxcol;

Posted by Arju at 11:31 PM 0 comments Labels:
Reactions: 

How does one select EVERY Nth row from a table?

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:

Method 1: Using a subquery

SELECT *
FROM   emp
WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                    FROM   emp);

Method 2: Use dynamic views (available from Oracle7.2):

SELECT *
FROM   ( SELECT rownum rn, empno, ename
        FROM emp
      ) temp
WHERE  MOD(temp.ROWNUM,4) = 0;

Method 3: Using GROUP BY and HAVING

SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n;

Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.
Posted by Arju at 11:30 PM 0 comments Labels:
Reactions: 
Subscribe to: Posts (Atom)