------------------------------
Incremental export is an enhancement to export/import in v6 of the Rdbms. Thisnew feature of export allows, under certain conditions, the dba to shorten theexport time, but still have the reliability of export/import for recoverypurposes. Incremental exports will only export tables that have changedsince the last export taken with the inctype parameter. If one row of atable is updated, then the entire table is exported NOT just the changed data.The benefits occur when you have historical tables, or tables that areprimarily query only. This is where the savings in time occurs. Therefore,if all (or most) of the tables in your database are updated daily, littlebenefit is gained from incremental exports. Another important factor toconsider is the fact that incremental imports, at recovery time, will drop thetable before importing it. For a database that have very large tables thatchange everyday, recovery can be time consuming.
Three types of incremental exports exist, the differences between the threeis the amount of data they export.
1. Complete export (equivalent to full database export) exports all objects and clears the incremental export tables. Although complete exports will export the same information as full exports, complete will also mark the tables, which is essential for the next cumulative or incremental export.
2. Cumulative export will export all changed objects since the last'complete' or 'cumulative' export.
3. Incremental export will export all changed objects since the last complete', 'cumulative' or 'incremental' exports.
Each export will insert information into the incremental export tables, however,Complete exports will clear the tables before inserting new information.
For example, here is the export scheme for a mock company:
1. Complete export on the first day of the month.
2. Cumulative exports every Sunday.
3. Incremental exports every day.
Below is a picture of this backup scheme:
*******************************************************************************
M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30X I I I I I C I I I I I I C I I I I I I C I I I I I I C I I
*******************************************************************************
The cumulative export on the 7th, will include all the objects changed since thecomplete on the 1st. This cumulative contains the same information as incrementexports on the 2nd through the 6th and more, therefore cumulative exports arenot manditory, their benefit comes during recovery. Instead of six files toapply, there is only one.
RECOVERY:
In order to recover from incremental exports, you must initialize the database,and begin importing. Since many of the export files contain the same tables,incremental imports will always drop the object before importing. This prevents duplicate data from being imported. This is how an incrementalimport creates tables:
. importing user JACK "DROP TABLE "SALGRADE"" "CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) P" "CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 10240 NEXT 102" "40 MINEXTENTS 1 MAXEXTENTS 99
PCTINCREASE 50) TABLESPACE "SYSTEM""
Before beginning to import the data, you must have the database structure backto the last export. This is done by importing the last incremental exportfirst with "inctype=system" specified. The only information imported aresystem objects including tablespaces, users, and rollback segments. Now thatthe database structure has been recovered and the users all exist, the datacan be imported. We begin with the complete export, with "inctype=restore"specified. Continue with the cumulative exports, and finally the incrementalexports.
NOTE: the last incremental export is imported twice, once at the beginningof the recovery with 'inctype=system' for system objects, and again with'inctype=restore' for the current copy of the data.
Recovery will consist of the following imports:
IMP SYSTEM/MANAGER INCTYPE=SYSTEM FULL=Y FILE=I30 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=X1 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=C7 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=C21 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=C28 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=I29 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=I30
Below is a description of the incremental export tables owned by SYS and whatinformation they store:
SQL> desc sys.incexp Name Null? Type -------------------- -------- ---- OWNER# NOT NULL NUMBER - id # of owner = user_id in dba_users. NAME NOT NULL CHAR(30) - name of object*TYPE NOT NULL NUMBER(1) - type of object # CTIME DATE - date exported ITIME NOT NULL DATE - EXPID NOT NULL NUMBER(3) - id of file
*Key to column TYPE: 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE
SQL> desc sys.incvid Name Null? Type ----------------------------- ---- EXPID NOT NULL NUMBER(3) - HOLDS NEXT ID NUMBER.
SQL> desc sys.incfil Name Null? Type ----------------------------- ---- EXPID NOT NULL NUMBER(3) = (sys.incexp.EXPID)*EXPTYPE NOT NULL CHAR(1) - represents type of exp EXPFILE NOT NULL CHAR(100) - filename EXPDATE NOT NULL DATE - date exported EXPUSER NOT NULL CHAR(30) - user exported.
*Key to column EXPTYPE: X=Complete C=Cummulative I=Incremental
Read more at http://oracleonline.info
Thursday, June 12, 2008
Wednesday, June 11, 2008
Setting Up Enterprise Manager Preferences
Learn Oracle - Setting Up Enterprise Manager Preferences
Enterprise manager enables Oracle DBA to set up preferences that help Oracle DBA manage the database. These include the following:
Notification---These are settings that enable Oracle to email Oracle DBA alert notifications. Alerts are notifications of when a database is in an undesirable state and needs your attention. By default, the Enterprise Manager home page lists all alerts. However, set up is required for email notification.
Blackout Administration---When Oracle DBA have planned downtime for database maintenance, you can indicate to Oracle that you do not want false alerts to be sent to you. To do so, you define a black out period. See Defining Blackout Time Periods in this section.
Preferred Credentials---Enterprise Manager can automatically execute many routine administrative tasks, such as backups on your behalf. This is done using sophisticated job scheduling system built into the Enterprise. To keep your environment secure, setting up tasks for automatic execution in Enterprise Manger requires Oracle DBA to provide login information for the machine and database. In order to avoid having to enter this information every time Oracle DBA create a job or task, Enterprise Manager enables Oracle DBA to save this information as preferred credentials. Preferred credentials are stored in the database in encrypted mode to protect it from unauthorized use. See Setting Preferred Credentials in this section.
Read more at http://oracleonline.info/oem_preference.html
Enterprise manager enables Oracle DBA to set up preferences that help Oracle DBA manage the database. These include the following:
Notification---These are settings that enable Oracle to email Oracle DBA alert notifications. Alerts are notifications of when a database is in an undesirable state and needs your attention. By default, the Enterprise Manager home page lists all alerts. However, set up is required for email notification.
Blackout Administration---When Oracle DBA have planned downtime for database maintenance, you can indicate to Oracle that you do not want false alerts to be sent to you. To do so, you define a black out period. See Defining Blackout Time Periods in this section.
Preferred Credentials---Enterprise Manager can automatically execute many routine administrative tasks, such as backups on your behalf. This is done using sophisticated job scheduling system built into the Enterprise. To keep your environment secure, setting up tasks for automatic execution in Enterprise Manger requires Oracle DBA to provide login information for the machine and database. In order to avoid having to enter this information every time Oracle DBA create a job or task, Enterprise Manager enables Oracle DBA to save this information as preferred credentials. Preferred credentials are stored in the database in encrypted mode to protect it from unauthorized use. See Setting Preferred Credentials in this section.
Read more at http://oracleonline.info/oem_preference.html
Wednesday, June 4, 2008
Granting Database Control Administrative Privileges
Learn Oracle - Granting Database Control Administrative Privileges
When Oracle DBA log in to the Oracle Enterprise Manager Database Control using the SYS or SYSTEM user account, Oracle DBA are logging in as the Oracle Enterprise Manager super user. These are the only accounts that are automatically granted the roles and privileges required to access all the management functionality provided the Database Control.
To grant management access to other database users, use the following procedure:
1. Start your Web browser and log in to the Database Control as the SYS or SYSTEM database user.
2. Click Setup at the top of the Database Home page.
3. Click Administrators in the left navigation bar. If Oracle DBA need to create a new administrator, Oracle DBA must first create the user and then assign administrative privileges as described in this section.
4. Click Create to create a new Enterprise Manager user by assigning the management privileges to an existing database user.
5. Click the flashlight icon next to the Name field and select an existing database user from the pop-up window.
6. Enter the password for the selected user and click Finish. Enterprise Manager assigns the management privileges to the selected user. The selected database user is now included in the list of management users on the Setup Administrators page. All the users shown on this page can log in to the Database Control and perform database management tasks.
When Oracle DBA log in to the Oracle Enterprise Manager Database Control using the SYS or SYSTEM user account, Oracle DBA are logging in as the Oracle Enterprise Manager super user. These are the only accounts that are automatically granted the roles and privileges required to access all the management functionality provided the Database Control.
To grant management access to other database users, use the following procedure:
1. Start your Web browser and log in to the Database Control as the SYS or SYSTEM database user.
2. Click Setup at the top of the Database Home page.
3. Click Administrators in the left navigation bar. If Oracle DBA need to create a new administrator, Oracle DBA must first create the user and then assign administrative privileges as described in this section.
4. Click Create to create a new Enterprise Manager user by assigning the management privileges to an existing database user.
5. Click the flashlight icon next to the Name field and select an existing database user from the pop-up window.
6. Enter the password for the selected user and click Finish. Enterprise Manager assigns the management privileges to the selected user. The selected database user is now included in the list of management users on the Setup Administrators page. All the users shown on this page can log in to the Database Control and perform database management tasks.
Wednesday, May 28, 2008
Accessing the oracle Enterprise Manager Database Control
Accessing the Oracle Enterprise Manager Database Control
At the end of a successful database creation, Oracle Enterprise Manager is automatically launched and the database is started. At other times, provided the dbconsole process is running on the server, Oracle DBA can navigate to your Database Control from your client browser as follows:
1. Point your web browser to the following URL: http://hostname:portnumber/em For example, if Oracle DBA installed the database on a host computer named comp42, and the installer indicated that your Enterprise Manager Console HTTP port number is 5500 (also recorded in the $ORACLE_HOME/install/portlist.ini file), enter the following URL: http://comp42:5500/em If the database is up, Enterprise Manager displays the Database Control Login page.
If the database is down and needs to be started, Enterprise Manager displays the Startup/Shutdown and Perform Recovery page. If this is the case, click the Startup/Shutdown button. Oracle DBA are then prompted for the host and target database login usernames and passwords, which Oracle DBA must enter. For the database user and password use SYS and the password Oracle DBA specified during installation.
Click OK to start the database. In the Confirmation screen, click YES to start the database in open mode.
2. Log in to the database using a username that is authorized to access the Database Control. This initially could be SYS or SYSTEM. Use the password Oracle DBA specified for the account during the database installation.
The property pages across the top of the page enable Oracle DBA to access performance, administration, and maintenance pages for managing your database. The functionality provided by these pages is discussed in other chapters of this book. The various sections of the Database Home page, and related links, provide a wealth of information about the database’s environment and health. For example, the Alerts, Related Alerts, and ADDM Analysis sections warn Oracle DBA of errors and performance problems that are impacting the operation of your database. Oracle DBA can click the provided links to see more detail about the problem area, and even to obtain recommendations and SQL for resolving the problem
At the end of a successful database creation, Oracle Enterprise Manager is automatically launched and the database is started. At other times, provided the dbconsole process is running on the server, Oracle DBA can navigate to your Database Control from your client browser as follows:
1. Point your web browser to the following URL: http://hostname:portnumber/em For example, if Oracle DBA installed the database on a host computer named comp42, and the installer indicated that your Enterprise Manager Console HTTP port number is 5500 (also recorded in the $ORACLE_HOME/install/portlist.ini file), enter the following URL: http://comp42:5500/em If the database is up, Enterprise Manager displays the Database Control Login page.
If the database is down and needs to be started, Enterprise Manager displays the Startup/Shutdown and Perform Recovery page. If this is the case, click the Startup/Shutdown button. Oracle DBA are then prompted for the host and target database login usernames and passwords, which Oracle DBA must enter. For the database user and password use SYS and the password Oracle DBA specified during installation.
Click OK to start the database. In the Confirmation screen, click YES to start the database in open mode.
2. Log in to the database using a username that is authorized to access the Database Control. This initially could be SYS or SYSTEM. Use the password Oracle DBA specified for the account during the database installation.
The property pages across the top of the page enable Oracle DBA to access performance, administration, and maintenance pages for managing your database. The functionality provided by these pages is discussed in other chapters of this book. The various sections of the Database Home page, and related links, provide a wealth of information about the database’s environment and health. For example, the Alerts, Related Alerts, and ADDM Analysis sections warn Oracle DBA of errors and performance problems that are impacting the operation of your database. Oracle DBA can click the provided links to see more detail about the problem area, and even to obtain recommendations and SQL for resolving the problem
Sunday, May 25, 2008
Starting the Oracle Enterprise Manager Console
Starting the Oracle Enterprise Manager Console
To access the Oracle Enterprise Manager Console from a client browser, the dbconsole process needs to be running on the server.
This process is automatically started after installation. However, if the process is not running because of a system restart or otherwise, Oracle DBA can manually start it at the command line.
Control Panel->Administrative Tools->Services. The Services page appears.
2. Oracle services begin with Oracle. The dbconsole service is listed as OracleDBConsole. The status of this process is listed in the Status column, either Started or Stopped. Double click the service. The property page appears.
3. In the properties page, ensure that the Startup Type is either Manual or Automatic and not Disabled. Click Start, if the process is not already started. Click OK. Oracle DBA can also use the Services page to Stop the process.
Read more at http://Oracleonline.info
To access the Oracle Enterprise Manager Console from a client browser, the dbconsole process needs to be running on the server.
This process is automatically started after installation. However, if the process is not running because of a system restart or otherwise, Oracle DBA can manually start it at the command line.
2. Oracle services begin with Oracle. The dbconsole service is listed as OracleDBConsole. The status of this process is listed in the Status column, either Started or Stopped. Double click the service. The property page appears.
3. In the properties page, ensure that the Startup Type is either Manual or Automatic and not Disabled. Click Start, if the process is not already started. Click OK. Oracle DBA can also use the Services page to Stop the process.
Read more at http://Oracleonline.info
Thursday, May 22, 2008
Upgrading a Database
Perhaps your task is not to create a new database, but to upgrade an existing database. A database upgrade is necessary when Oracle DBA have an older database such as Oracle9i and need to upgrade it to the current release. Oracle provides a tool for upgrading an existing database, called the Database Upgrade Assistant (DBUA).
The Database Upgrade Assistant (DBUA) interactively steps Oracle DBA through the upgrade process and configures the database for the new Oracle Database 10g release. The Database Upgrade Assistant automates the upgrade process by performing all of the tasks normally performed manually.
The Database Upgrade Assistant makes appropriate recommendations for configuration options such as tablespaces and redo logs. Oracle DBA can then act on these recommendations. For example, the Database Upgrade Assistant recommends sizing information for the new SYSAUX tablespace, which is required in Oracle Database 10g. Before the Upgrade The Database Upgrade Assistant performs the following pre-upgrade steps: It checks for any invalid user accounts or roles It checks for any invalid datatypes It checks for any desupported character sets It checks for adequate resources, including rollback segments, tablespaces, and free disk space It optionally backs up all necessary files The Database Upgrade Assistant does not begin the upgrade until it completes all of the pre-upgrade steps.
During the Upgrade The Database Upgrade Assistant automatically modifies or creates new required tablespaces, invokes the appropriate upgrade scripts, archives the redo logs, and disables archiving during the upgrade phase. While the upgrade is running, the Database Upgrade Assistant shows the upgrade progress for each component.
The Database Upgrade Assistant writes detailed trace and log files and produces a complete HTML report for later reference. To enhance security, the Database Upgrade Assistant automatically locks new user accounts in the upgraded database. The Database Upgrade Assistant then proceeds to create new configuration files (parameter and listener files) in the new Oracle home.
Read the complete article at http://Oracleonline.info/upgrade_database.html
The Database Upgrade Assistant (DBUA) interactively steps Oracle DBA through the upgrade process and configures the database for the new Oracle Database 10g release. The Database Upgrade Assistant automates the upgrade process by performing all of the tasks normally performed manually.
The Database Upgrade Assistant makes appropriate recommendations for configuration options such as tablespaces and redo logs. Oracle DBA can then act on these recommendations. For example, the Database Upgrade Assistant recommends sizing information for the new SYSAUX tablespace, which is required in Oracle Database 10g. Before the Upgrade The Database Upgrade Assistant performs the following pre-upgrade steps: It checks for any invalid user accounts or roles It checks for any invalid datatypes It checks for any desupported character sets It checks for adequate resources, including rollback segments, tablespaces, and free disk space It optionally backs up all necessary files The Database Upgrade Assistant does not begin the upgrade until it completes all of the pre-upgrade steps.
During the Upgrade The Database Upgrade Assistant automatically modifies or creates new required tablespaces, invokes the appropriate upgrade scripts, archives the redo logs, and disables archiving during the upgrade phase. While the upgrade is running, the Database Upgrade Assistant shows the upgrade progress for each component.
The Database Upgrade Assistant writes detailed trace and log files and produces a complete HTML report for later reference. To enhance security, the Database Upgrade Assistant automatically locks new user accounts in the upgraded database. The Database Upgrade Assistant then proceeds to create new configuration files (parameter and listener files) in the new Oracle home.
Read the complete article at http://Oracleonline.info/upgrade_database.html
Tuesday, May 20, 2008
Using DBCA to Configure Database Options
Using DBCA to Configure Database Options To change the database configuration, select Configure database options in a database from the Operations page.
Oracle DBA can change your configuration from a dedicated server to a shared server. Oracle DBA can add database options that have not been previously configured for use with your database, for example: Oracle Label Security or Oracle OLAP.
Read more at http://Oracleonline.info.
Oracle DBA can change your configuration from a dedicated server to a shared server. Oracle DBA can add database options that have not been previously configured for use with your database, for example: Oracle Label Security or Oracle OLAP.
Read more at http://Oracleonline.info.
Monday, May 19, 2008
Delete database using DBCA
To delete a database using DBCA, select Delete a database from the Operations page.
When Oracle DBA select this option, DBCA deletes all the files associated with this database. On Windows, any associated services are also deleted.
Read more at http://Oracleonline.info.
When Oracle DBA select this option, DBCA deletes all the files associated with this database. On Windows, any associated services are also deleted.
Read more at http://Oracleonline.info.
Friday, May 16, 2008
Starting DBCA
Learn Oracle - Starting DBCA
To launch DBCA on the Windows operating system use the following procedure:
1. Log onto your computer as a member of the administrative group that is authorized to install Oracle software and create and run the database.
2. To launch DBCA on a Windows operating system select the following: Start > Programs > Oracle - home_name > Configuration and Migration Tools > Database Configuration Assistant To launch the DBCA on a UNIX, or as another method on a Windows operating system, enter the following at a command prompt: dbca which is typically found in $ORACLE_HOME/bin. The Welcome page appears.
3. Click Next to continue. Using DBCA to Create a Database Select Create a Database on the DBCA Operations page to begin a wizard that enables Oracle DBA to configure and create a database.
During the interview, Oracle DBA are asked for your input on the following:
Learn oracle - Database Templates
Learn oracle - Database Identification
Learn oracle - Management Options
Learn oracle - Database Credentials
Learn oracle - Storage Options
Learn oracle - Database File Locations
Learn oracle - Recovery Configuration
Learn oracle - Database Content
Learn oracle - Initialization Parameters
Learn oracle - Database Storage
Learn oracle - Creation Options
Note that most pages of the wizard provide a default setting that Oracle DBA can accept. To accept all the default parameters, Oracle DBA can click Finish at any step. Database Templates This page enables Oracle DBA to select the type of database Oracle DBA want to create.
By default, Oracle Corporation ships pre-defined templates. There are templates for Data Warehouse, General Purpose, and Transaction Processing databases. The templates contain settings optimized for workload. Click Show Details to see the configuration for each type of database. Choose the template suited to the type of workload your database will support.
If Oracle DBA are not sure, select the default General Purpose template. For more complex environments, Oracle DBA may want to select the Custom Database option. This will result in a more extensive interview and it will take longer to create your database, since a database creation script must be run.
Read the complete article at http://Oracleonline.info/dbca_create.html.
To launch DBCA on the Windows operating system use the following procedure:
1. Log onto your computer as a member of the administrative group that is authorized to install Oracle software and create and run the database.
2. To launch DBCA on a Windows operating system select the following: Start > Programs > Oracle - home_name > Configuration and Migration Tools > Database Configuration Assistant To launch the DBCA on a UNIX, or as another method on a Windows operating system, enter the following at a command prompt: dbca which is typically found in $ORACLE_HOME/bin. The Welcome page appears.
3. Click Next to continue. Using DBCA to Create a Database Select Create a Database on the DBCA Operations page to begin a wizard that enables Oracle DBA to configure and create a database.
During the interview, Oracle DBA are asked for your input on the following:
Learn oracle - Database Templates
Learn oracle - Database Identification
Learn oracle - Management Options
Learn oracle - Database Credentials
Learn oracle - Storage Options
Learn oracle - Database File Locations
Learn oracle - Recovery Configuration
Learn oracle - Database Content
Learn oracle - Initialization Parameters
Learn oracle - Database Storage
Learn oracle - Creation Options
Note that most pages of the wizard provide a default setting that Oracle DBA can accept. To accept all the default parameters, Oracle DBA can click Finish at any step. Database Templates This page enables Oracle DBA to select the type of database Oracle DBA want to create.
By default, Oracle Corporation ships pre-defined templates. There are templates for Data Warehouse, General Purpose, and Transaction Processing databases. The templates contain settings optimized for workload. Click Show Details to see the configuration for each type of database. Choose the template suited to the type of workload your database will support.
If Oracle DBA are not sure, select the default General Purpose template. For more complex environments, Oracle DBA may want to select the Custom Database option. This will result in a more extensive interview and it will take longer to create your database, since a database creation script must be run.
Read the complete article at http://Oracleonline.info/dbca_create.html.
Tuesday, May 13, 2008
Installing Your Oracle Software and Database
These steps provide a summary of the installation process. Online help is available to provide further direction in the choices that Oracle DBA make.
1. Log on to your computer as a member of the administrative group that is authorized to install Oracle software and create and run the database. Refer to your operating specific documentation or contact your system administrator find out if Oracle DBA have the necessary privileges to install new software.
2.Insert the distribution CD for the database into your CD drive. The Autorun window will appear automatically. Select Install/Deinstall Products. If downloading from Oracle’s download site, follow the instructions given on the Web site.
3. The Oracle Universal Installer Welcome page appears. Select Next to begin the installation of your software
4. Linux and Unix Only. If this is the first time that Oracle DBA are installing any Oracle software on this machine, the following occurs: The Specify Inventory Directory page appears. Oracle DBA must specify a directory for installation files and the name of an operating system group that has write permission to the directory. OUI uses this location to keep track of all Oracle software. This information is used while applying patches to or upgrading and existing installation and while deinstalling Oracle software. Note that this area is different from the Oracle home, which Oracle DBA specify later. Select Next to continue. A dialog page appears. You are asked to open a new terminal window, log in as root, and to run the oraInst.sh script. When the script finishes, return to the Oracle Universal Installer page and select Continue.
Read the complete steps at http://Oracleonline.info
1. Log on to your computer as a member of the administrative group that is authorized to install Oracle software and create and run the database. Refer to your operating specific documentation or contact your system administrator find out if Oracle DBA have the necessary privileges to install new software.
2.Insert the distribution CD for the database into your CD drive. The Autorun window will appear automatically. Select Install/Deinstall Products. If downloading from Oracle’s download site, follow the instructions given on the Web site.
3. The Oracle Universal Installer Welcome page appears. Select Next to begin the installation of your software
4. Linux and Unix Only. If this is the first time that Oracle DBA are installing any Oracle software on this machine, the following occurs: The Specify Inventory Directory page appears. Oracle DBA must specify a directory for installation files and the name of an operating system group that has write permission to the directory. OUI uses this location to keep track of all Oracle software. This information is used while applying patches to or upgrading and existing installation and while deinstalling Oracle software. Note that this area is different from the Oracle home, which Oracle DBA specify later. Select Next to continue. A dialog page appears. You are asked to open a new terminal window, log in as root, and to run the oraInst.sh script. When the script finishes, return to the Oracle Universal Installer page and select Continue.
Read the complete steps at http://Oracleonline.info
Friday, May 9, 2008
Installation Choices
Installation Choices The Oracle Universal Installer guides you through an interview phase that asks you to specify your choices for installation and database creation.
The exact sequence of pages depends on your platform. All platforms include the following choices: What database product do you want to install? You can install one of the following: Oracle Enterprise Edition—Oracle’s most full featured database product providing data management for high-end applications.
Oracle Standard Edition—A less-expensive and somewhat scaled down version of the Oracle database suitable for workgroup or department level applications Custom Install—Allows Oracle DBA to customize the Enterprise Edition by selecting products to install. Do this if Oracle DBA want to add a component to your database that would not normally be installed or prevent certain components from being installed.
Do Oracle DBA want to create a starter database in addition to installing the Oracle software? To create the database, the installer automatically launches the Database Configuration Assistant at the end of the installation.
If Oracle DBA want a preconfigured database installed, Oracle DBA can select one of the following database types, optimized according to usage: General Purpose Transaction Processing Data Warehouse If Oracle DBA want to create an custom database where Oracle DBA configure your own database structure, you can select an advanced install. What are your Database Configuration Options?
These include your Global Database Name, Oracle System Identifier (SID), and whether to install the example schemas. The Global Database Name is the full name of the database which uniquely identifies it from any other database. The global database name is of the form database_name.database_domain as in sales.us.acme.com. The database name portion sales is a simple name you call your database.
The database domain portion us.acme.com specifies the database domain in which the database is located. Together database name and domain make up the Global Database Name. Oracle recommends that you install the example schemas so that you can run many of the examples in the documentation.
Read the complete oracle article at http://Oracleonline.info/install_choice.html
The exact sequence of pages depends on your platform. All platforms include the following choices: What database product do you want to install? You can install one of the following: Oracle Enterprise Edition—Oracle’s most full featured database product providing data management for high-end applications.
Oracle Standard Edition—A less-expensive and somewhat scaled down version of the Oracle database suitable for workgroup or department level applications Custom Install—Allows Oracle DBA to customize the Enterprise Edition by selecting products to install. Do this if Oracle DBA want to add a component to your database that would not normally be installed or prevent certain components from being installed.
Do Oracle DBA want to create a starter database in addition to installing the Oracle software? To create the database, the installer automatically launches the Database Configuration Assistant at the end of the installation.
If Oracle DBA want a preconfigured database installed, Oracle DBA can select one of the following database types, optimized according to usage: General Purpose Transaction Processing Data Warehouse If Oracle DBA want to create an custom database where Oracle DBA configure your own database structure, you can select an advanced install. What are your Database Configuration Options?
These include your Global Database Name, Oracle System Identifier (SID), and whether to install the example schemas. The Global Database Name is the full name of the database which uniquely identifies it from any other database. The global database name is of the form database_name.database_domain as in sales.us.acme.com. The database name portion sales is a simple name you call your database.
The database domain portion us.acme.com specifies the database domain in which the database is located. Together database name and domain make up the Global Database Name. Oracle recommends that you install the example schemas so that you can run many of the examples in the documentation.
Read the complete oracle article at http://Oracleonline.info/install_choice.html
Thursday, May 8, 2008
Checking Prerequisites
Before the installation process, Oracle performs a number of automated prerequisite checks to ensure that your machine fulfills the basic hardware and software requirements for an Oracle install. If your machine does not meet any of the requirements, an error message is displayed.
The requirements may vary somewhat depending upon the type of machine and operation system Oracle DBA are running, but some examples of prerequisite checks include: A minimum of 512 MB of memory is available
Sufficient paging space is available. Appropriate operating system service packs or patches are installed. Appropriate files system format is being used. The installer automatically sets any operating system environment variables that the Oracle database server requires for its operation.
The Oracle Universal Installer guides Oracle DBA through an interview phase that asks Oracle DBA to specify your choices for installation and database creation. The exact sequence of pages depends on your platform. All platforms include the following choices: What database product do Oracle DBA want to install?
Oracle DBA can install one of the following: Oracle Enterprise Edition—Oracle’s most full featured database product providing data management for high-end applications. Oracle Standard Edition—A less-expensive and somewhat scaled down version of the Oracle database suitable for workgroup or department level applications Custom Install—Allows Oracle DBA to customize the Enterprise Edition by selecting products to install.
Do this if Oracle DBA want to add a component to your database that would not normally be installed or prevent certain components from being installed.
Read on to learn oracle more http://Oracleonline.info
The requirements may vary somewhat depending upon the type of machine and operation system Oracle DBA are running, but some examples of prerequisite checks include: A minimum of 512 MB of memory is available
Sufficient paging space is available. Appropriate operating system service packs or patches are installed. Appropriate files system format is being used. The installer automatically sets any operating system environment variables that the Oracle database server requires for its operation.
The Oracle Universal Installer guides Oracle DBA through an interview phase that asks Oracle DBA to specify your choices for installation and database creation. The exact sequence of pages depends on your platform. All platforms include the following choices: What database product do Oracle DBA want to install?
Oracle DBA can install one of the following: Oracle Enterprise Edition—Oracle’s most full featured database product providing data management for high-end applications. Oracle Standard Edition—A less-expensive and somewhat scaled down version of the Oracle database suitable for workgroup or department level applications Custom Install—Allows Oracle DBA to customize the Enterprise Edition by selecting products to install.
Do this if Oracle DBA want to add a component to your database that would not normally be installed or prevent certain components from being installed.
Read on to learn oracle more http://Oracleonline.info
Wednesday, May 7, 2008
How Oracle Works
The following example describes the most basic level of operations that Oracle performs. This illustrates an Oracle configuration where the Oracle DBA and associated server process are on separate computers (connected through a network).
An instance has started on the computer running Oracle (often called the host or database server).
A computer running an application (a local computer or client workstation) runs the application in a Oracle DBA process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.
The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process on behalf of the Oracle DBA process.
The Oracle DBA runs a SQL statement and commits the transaction. For example, the Oracle DBA changes a name in a row of a table.
The server process receives the statement and checks the shared pool for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, then the server process checks the Oracle DBA 's access privileges to the requested data, and the previously existing shared SQL area is used to process the statement. If not, then a new shared SQL area is
allocated for the statement, so it can be parsed and processed.
The server process retrieves any necessary data values from the actual datafile (table) or those stored in the SGA.
The server process modifies data in the system global area. The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the redo log file.
If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.
Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.
Read on Oracle Tutorial to know more...http://Oracleonline.info
An instance has started on the computer running Oracle (often called the host or database server).
A computer running an application (a local computer or client workstation) runs the application in a Oracle DBA process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.
The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process on behalf of the Oracle DBA process.
The Oracle DBA runs a SQL statement and commits the transaction. For example, the Oracle DBA changes a name in a row of a table.
The server process receives the statement and checks the shared pool for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, then the server process checks the Oracle DBA 's access privileges to the requested data, and the previously existing shared SQL area is used to process the statement. If not, then a new shared SQL area is
allocated for the statement, so it can be parsed and processed.
The server process retrieves any necessary data values from the actual datafile (table) or those stored in the SGA.
The server process modifies data in the system global area. The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the redo log file.
If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.
Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.
Read on Oracle Tutorial to know more...http://Oracleonline.info
Tuesday, May 6, 2008
Oracle Database Architecture
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data.
All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers. With this architecture, each new system can be rapidly provisioned from the pool of components.
There is no need for peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.
The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.This learn oracle article contains the following topics :
Physical Database Structure
Logical Database Structure
An Oracle Instance
Learn Oracle - Physical Database Structures
Datafiles - Every Oracle database has one or more physical datafiles. The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.
The characteristics of datafiles are:
A datafile can be associated with only one database.Datafiles can have certain characteristics set to let them automatically extend when the database runs out of space.One or more datafiles form a logical unit of database storage called a tablespace.
Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle. For example, assume that a user wants to access some data in a table of a database.
If the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory. Modified or new data is not necessarily written to a datafile immediately.
Learn more about Oracle at http://Oracleonline.info/Oracle_architecture.html
All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers. With this architecture, each new system can be rapidly provisioned from the pool of components.
There is no need for peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.
The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.This learn oracle article contains the following topics :
Physical Database Structure
Logical Database Structure
An Oracle Instance
Learn Oracle - Physical Database Structures
Datafiles - Every Oracle database has one or more physical datafiles. The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.
The characteristics of datafiles are:
A datafile can be associated with only one database.Datafiles can have certain characteristics set to let them automatically extend when the database runs out of space.One or more datafiles form a logical unit of database storage called a tablespace.
Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle. For example, assume that a user wants to access some data in a table of a database.
If the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory. Modified or new data is not necessarily written to a datafile immediately.
Learn more about Oracle at http://Oracleonline.info/Oracle_architecture.html
Monday, May 5, 2008
Tools for Administering the Database
As an Oracle DBA, you can expect to be involved in the following tasks:
The following are some of the Oracle products, tools, and utilities you can use in achieving your goals as a Oracle database administrator:
Oracle Universal Installer (OUI)The Oracle Universal Installer installs your Oracle software and options. It can automatically launch the Database Configuration Assistant to install a database.
Database Configuration Assistant (DBCA)The Database Configuration Assistant creates a database from templates that are supplied by Oracle, or you can create your own. It enables you to copy a preconfigured seed database, thus saving the time and effort of generating and customizing a database from scratch.
Database Upgrade AssistantThis Oracle Database Upgrade Assistant guides you through the upgrade of your existing database to a new Oracle release.
Oracle Net ManagerNet Manager guides you through your Oracle Net network configuration.
Oracle Enterprise ManagerThe primary tool for managing your Oracle database is Oracle Enterprise Manager, a web-based interface. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Oracle Enterprise Manager for managing your database.
In addition, Oracle Enterprise Manager also provides an interface for performance advisors and for Oracle utilities such as SQL*Loader and Recovery Manager.
Read more at http://Oracleonline.info
The following are some of the Oracle products, tools, and utilities you can use in achieving your goals as a Oracle database administrator:
Oracle Universal Installer (OUI)The Oracle Universal Installer installs your Oracle software and options. It can automatically launch the Database Configuration Assistant to install a database.
Database Configuration Assistant (DBCA)The Database Configuration Assistant creates a database from templates that are supplied by Oracle, or you can create your own. It enables you to copy a preconfigured seed database, thus saving the time and effort of generating and customizing a database from scratch.
Database Upgrade AssistantThis Oracle Database Upgrade Assistant guides you through the upgrade of your existing database to a new Oracle release.
Oracle Net ManagerNet Manager guides you through your Oracle Net network configuration.
Oracle Enterprise ManagerThe primary tool for managing your Oracle database is Oracle Enterprise Manager, a web-based interface. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Oracle Enterprise Manager for managing your database.
In addition, Oracle Enterprise Manager also provides an interface for performance advisors and for Oracle utilities such as SQL*Loader and Recovery Manager.
Read more at http://Oracleonline.info
Saturday, May 3, 2008
Common Oracle DBA Tasks
As an Oracle DBA, you can expect to be involved in the following tasks:
Installing Oracle software
Creating Oracle databases
Performing upgrades of the database and software to new release levels
Starting up and shutting down the Oracle database
Managing the database's storage structures
Managing users and security
Managing schema objects, such as tables, indexes, and views
Making Oracle database backups and performing recovery when necessary
Proactively monitoring the database's health and taking preventive or corrective action as required
Monitoring and tuning performance In a small to midsize database environment, you might be the sole person performing these tasks. In large, enterprise environments, the job is often divided among several DBAs, each with their own specialty, such as database security or database tuning.
Few more can be read at http://Oracleonline.info/dba_task.html
Installing Oracle software
Creating Oracle databases
Performing upgrades of the database and software to new release levels
Starting up and shutting down the Oracle database
Managing the database's storage structures
Managing users and security
Managing schema objects, such as tables, indexes, and views
Making Oracle database backups and performing recovery when necessary
Proactively monitoring the database's health and taking preventive or corrective action as required
Monitoring and tuning performance In a small to midsize database environment, you might be the sole person performing these tasks. In large, enterprise environments, the job is often divided among several DBAs, each with their own specialty, such as database security or database tuning.
Few more can be read at http://Oracleonline.info/dba_task.html
Friday, May 2, 2008
What is a Oracle Database
Oracle is a relational database. In a relational database, all data is stored in two-dimensional tables that are composed of rows and columns. The Oracle Database enables you to store data, update it, and efficiently retrieve it.
Oracle provides software to create and manage the Oracle database. The database consists of physical and logical structures in which system, user, and control information is stored. The software that manages the database is called the Oracle database server. Collectively, the software that runs oracle and the physical database are called the Oracle database system.
Some of Oracle's tools to access the database and create programs are:
SQL*Plus has a command line interface. With it, you can access the database and write stored procedures, you can run SQL commands to retrieve data and you can run scripts of either SQL, PL/SQL or built-in SQL*Plus commands, or a mixture of those three things.
Oracle Developer is a 4GL GUI application Builder. With Developer, you can create forms, reports, and graphics. Oracle*Forms and Oracle*Reports are two components of Oracle Developer. Earlier versions created client-server applications, but the more recent versions create web applications that run under the Oracle Application Server (OAS). OAS is a web-based application server sold by Oracle. OAS is licensed separately and is very expensive (as are its closed source competitors). The current version is OAS 10g.
HTML DB is a fairly new application builder geared toward web development (added to the DB with release 9iR2). HTML DB does not need an application server. This tool runs from the database and can be presented to the web using the Apache web server that comes with the database. Since this is not an additional license, it provides a cheaper way to develop applications. Developer is a feature-rich thick client with all of the normal GUI widgets. HTML DB is HTML-based and is very thin and limited to the HTML provided widgets.
Oracle Enterprise Manager (OEM) is the Enterprise GUI tool to manage the database. From this tool, you can perform any action in the database that is required. This tool is primarily used for administration but can also be provided to developers for application tuning and monitoring. In Oracle 10g, OEM also provides Grid control.
Read next steps at http://Oracleonline.info
Oracle provides software to create and manage the Oracle database. The database consists of physical and logical structures in which system, user, and control information is stored. The software that manages the database is called the Oracle database server. Collectively, the software that runs oracle and the physical database are called the Oracle database system.
Some of Oracle's tools to access the database and create programs are:
SQL*Plus has a command line interface. With it, you can access the database and write stored procedures, you can run SQL commands to retrieve data and you can run scripts of either SQL, PL/SQL or built-in SQL*Plus commands, or a mixture of those three things.
Oracle Developer is a 4GL GUI application Builder. With Developer, you can create forms, reports, and graphics. Oracle*Forms and Oracle*Reports are two components of Oracle Developer. Earlier versions created client-server applications, but the more recent versions create web applications that run under the Oracle Application Server (OAS). OAS is a web-based application server sold by Oracle. OAS is licensed separately and is very expensive (as are its closed source competitors). The current version is OAS 10g.
HTML DB is a fairly new application builder geared toward web development (added to the DB with release 9iR2). HTML DB does not need an application server. This tool runs from the database and can be presented to the web using the Apache web server that comes with the database. Since this is not an additional license, it provides a cheaper way to develop applications. Developer is a feature-rich thick client with all of the normal GUI widgets. HTML DB is HTML-based and is very thin and limited to the HTML provided widgets.
Oracle Enterprise Manager (OEM) is the Enterprise GUI tool to manage the database. From this tool, you can perform any action in the database that is required. This tool is primarily used for administration but can also be provided to developers for application tuning and monitoring. In Oracle 10g, OEM also provides Grid control.
Read next steps at http://Oracleonline.info
Wednesday, April 23, 2008
A Basic Introduction To SQL
The most basic syntax of SQL starts with a SELECT clause and a FROM clause. A SELECT clause tells the database WHAT you want to select.
A FROM clause tells the database where to find that data.From SQL*Plus, login as whatever user you have available and enter:SELECT table_name FROM all_tables;When I run my query,
I get a list of tables followed by the text "113 rows selected.". The SELECT clause is TABLE_NAME and the FROM clause is all_tables. The semi colon at the end tells Oracle that
you have completed your command. You MUST enter a semi-colon or a slash before Oracle will process you command.This was a very useful command but it could be made more useful. The list of tables was just that, a list.
If we were looking for a specific table, it would be hard with an unordered list like that. Let's try putting some order to it.Enter:
SELECT table_name FROM all_tables ORDER BY table_name /
Ok, there are three differences here. There is no semi colon but there is a slash, "/". The slash also tells Oracle that Oracle DBA have completed a command. The slash must appear ion column 1 on a line by itself. A semi colon can follow anywhere as long as there are no blank lines between it and the command.Type slash on a line by itself and Oracle will re-execute the previous command. Try it now.Ok,
we also added an ORDER BY clause to our command. The ORDER BY tells Oracle to, oddly enough, order the result set. What did it order by? TABLE_NAME.You can scroll through the list and find a specific table. Scroll through it now and find the table, DUAL. If you don't see this table, something is either not configured properly in your database or not configured properly with your user. DUAL is a special table provide by Oracle. It should always have one row and only have one column. That column, DUMMY, should always have the value 'X'.
Enter: SELECT table_name FROM all_tables WHERE table_name = 'DUAL' /
Read complete article at http://Oracleonline.info/what_is_sql.html
A FROM clause tells the database where to find that data.From SQL*Plus, login as whatever user you have available and enter:SELECT table_name FROM all_tables;When I run my query,
I get a list of tables followed by the text "113 rows selected.". The SELECT clause is TABLE_NAME and the FROM clause is all_tables. The semi colon at the end tells Oracle that
you have completed your command. You MUST enter a semi-colon or a slash before Oracle will process you command.This was a very useful command but it could be made more useful. The list of tables was just that, a list.
If we were looking for a specific table, it would be hard with an unordered list like that. Let's try putting some order to it.Enter:
SELECT table_name FROM all_tables ORDER BY table_name /
Ok, there are three differences here. There is no semi colon but there is a slash, "/". The slash also tells Oracle that Oracle DBA have completed a command. The slash must appear ion column 1 on a line by itself. A semi colon can follow anywhere as long as there are no blank lines between it and the command.Type slash on a line by itself and Oracle will re-execute the previous command. Try it now.Ok,
we also added an ORDER BY clause to our command. The ORDER BY tells Oracle to, oddly enough, order the result set. What did it order by? TABLE_NAME.You can scroll through the list and find a specific table. Scroll through it now and find the table, DUAL. If you don't see this table, something is either not configured properly in your database or not configured properly with your user. DUAL is a special table provide by Oracle. It should always have one row and only have one column. That column, DUMMY, should always have the value 'X'.
Enter: SELECT table_name FROM all_tables WHERE table_name = 'DUAL' /
Read complete article at http://Oracleonline.info/what_is_sql.html
Tuesday, April 22, 2008
Installing Oracle on Linux
This article is a comprehensive guide for installing Oracle9i Release 2 (9.2.0.4.0) on the Red Hat Linux Fedora Core 2 operating environment. Also included in this article is a detailed overview for applying the Oracle9i (9.2.0.5.0) patchset. Keep in mind the following assumptions throughout this article:
When installing Red Hat Linux Fedora Core 2, I install ALL components. (Everything). This makes it easier than trying to troubleshoot missing software components.
As of March 26, 2004, Oracle includes the Oracle9i database software with the 9.2.0.4.0 patchset already included. This will save considerable time since the patchset does not have to be downloaded and installed. We will, however, be applying the 9.2.0.5.0 patchset.
Although it is not required, it is recommend to apply the 9.2.0.5.0 patchset.
The post installation section includes steps for configuring the Oracle Networking files, configuring the database to start and stop when the machine is cycled, and other miscellaneous tasks.
Finally, at the end of this article, we will be creating an Oracle 9.2.0.5.0 database named ORA920 using supplied scripts. Swap Space Considerations
Ensure enough swap space is available.
Installing Oracle9i requires a minimum of 512MB of memory. (An inadequate amount of swap during the installation will cause the Oracle Universal Installer to either "hang" or "die")
To check the amount of memory / swap you have allocated, type either:
# free
- OR -
# cat /proc/swaps
- OR -
# cat /proc/meminfo grep MemTotal
Read the full article at http://Oracleonline.info/install_on_linux.html
When installing Red Hat Linux Fedora Core 2, I install ALL components. (Everything). This makes it easier than trying to troubleshoot missing software components.
As of March 26, 2004, Oracle includes the Oracle9i database software with the 9.2.0.4.0 patchset already included. This will save considerable time since the patchset does not have to be downloaded and installed. We will, however, be applying the 9.2.0.5.0 patchset.
Although it is not required, it is recommend to apply the 9.2.0.5.0 patchset.
The post installation section includes steps for configuring the Oracle Networking files, configuring the database to start and stop when the machine is cycled, and other miscellaneous tasks.
Finally, at the end of this article, we will be creating an Oracle 9.2.0.5.0 database named ORA920 using supplied scripts. Swap Space Considerations
Ensure enough swap space is available.
Installing Oracle9i requires a minimum of 512MB of memory. (An inadequate amount of swap during the installation will cause the Oracle Universal Installer to either "hang" or "die")
To check the amount of memory / swap you have allocated, type either:
# free
- OR -
# cat /proc/swaps
- OR -
# cat /proc/meminfo grep MemTotal
Read the full article at http://Oracleonline.info/install_on_linux.html
Transaction Management with LogMiner and Flashback Data Archive
LogMiner is an often ignored yet very powerful tool in the Oracle Database. It is used to extract DML statements from the redo log files—the original SQL that caused the transaction and even the SQL that can undo the transactions. In Oracle Database 11g, however, Oracle Enterprise Manager has a graphical interface to extract transaction from the redo logs using LogMiner, which makes it extremely easy to use the tool to examine and rollback transactions. (Note: As in previous versions, you can continue to use the DBMS_LOGMNR package to perform command line-driven log mining if you wish.)
Let's see an example how this is done. To enable log mining, Oracle DBA need only minor supplemental logging enabled for the database or at least the table. Flashback Transaction requires primary key logging. To enable it for the entire database, issue the following commands:
SQL> alter database add supplemental log data; Database altered. SQL> alter database add supplemental log data (primary key) columns; Database altered.Now, consider the following statements issued by an application against your database:
SQL> insert into res values (100002,sysdate,12,1);
1 row created.
SQL> commit;
Commit complete.
SQL> update res set hotel_id = 13 where res_id = 100002;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete res where res_id = 100002;
1 row deleted.
SQL> commit;
Commit complete.
Read on full article at http://Oracleonline.info/
Let's see an example how this is done. To enable log mining, Oracle DBA need only minor supplemental logging enabled for the database or at least the table. Flashback Transaction requires primary key logging. To enable it for the entire database, issue the following commands:
SQL> alter database add supplemental log data; Database altered. SQL> alter database add supplemental log data (primary key) columns; Database altered.Now, consider the following statements issued by an application against your database:
SQL> insert into res values (100002,sysdate,12,1);
1 row created.
SQL> commit;
Commit complete.
SQL> update res set hotel_id = 13 where res_id = 100002;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete res where res_id = 100002;
1 row deleted.
SQL> commit;
Commit complete.
Read on full article at http://Oracleonline.info/
Sunday, April 20, 2008
SQL Access Advisor
This Article will make you learn how Oracle 11g Sql Access Advisor helps in analyzing indexes, materialized views, and so on as it does in Oracle Database 10g, in Oracle Database 11g SQL Access Advisor also analyzes tables and queries to identify possible partitioning strategies—a great help when designing optimal schema. In Oracle Database 11g SQL Access Advisor can now provide recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structures.
The Problem
Here's a typical problem. The SQL statement below is issued by the application. The query seems resource intensive and slow.
select store_id, guest_id, count(1) cnt
from res r, trans t
where r.res_id between 2 and 40
and t.res_id = r.res_id
group by store_id, guest_id
/
This SQL touches two tables, RES and TRANS; the latter is a child table of the former. Oracle DBA have been asked to find solutions to improve query performance—and SQL Access Advisor is the perfect tool for the job.
Oracle DBA can interact with the advisor either via command line or Oracle Enterprise Manager Database Control, but using the GUI provides somewhat better value by letting Oracle DBA visualize the solution and reducing many tasks to simple pointing and clicking.
To solve the problem in the SQL using SQL Access Advisor in Enterprise Manager, follow the steps below.
The first task is, of course, to fire up Enterprise Manager. On the Database Homepage, scroll down to the bottom of the page where Oracle DBA will see several hyperlinks.
Read full article at http://Oracleonline.info
The Problem
Here's a typical problem. The SQL statement below is issued by the application. The query seems resource intensive and slow.
select store_id, guest_id, count(1) cnt
from res r, trans t
where r.res_id between 2 and 40
and t.res_id = r.res_id
group by store_id, guest_id
/
This SQL touches two tables, RES and TRANS; the latter is a child table of the former. Oracle DBA have been asked to find solutions to improve query performance—and SQL Access Advisor is the perfect tool for the job.
Oracle DBA can interact with the advisor either via command line or Oracle Enterprise Manager Database Control, but using the GUI provides somewhat better value by letting Oracle DBA visualize the solution and reducing many tasks to simple pointing and clicking.
To solve the problem in the SQL using SQL Access Advisor in Enterprise Manager, follow the steps below.
The first task is, of course, to fire up Enterprise Manager. On the Database Homepage, scroll down to the bottom of the page where Oracle DBA will see several hyperlinks.
Read full article at http://Oracleonline.info
Saturday, April 19, 2008
Database Replay - New feature of Oracle 11g
What is your biggest concern when you need to make a change in the database—be it some minor change, such as altering initialization parameters and database attributes, or major but inevitable ones such as applying patchsets? What about for your upgrade to Oracle Database 11g itself?
For me, the biggest concern is the risk of the change "breaking" something. Even the most minor changes can have a domino effect, eventually producing a visible impact.
To minimize this risk, most shops make the change in a control environment similar to the production one, apply a workload similar to your production system's, and observe the impact. It's rather trivial, at least technologically speaking, to replicate your production system but reproducing the workload is a different story. That's easier said than done.
Most organizations attempt to do that using some third-party load generation tool that can run automatically to simulate real user activity. Although this approach may be adequate in most cases, it's never a truly faithful reproduction of your production database workload. These third-party tools merely execute a pre-written query several times with different parameters; you have to supply the query to the tool and give it a range of parameters that it can use randomly. This is not a representative workload of your production system but rather merely the running of a small part of your production workload executed several times—resulting in as little as 1 percent of your application code being tested. Worst of all, these tools require you to supply all the queries from the production workload yourself, which can take weeks or months for small applications or even up to a year for complex ones.
If you could, wouldn't it be a better approach to record all database operations—DML-related and otherwise—inside the database itself, and then replay them in the very sequence they occurred?
Enter Database Replay
In Oracle Database 11g, your wish is granted, and then some. The new Database Replay tool works like a DVR inside the database. Using a unique approach, it faithfully captures all database activity beneath the level of SQL in a binary format and then replays it either in the same database or in a different one (which is exactly what you would want to do prior to making a database change). You can also customize the capture process to include certain types of activity, or perhaps exclude some.
Database Replay delivers half of what Oracle calls Oracle Database 11g's "Real Application Testing" option; the other half is provided by another tool, SQL Performance Analyzer. The main difference between these two tools is the scope involved: whereas Database Replay applies to the capture and replay of all (subject to some filtering) activities in a database, SQL Performance Analyzer allows you to capture specific SQL statements and replay them. (You can't see or access specific SQLs captured in Database Replay, while in SQL Performance Analyzer you can.) The latter offers a significant advantage for SQL tuning because you can tweak the SQL statement issued by an application and assess its impact. (SQL Performance Analyzer is covered in a forthcoming installment in this series.)
Read more about this at http://Oracleonline.info/Oracle_11g_data_replay.html
For me, the biggest concern is the risk of the change "breaking" something. Even the most minor changes can have a domino effect, eventually producing a visible impact.
To minimize this risk, most shops make the change in a control environment similar to the production one, apply a workload similar to your production system's, and observe the impact. It's rather trivial, at least technologically speaking, to replicate your production system but reproducing the workload is a different story. That's easier said than done.
Most organizations attempt to do that using some third-party load generation tool that can run automatically to simulate real user activity. Although this approach may be adequate in most cases, it's never a truly faithful reproduction of your production database workload. These third-party tools merely execute a pre-written query several times with different parameters; you have to supply the query to the tool and give it a range of parameters that it can use randomly. This is not a representative workload of your production system but rather merely the running of a small part of your production workload executed several times—resulting in as little as 1 percent of your application code being tested. Worst of all, these tools require you to supply all the queries from the production workload yourself, which can take weeks or months for small applications or even up to a year for complex ones.
If you could, wouldn't it be a better approach to record all database operations—DML-related and otherwise—inside the database itself, and then replay them in the very sequence they occurred?
Enter Database Replay
In Oracle Database 11g, your wish is granted, and then some. The new Database Replay tool works like a DVR inside the database. Using a unique approach, it faithfully captures all database activity beneath the level of SQL in a binary format and then replays it either in the same database or in a different one (which is exactly what you would want to do prior to making a database change). You can also customize the capture process to include certain types of activity, or perhaps exclude some.
Database Replay delivers half of what Oracle calls Oracle Database 11g's "Real Application Testing" option; the other half is provided by another tool, SQL Performance Analyzer. The main difference between these two tools is the scope involved: whereas Database Replay applies to the capture and replay of all (subject to some filtering) activities in a database, SQL Performance Analyzer allows you to capture specific SQL statements and replay them. (You can't see or access specific SQLs captured in Database Replay, while in SQL Performance Analyzer you can.) The latter offers a significant advantage for SQL tuning because you can tweak the SQL statement issued by an application and assess its impact. (SQL Performance Analyzer is covered in a forthcoming installment in this series.)
Read more about this at http://Oracleonline.info/Oracle_11g_data_replay.html
Thursday, April 17, 2008
Learn Oracle - Become oracle DBA in 10 simple steps
I have created Learn Oracle website for anyone who wants to Learn Oracle and perform common day-to-day administrative tasks with the Oracle Database. Prior knowledge or experience with managing databases is not required. The only requirement is a basic knowledge of computers.The website is targeted toward the following groups of Oracle users:
-Developers wanting to acquire part-time DBA skills
-Anyone managing departmental servers
-Database administrators managing an Oracle database for small or medium business (SMB)
The website is equally useful for enterprise DBAs. It provides recommended best practices and describes efficient ways of performing administrative tasks with Oracle Enterprise Manager as the primary interface.
You can access the website at http://Oracleonline.info/
-Developers wanting to acquire part-time DBA skills
-Anyone managing departmental servers
-Database administrators managing an Oracle database for small or medium business (SMB)
The website is equally useful for enterprise DBAs. It provides recommended best practices and describes efficient ways of performing administrative tasks with Oracle Enterprise Manager as the primary interface.
You can access the website at http://Oracleonline.info/
Subscribe to:
Posts (Atom)