V6 - INCREMENTAL EXPORT/IMPORT

------------------------------
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

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

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.

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

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

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

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.