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.