Configuraing Oracle DbConsole and SYSMAN Schema

Follow following steps for re-configuration Oracle DBConsole:

Removing OracleDBConsole[SID] Service

Go to Windows Services panel and verify that OracleDBConsole[SID] is there.
If you fnd it in the list and it can NOT be deleted by emca -x [SID] command,
you need to remove it manually.

Delete OracleDBConsole[SID] from the registry:

1. Go to Start -> Run -> type regedit.
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole[SID].
3. Delete OracleDBConsole[SID] key (hit delete).

Removing Sysman Directories

In order to recreate Sysman Schema, you will need to remove old directories:

1. Navigate to %ORACLE_HOME%\DomainName_SID directory and remove it.
2. Navigate to %ORACLE_HOME%\oc4j\j2ee\OC4J_DBConsole_DomainName_SID directory and remove it.
3. Reboot the server.
4. Verify that OracleDBConsole[SID] is not listed in Windows Services.

Creating new Sysman Schema and OracleDBConsole[SID] Service.

This presumes that Sysman Schema has been previosly dropped. If not, drop it first:

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and mangement objects:
a. drop user sysman cascade;
b. drop role MGMT_USER;
c. drop user MGMT_VIEW cascade;
d. drop public synonym MGMT_TARGET_BLACKOUTS;
e. drop public synonym SETEMVIEWUSERCONTEXT;

In fact, OracleDBConsole[SID] Service is created automatically as part of
creating Sysman Schema procedure:

1. Start Database Configuration Assistant.
2. Click Next on the Welcome screen.
3. Select Configure Database Options and click Next.
4. Next screen will display database name, click Next.
5. Select Eneterprise Manager Repository and click Next.
6. Select your Server Mode (default is Dedicated) and click Finish.

This process may take several minutes and it will create dropped directories and OracleDBConsole[SID] Service.
.

ORA-01502: index is in unusable state

When try to execute query on any table with select SQL statement, Oracle returns the following error:

ORA-01502: index 'indexname' or partition of such index is in unusable state

The reason is that some indexes has been marked unusable by a direct load or by a DDL operation, or shrinking space etc. Here is simple solution.

1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition

Here we rebuilding UNUSABLE indexes onling, quering to USER_INDEXES view.

$sqlplus /nolog

Sql> connect system/oracle
Sql> SELECT count(*),status FROM all_indexes GROUP BY status;
Sql> SELECT 'alter index 'table_owner'.'index_name' rebuild online ;' FROM all_indexes where status='UNUSABLE' ORDER BY table_owner

Just execute sql statement output, it will rebuild all unused indexes.

Oracle Data Pump in Oracle Database

Oracle Database have new Data Pump utility, which is faster and more flexible as compaire to the "exp" and "imp" utilities used in old Oracle database releases. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables. Here some simple data pump examples using expdp and impdp utility.

1. Create a DIRECTORY, Grant on DIRECTORY (useing directory name as "datadir","datadump")

Create directory at the OS Level

$ cd /oradisk
$ mkdir datadump

$ sqlplus /nolog
SQL> connect /as sysdba

SQL> DROP DIRECTORY datadump;
SQL> CREATE DIRECTORY datadump AS '/oradisk/datadump';
SQL> GRANT READ,WRITE ON DIRECTORY datadump TO HR;

Export the EMPPLOYEE table from the HR user

$ expdp hr/hr directory=datadump tables=hr.employee
SQL> Drop table employee purge;

Import the .DMP file back into the HR user.

$ impdp hr/hr directory=datadump tables=employee

Check employee table after completing import.

$ sqlplus hr/hr
Sql> select * from employee;

1. Examples: Performing a table mode export

expdp system/ TABLES=sh.costs,sh.sales DUMPFILE=datadir:table.dmp NOLOGFILE=Y

2. Examples: Performing a table mode export
expdp system/oracle SCHEMAS=sh,hr DUMPFILE=datadir:schema%U.dmp LOGFILE=datadir:expschema.log

3. Example - Performing a Parallel Full Database Exportexpdp system/oracle
FULL=Y DUMPFILE=datadir:fullexp%U.dmp, FILESIZE=2g PARALLEL=4 LOGFILE=datadir:expfull.log JOB_NAME=expfull

The FULL parameter indicates that the export is a full database mode export. All data andmetadata in the database are exported.

The PARALLEL parameter specifies the maximum number of threads of activeexecution operating on behalf of the export job. This parameter enables you to maketrade-offs between resource consumption and elapsed time. For best performance, thevalue specified for PARALLEL should be at least as large as the number of output filesspecified with the DUMPFILE parameter. Each Data Pump execution thread writesexclusively to one file at a time.

The PARALLEL parameter is valid only in the Enterprise Edition of the Oracle database.To increase or decrease the value of PARALLEL during job execution, use interactivecommandmode that is described in the example below.The FILESIZE parameter will limit the maximum size of each dump file to 2 gigabytes.

You can query DBA_DATAPUMP_JOBS to monitor the current jobs.

SQL> select * from dba_datapump_jobs;

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export.

expdp scott/tiger schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" DUMPFILE:SCOTT.dmp LOGFILE=datadump:expdpSCOTT.log
expdp scott/tiger schemas=SCOTT exclude=TABLE:"= 'BONUS'" DUMPFILE:SCOTT.dmp LOGFILE=datadump:expdpSCOTT.log