Datapump

exp.par

userid=sys/
file=/../file.dmp
log=/../file_exp.log
owner=krg
direct=y

imp.par

userid=sys/
file=/../file.dmp
log=/../file_imp.log
fromuser=krg
touser=krg_test
buffer=10485760
commit=y


nohup exp parfile=exp.par &
nohup imp parfile=imp.par &

tail -f nohup.out

select object_type, count(*) from user_objects;



DataPump

create directory dp_stat_dir as '/u09/backups/oradb/datapump/test';

grant read, write on directory dp_stat_dir to system;

SELECT * FROM dba_directories;

export ORACLE_SID=test

expdp \'/ as sysdba\' tables=SYSTEM.STATTAB directory=dp_stat_dir dumpfile=exp_stat_file%U.dmp logfile=exp_stat_file.log

export ORACLE_SID=test

impdp \'/ as sysdba\' tables=SYSTEM.STATTAB directory=dp_stat_dir dumpfile=exp_stat_file%U.dmp logfile=imp_stat_file.log

Using DataPump Export/Import examples

I will present some expamples using DataPump.

1) Full export
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y

- Excluding a schema or a list of schemas

expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA')"


expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA','MYOTHERSCHEMA')"


expdp system include=SCHEMA:\"like 'TST%'\" directory=tmp dumpfile=tstexpuser.dmp full=y content=metadata_only


2) Exporting Schema to a dump file

expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics SCHEMAS=MYSCHEMA

3) Exporting Metadata Only

- Full Database
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics content=metadata_only full=y

-- Schema's

expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics content=metadata_only schemas=myschema



4) Importing and remaping schema using dump file
impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA REMAP_SCHEMA=MYSCHEMA:OTHERSCHEMA DUMPFILE=mydump.dmp

5) Importing schemas directly from an other database using dblink

a) impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA,MYSCHEMA1 NETWORK_LINK=MYDBLINK

b) impdp system DIRECTORY=MYDIR SCHEMAS=MYSCHEMA NETWORK_LINK=MYLINK REMAP_SCHEMA=MYSCHEMA:MYSCHEMA_NEW CONTENT=METADATA_ONLY remap_tablespace=MYTBS1:MYNEWTBS,MYTBS2:MYNEWTBS

6) Export Schemas and import with remaping tablespace (Some more parameters are used)

a) Take the export

expdp system directory=EXPORT_DIR dumpfile=mydump.dmp logfile=mydump.log estimate=statistics parallel=2 job_name=mydump_exp schemas=MYSCHEMA1,MYSCHEMA2

b) Import and remaping default tablespace and index tablespaces

impdp system directory=export_dir schemas=MYSCHEMA1,MYSCHEMA2 dumpfile=mydump.dmp logfile=mydump.log job_name=mydump_imp parallel=2 remap_tablespace=myschema1_data:users,myschema1_index:users,myschema2_data:users,myschema2_index:users

7) Export table from a schema and import to an other schema

expdp system directory=export_dir dumpfile=mytable.dmp logfile=mytable.log ESTIMATE=statistics tables=myschema.mytable

impdp system directory=export_dir tables=myschema.mytable remap_schema=myschema:mynewschema dumpfile=mytable.dmp

8) Import Transportable Tablespace with Remapping Schema also objects stats are excluded during the import

impdp system dumpfile=mydump.dmp DIRECTORY=my_dir remap_schema=ORIG_SCHEMA:NEW_SCHEMA EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS TRANSPORT_DATAFILES='/u02/oradata/myfile.dbf'









Goal

This document demonstrates how to load and unload certain objects with the Oracle10g Export DataPump and Import DataPump utilities. This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.

Incorrect usage of metadata filters, can result in errors such as:

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: "DEPT": invalid identifier

ORA-39001: invalid argument value
ORA-39041: Filter "INCLUDE" either identifies all object types or no object types.

ORA-39001: invalid argument value
ORA-39041: Filter "EXCLUDE" either identifies all object types or no object types.

ORA-39001: invalid argument value
ORA-39038: Object path "USER" is not supported for TABLE jobs.

UDE-00011: parameter include is incompatible with parameter exclude

ksh: syntax error: '(' unexpected

ORA-31655: no data or metadata objects selected for job

Fix

The examples below are based on:
- the demo schema SCOTT that is created with script: $ORACLE_HOME/rdbms/admin/scott.sql
- the directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:

-- Windows:

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;

-- Unix:

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;

Note that when creating an export DataPump dumpfile, you have to ensure that the filename does not already exist in that directory.

The following examples show how metadata can be filtered with the EXCLUDE and INCLUDE parameters.

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

With Metadata filters you can specify a set of objects to be included or excluded from an Export or Import operation, such as tables, indexes, grants, procedures.

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

Examples:

D:\> expdp ... SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:\"IN ('EMP', 'DEPT')\"
D:\> impdp ... SCHEMAS=scott INCLUDE=FUNCTION, PACKAGE, TABLE:\"= 'EMP'\"

The name_clause (specified after the colon separator) is optional. It allows a selection of specific objects within an object type. The EXCLUDE example above will export the complete SCOTT schema, except (1) the sequences owned by SCOTT and (2) the tables EMP and DEPT (with their dependent objects).
The INCLUDE example above will only import the functions, and packages (no procedures!) that are owned by SCOTT, and will also import the table EMP (with its dependent objects).
Note that if you forget to place the escape characters on the command line, and have spaces in the EXCLUDE and/or INCLUDE parameters, that the job may fail with an error such as: ORA-39071 (Value for EXCLUDE is badly formed).

A different kind of filtering is Data filtering. Data filtering is implemented through the QUERY and SAMPLE parameters, which specify restrictions on the table rows that are to be exported. For details, see also:
Note:277010.1 "Export/Import DataPump Parameter QUERY - How to Specify a Query"

2. SQL Operator usage.

The name_clause is a SQL expression that is used as a filter on the object names of the object. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. If no name_clause is provided, all objects of the specified type are excluded/included. The name clause must be separated from the object type with a colon. Examples of operator-usage (when used in a parameter file):

EXCLUDE=SEQUENCE

or:

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

or:

EXCLUDE=INDEX:"= 'MY_INDX'"

or:

INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"

or:

INCLUDE=TABLE:"> 'E'"

3. Double quotes and single quotes usage.

The name clause is separated from the object type with a colon. The name clause must be enclosed in double quotation marks. The single-quotation marks are required to delimit the name strings. Using the INCLUDE or EXCLUDE parameter in a parameter file is the preferred method.

Parameter file: exp.par
-------------------------
DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
SCHEMAS   = scott
INCLUDE   = TABLE:"IN ('EMP', 'DEPT')"

> expdp system/manager parfile=exp.par

To run this job without a parameter file, you need to escape the special characters. Incorrect escaping can result in errors such as: ksh: syntax error: '(' unexpected.
Command line examples (for Windows: type parameters on one single line) :

Windows (place all expdp parameters on a single line:):

D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp
LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEP')\"

Unix:

% expdp system/manager DIRECTORY=my_dir \
DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"

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

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

Parameter file - incorrect syntax (no tables are exported;
ORA-31655: no data or metadata objects selected for job):

INCLUDE=TABLE:"='EMP'"
INCLUDE=TABLE:"='DEPT'"

Parameter file - correct syntax:

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

or (all tables that have an 'E' and a 'P' in their name):

INCLUDE=TABLE:"LIKE '%E%'"
INCLUDE=TABLE:"LIKE '%P%'"

5. The EXCLUDE and INCLUDE parameters are mutually exclusive.

It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.

Parameter file - incorrect syntax (error: UDE-00011):

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

Parameter file - correct syntax:

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

6. Specified object types depend on the export/import Data Pump mode.

During a TABLE level export/import, certain object types that are directly related to SCHEMA or DATABASE level jobs, cannot be specified. The same applies to a SCHEMA level export/import where no DATABASE level object types can be specified.

Example (incorrect spelling of object type USERS (should be: USER);
error: ORA-39041):

DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
TABLES    = scott.emp
INCLUDE   = USERS:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT

Example (incorrect usage of object types in INCLUDE parameter for
a TABLE level export; error: ORA-39038):

DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
TABLES    = scott.emp
INCLUDE   = USER:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT

Corrected parameters (run job in schema mode):

DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
SCHEMAS   = scott
INCLUDE   = USER:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT
INCLUDE   = TABLE:"= 'EMP'"

To determine the name of the object types can be specified with EXCLUDE and INCLUDE, you can run the following query:

SET lines 200 pages 20000
COL object_path FOR a60
COL comments FOR a110

-- for database level export/import:
SELECT named, object_path, comments
  FROM database_export_objects
 WHERE object_path NOT LIKE '%/%';

-- for table schema export/import:
SELECT named, object_path, comments
  FROM schema_export_objects
 WHERE object_path NOT LIKE '%/%';

-- for table level export/import:
SELECT named, object_path, comments
  FROM table_export_objects
 WHERE object_path NOT LIKE '%/%';

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

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

SET lines 150 pages 20000
COL object_path FOR a30
COL comments FOR a110

-- for database level export/import:
SELECT named, object_path, comments
  FROM database_export_objects
 WHERE named='Y';

-- for table schema export/import:
SELECT named, object_path, comments
  FROM schema_export_objects
 WHERE named='Y';

-- for table level export/import:
SELECT named, object_path, comments
  FROM table_export_objects
 WHERE named='Y';

N OBJECT_PATH                  COMMENTS
- ---------------------------- -----------------------------------------------
Y CONSTRAINT                   Constraints (including referential constraints)
Y INDEX                        Indexes
Y PROCDEPOBJ                   Instance procedural objects
Y REF_CONSTRAINT               Referential constraints
Y TRIGGER                      Triggers on the selected tables

Note that the object type TABLE is not listed here because this is the query output of the TABLE_EXPORT_OBJECTS view: the tables are already specified with the TABLES parameter in the DataPump job.

Import DataPump example:

DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
TABLES    = scott.emp
EXCLUDE   = TRIGGER:"IN ('TRIG1', 'TRIG2')", INDEX:"= 'INDX1'", REF_CONSTRAINT

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

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

To determine which objects are dependent, e.g. for a TABLE, you can run the following query (in Oracle10g Release 2 and higher):

SET lines 200 pages 20000
COL object_path FOR a60
COL comments FOR a110

-- for TABLE dependent object types (10.2.0.x only):
SELECT named, object_path, comments
  FROM database_export_objects
WHERE object_path LIKE 'TABLE/%';

N OBJECT_PATH                                 COMMENTS
- ------------------------------------------- ------------------------------------------------
  TABLE/AUDIT_OBJ                             Object audits on the selected tables
  TABLE/COMMENT                               Table and column comments on the selected tables
  TABLE/CONSTRAINT                            Constraints (including referential constraints)
  TABLE/CONSTRAINT/REF_CONSTRAINT             Referential constraints
  TABLE/FGA_POLICY                            Fine-grained auditing policies
  TABLE/GRANT                                 Object grants on the selected tables
  TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT        Object grants on the selected tables
  TABLE/INDEX                                 Indexes
  TABLE/INDEX/STATISTICS                      Precomputed statistics
  TABLE/INSTANCE_CALLOUT                      Instance callouts
  TABLE/MATERIALIZED_VIEW_LOG                 Materialized view logs
  TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT  Grants on instance procedural objects
  TABLE/POST_INSTANCE/PROCDEPOBJ              Instance procedural objects
  TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT        Audits on instance procedural objects
  TABLE/POST_TABLE_ACTION                     Post-table actions
  TABLE/PRE_TABLE_ACTION                      Pre-table actions
  TABLE/PROCACT_INSTANCE                      Instance procedural actions
  TABLE/RLS_CONTEXT                           Fine-grained access control contexts
  TABLE/RLS_GROUP                             Fine-grained access control policy groups
  TABLE/RLS_POLICY                            Fine-grained access control policies
  TABLE/TRIGGER                               Triggers

9. Exporting or Importing a large number of objects.

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

-- create a table that contains the names of the objects:

CONNECT scott/tiger
CREATE TABLE expdp_tab (owner VARCHAR2(30),
   object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_tab VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;

-- run export DataPump job:

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

Note that this workaround is not applicable for a long list of tablespace names. In Oracle10g Release 2 an exceed of the 4000 bytes for table names or tablespace names could result in UDE-00019 (xxx parameter list is too long). This has been resolved with the fix for Bug:5714205 "EXPDP / IMPDP do not support long lists for parameters" (fixed in 10.2.0.4.0 and 11.1.0.x) where the issue with the limitation has been removed completely.

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

When specifying the EXCLUDE parameter for en Export Data Pump or Import Data Pump job, all object types for the given mode of export/import (like schema mode) will be included, except those specified in an EXCLUDE statement. If an object is excluded, all of its dependent objects are also excluded. For example, excluding a table will also exclude all indexes and triggers on the table.

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

This means:

-- Exclude all nonreferential constraints, except for
-- NOT NULL constraints and any constraints needed for
-- successful table creation and loading:

EXCLUDE=CONSTRAINT

-- Exclude referential integrity (foreign key) constraints:

EXCLUDE=REF_CONSTRAINT

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

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

EXCLUDE=SCHEMA:"='SCOTT'"

If you try to exclude a user by using a statement such as EXCLUDE=USER:"= 'SCOTT'", only the CREATE USER scott DDL statement will be excluded, and you may not get the results you expect.

11. Other issues when including objects at a Data Pump job.

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



Additional Resources

Community: Database Utilities

Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.

References

@ BUG:4053129 - EXPDP FAILS WITH ERROR ORA-39125 ORA-6502 ON LARGE LIST OF TABLE NAMES
BUG:5527129 - UDE-00019 RETURNED WHEN ATTEMPTING TO EXPORT A LONG LIST OF TABLES VIA EXPDP
BUG:5714205 - DATAPUMP IS NOT UPWARD COMPATIBLE TO EXP
NOTE:277010.1 - Export/Import DataPump Parameter QUERY - How to Specify a Query
NOTE:277905.1 - Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas
NOTE:315529.1 - DataPump Export Failed With Error ORA-6502 PL/SQL Numeric Or Value Error: Character String Buffer Too Small

DBConsole recreate

Устанавливаем переменные окружения:

$export ORACLE_HOSTNAME=dbsrv.oracle.com
$export ORACLE_HOME=/app/oracle/product/10.2.0/db_1
$export ORACLE_SID=test1
$


Ручное удаление:

Останавливаем dbconsole.

$ORACLE_HOME/bin/emctl stop dbconsole

$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
$
$ ps -ef|grep emagent
$ ps -ef|grep dbsnmp
$
$ ps -fu oracle

SQL> alter user sys identified by XXX;
SQL> alter user sysman identified by XXX;
SQL> alter user dbsnmp identified by XXX;

$$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager dbsrv.oracle.com 1521 test1 -action drop

Enter SYS user's password :
Enter repository user name : sysman
Enter repository user password :
Getting temporary tablespace from database...
Found temporary tablespace: TEMP
Checking SYS Credentials ... OK.
Dropping the repository..
Quiescing DB ... Done.
Checking for Repos User ... Exists.
Repos User exists..
Clearing EM Contexts ... OK.
Dropping EM users ...
Done.
Dropping Repos User ... Done.
Dropping Roles/Synonymns/Tablespaces ... Done.
Unquiescing DB ... Done.
Dropped Repository Successfully.


Автоматическое создание:

Останавливаем dbconsole.

$ORACLE_HOME/bin/emctl stop dbconsole

$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
$
$ ps -ef|grep emagent
$ ps -ef|grep dbsnmp
$
$ ps -fu oracle


$emca -config dbcontrol db -repos create

STARTED EMCA at Feb 10, 2010 9:53:30 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: test1
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /app/oracle/product/10.2.0/db_1

Database hostname ................ dbsrv.oracle.com
Listener port number ................ 1521
Database SID ................ test1
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Feb 10, 2010 9:54:20 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-53-30-AM.log.
Feb 10, 2010 9:54:22 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 10, 2010 9:57:38 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Feb 10, 2010 9:57:43 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://dbsrv.oracle.com.lan:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 10, 2010 9:59:25 AM
$




(ОПЦИОНАЛЬНО)

Можно попробовать еще раз пересоздать dbconsole, 
но теперь уже все автоматически.


Автоматическое удаление:

Останавливаем dbconsole.

$ORACLE_HOME/bin/emctl stop dbconsole

$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
$
$ ps -ef|grep emagent
$ ps -ef|grep dbsnmp
$
$ ps -fu oracle



$emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Feb 10, 2010 9:50:18 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: test1
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Feb 10, 2010 9:50:52 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-50-18-AM.log.
Feb 10, 2010 9:50:53 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Feb 10, 2010 9:51:01 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Feb 10, 2010 9:53:15 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 10, 2010 9:53:15 AM
$


Автоматическое создание:

Останавливаем dbconsole.

$ORACLE_HOME/bin/emctl stop dbconsole

$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
$
$ ps -ef|grep emagent
$ ps -ef|grep dbsnmp
$
$ ps -fu oracle


test1:dbsrvt:~$emca -config dbcontrol db -repos create

STARTED EMCA at Feb 10, 2010 9:53:30 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: test1
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /app/oracle/product/10.2.0/db_1

Database hostname ................ dbsrv.oracle.com
Listener port number ................ 1521
Database SID ................ test1
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Feb 10, 2010 9:54:20 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-53-30-AM.log.
Feb 10, 2010 9:54:22 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 10, 2010 9:57:38 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Feb 10, 2010 9:57:43 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://dbsrv.oracle.com.lan:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 10, 2010 9:59:25 AM
$

Oracle XML Database Recreate

SQL> select comp_name, status, version from DBA_REGISTRY;

COMP_NAME                               STATUS      VERSION
--------------------------------------- ----------- ------------------------------
Oracle Database Catalog Views           VALID       10.2.0.3.0
Oracle Database Packages and Types      VALID       10.2.0.3.0
Oracle Workspace Manager                VALID       10.2.0.1.0
JServer JAVA Virtual Machine            VALID       10.2.0.3.0
Oracle XDK                              VALID       10.2.0.3.0
Oracle Database Java Packages           VALID       10.2.0.3.0
Oracle Expression Filter                VALID       10.2.0.3.0
Oracle Enterprise Manager               VALID       10.2.0.3.0
Oracle XML Database                     INVALID     10.2.0.3.0

9 rows selected.

SQL>


vi /app/oracle/product/10.2.0/db_1/dbs/inittest1.ora

java_pool_size=150M

или так:
ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;

star


REMOVAL STEPS

1. Shutdown and restart the database

2. Connect as sysdba and run the catnoqm.sql script
SQL> set echo on
SQL> spool /export/home/oracle/xdb_removal.log
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;

3.minumums
shared_pool_size =150 MB
java_pool_size =150 MB
and
XDB tablespace must have 150m

5. Shutdown the database immediate, and startup the database normal
change_on_install


INSTALL STEPS

1. Connect as sysdba and run the catqm.sql script

SQL>set echo on
SQL>spool /export/home/oracle/xdb_install.log
SQL>@?/rdbms/admin/catqm.sql XDB SYSAUX TEMP
SQL>spool off;


SQL>@?/rdbms/admin/catxdbj.sql


SQL>select comp_name, status, version from DBA_REGISTRY 
    where comp_name= 'Oracle XML Database'

SQL>select count(*) from dba_objects where owner='XDB' and status='INVALID';


6. Restart database and listener to enable Oracle XML DB protocol access
dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)"

@/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql;

Oracle Upgrade 10.2.0.3 -> 10.2.0.4

Pre_upgrade

export ORACLE_SID=test1
export ORACLE_HOME=/app/oracle/product/10.2.0/db_1

SQL>sqlplus "/ as sysdba"
SQL>shutdown immediate
SQL>STARTUP UPGRADE
SQL>SPOOL /export/home/oracle/upgrade_info.log 
SQL>@/app/oracle/product/10.2.0/db_2/rdbms/admin/utlu102i.sql
SQL>SPOOL OFF

SQL>@/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql

SQL>shutdown immediate;
SQL>spool off
SQL>exit

Upgrade


export PATH=/bin:/usr/bin:/usr/sbin:/etc:/opt/bin:/usr/ccs/bin:/usr/ucb/bi:/usr/local/bin:/app/oracle/product/10.2.0/db_2/bin
export ORACLE_HOME=/app/oracle/product/10.2.0/db_2
$env

Смотрим чтобы все переменные указывали на db_2
и в путях и в oracle_home


sqlplus "/ as sysdba"
STARTUP UPGRADE
SPOOL /export/home/oracle/upgrade_db.log
@/app/oracle/product/10.2.0/db_2/rdbms/admin/catupgrd.sql
shutdown immediate;
startup
SPOOL OFF
exit
EOF

sqlplus "/ as sysdba"
SPOOL /tmp/invalid_test1.log
@/app/oracle/product/10.2.0/db_2/rdbms/admin/utlrp.sql;
spool off
exit
EOF


$export ORACLE_HOSTNAME=dbsrv.oracle.com
$export ORACLE_HOME=/app/oracle/product/10.2.0/db_2
$export ORACLE_SID=test1
$

emca -upgrade db



Более подробная информация:

Run the Pre-Upgrade Information Tool
If you are upgrading database manually, then you should analyze it by running 
the Pre-Upgrade Information Tool.
The Pre-Upgrade Information Tool is a SQL script that ships with the new 
Oracle Database 10g release, and must be copied to and run from the environment
of the database being upgraded. In upgrading from 9.2.0.X to 10.2.0.4, run
the Pre-Upgrade-Information Tool in the 9.2.0.X environment. In upgrading a 
database from 10.2.0.X to 10.2.0.4, run the 10.2.0.4 version of the 
utlu102i.sql script in the environment of the previous 10.2.0.X release, and 
not in the 10.2.0.4 database.
Complete the following procedure to run the Pre-Upgrade Information Tool:
1. Start the database in the UPGRADE mode:
2. SQL> STARTUP UPGRADE
3. Set the system to spool results to a log file for later analysis:
4. SQL> SPOOL upgrade_info.log 
5. Run the Pre-Upgrade Information Tool:
6. SQL> @?/rdbms/admin/utlu102i.sql
7. Turn off the spooling of script results to the log file:
8. SQL> SPOOL OFF
Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log 
file. The following is an example of the output generated by the Pre-Upgrade Information Tool:
Oracle Database 10.2 Upgrade Information Utility    02-04-2008 11:48:11
.
**********************************************************************
Database:
**********************************************************************
--> name:       X102040
--> version:    10.2.0.1.0
--> compatible: 10.2.0.1
--> blocksize:  8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 505 MB
.... AUTOEXTEND additional space required: 15 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 401 MB
.... AUTOEXTEND additional space required: 376 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 265 MB
.... AUTOEXTEND additional space required: 15 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "shared_pool_size" needs to be increased to at least 167772160
WARNING: --> "java_pool_size" needs to be increased to at least 67108864
.
**********************************************************************
Components: [The following database components will be upgraded orinstalled]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Oracle Data Mining           [upgrade]  VALID
--> Messaging Gateway            [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Oracle Ultra Search          [upgrade]  VALID
--> Oracle Label Security        [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
PL/SQL procedure successfully completed.


Upgrading a Release 10.2 Database

After you install the patch set, you must perform the following steps
on every database associated with the upgraded Oracle home:
Note:
If you do not run the catupgrd.sql script as described in this section and 
you start up a database for normal operation, then ORA-01092: 
ORACLE instance terminated. Disconnection forced errors will occur and the 
error ORA-39700: 
database must be opened with UPGRADE option will be in the alert log.

1.Log in as the Oracle software owner user.

2.For Oracle RAC installations, start listener on each node of the cluster
as follows:
  $ srvctl start listener -n node

3.If you are using Automatic Storage Management, start the Automatic 
Storage Management instance.

4.For single-instance installations, start the listener as follows:
  $ lsnrctl start

5.For single-instance installations, use SQL*Plus to log in to the database 
as the SYS user with SYSDBA privileges:
  $ sqlplus /nolog 
  SQL> CONNECT SYS AS SYSDBA
  Enter password:SYS_password
  Users of single-instance installations now proceed to step 7.
   
6.For Oracle RAC installations:
  1.Use SQL*Plus to log in to the database as the SYS user with SYSDBA 
privileges:
    $ sqlplus /nolog 
    SQL> CONNECT SYS AS SYSDBA
    Enter password: SYS_password
    SQL> STARTUP NOMOUNT
  2.Set the CLUSTER_DATABASE initialization parameter to FALSE:
    SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile; 
  3.Shut down the database:
    SQL> SHUTDOWN

7.Enter the following SQL*Plus commands:
  SQL> STARTUP UPGRADE
  SQL> SPOOL patch.log
  SQL> @?/rdbms/admin/catupgrd.sql
  SQL> SPOOL OFF

8.Review the patch.log file for errors and inspect the list of components 
that is displayed at the end of catupgrd.sql script.
  This list provides the version and status of each SERVER component in the  database.

9.If necessary, rerun the catupgrd.sql script after correcting any problems.

10. Restart the database:
      SQL> SHUTDOWN IMMEDIATE
      SQL> STARTUP

11.Run the utlrp.sql script to recompile all invalid PL/SQL packages now 
instead of when the packages are accessed for the first time. 
   This step is optional but recommended.
   SQL> @?/rdbms/admin/utlrp.sql
   Note:
   When the 10.2.0.4 patch set is applied to an Oracle Database 10g 
Standard Edition database, there may be 54 invalid objects after the utlrp.sql 
   script runs. These objects belong to the unsupported components and do
not affect the database operation.
   Ignore any messages indicating that the database contains invalid recycle 
bin objects similar to the following:
   BIN$4lzljWIt9gfgMFeM2hVSoA==$0

12.Run the following command to check the status of all the components after
the upgrade:
   SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
   In the output of the preceding command, the status of all the components 
should be VALID for a successful upgrade.

13.If you are using the Oracle Recovery Manager catalog, enter the following 
   command:
   $ rman catalog username/password@alias 
   RMAN> UPGRADE CATALOG;

14.For Oracle RAC installations:
   1.Set the CLUSTER_DATABASE initialization parameter to TRUE:
     SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile; 
   2.Restart the database:
     SQL> SHUTDOWN IMMEDIATE
     SQL> STARTUP
   3.Start any database services that you want to use:
     $ srvctl start service -d db_name -s service_name

15.To configure and secure Enterprise Manager follow these steps:
   Ensure the database and Listener are operational.
   In the case of a single instance, execute
   emca -upgrade db
   In the case of Oracle Real Application Clusters (RAC), execute

   emca -upgrade db -cluster

Solaris 10 Process Cheat Sheet

svcs - report service status

svcs -shows all enabled services
svcs -a - shows all services
svcs -l cron - shows detail information about the service
svcs -xv - shows explanations for services states that are
enabled but not running or preventing other services from running

svcadm - administration program for manipulating service instances.

svcadm enable cron - starts the cron daemon
svcadm enable -r cron - starts the cron daemon and any dependencies
svcadm disable cron - stops the cron daemon
svcadm restart cron - stops and then restarts the cron daemon
svcadm refresh cron - rereads the configuration files (like kill -HUP)
svcadm clear cron - clear the maintenance stat of the cron daemon

inetadm - administration program for manipulating inetd-controlled services

inetadm - lists inetd-controlled services
inetadm -l - lists the properties of the service
inetadm -e - enables the service
inetadm -d - disables the service

psrinfo - displays information on processes

psradm - change processor operational status

psradm -f - take a process offline
psradm -n - bring a process back online

mpstat - report per process statistics

prstat - report active process statistics

prstat -a - show processes and users totals
prstat-P cpu - show process statistics of a cpu

pmap - displays address space info on a process
pargs - prints process arguments

pargs -e - prints process environment variables

ptree - prints process tree

pfiles - prints open files and ports for a process 

Tracking undo generated by session

select
   s.sid, 
   s.username,
   r.name       "RBS name", 
   t.start_time,
   t.used_ublk  "Undo blocks",
   t.used_urec  "Undo recs"
 from
   v$session s,
   v$transaction t,
   v$rollname r
 where
   t.addr = s.taddr and
   r.usn  = t.xidusn;

Solaris 10 : Project for oracle

project.max-shm-ids:        shmmni    Maximum shared memory IDs for a project.
project.max-sem-ids:        semmni    Maximum semaphore IDs for a project.
project.max-msg-ids:        msgmni    Maximum message queue IDs for a project.
project.max-shm-memory:     shmmax    Total amount of shared memory allowed for a project.
process.max-sem-nsems:      semmsl    Maximum number of semaphores allowed per semaphore set.
process.max-sem-ops:        semopm    Maximum number of semaphore operations allowed per semop.
process.max-msg-qbytes:     msgmnb    Maximum number of bytes of messages on a message queue.
process.max-msg-messages:   msgtql    Maximum number of messages on a message queue.


project.max-shm-memory         shmmax  ***
process.max-stack-size
process.max-file-descriptor
process.max-sem-nsems          semmsl  1024   256
project.max-sem-ids            semmni  512    100
project.max-shm-ids            shmmni  512    100

==========================================================================
#projects -l
#projdel group.dba
#projadd group.dba

#usermod -K project=group.dba oradb
#usermod -K project=group.dba oragrid
#projmod -sK "project.max-shm-memory=(priv,70gb,deny)"  group.dba
#projmod -sK "process.max-stack-size=(privileged,33554432,deny)" group.dba
#projmod -sK "process.max-file-descriptor=(privileged,65536,deny)" group.dba
#projmod -sK "process.max-sem-nsems=(priv,256,deny)" group.dba
#projmod -sK "project.max-sem-ids=(priv,100,deny)" group.dba
#projmod -sK "project.max-shm-ids=(priv,100,deny)" group.dba


*  Added to fix switchover problem

set shmsys:shminfo_shmmax=171798691840
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmni=100
set shmsys:shminfo_shmmni=100

==========================================================================
#projects -l
#projdel group.dba
#projadd group.dba

#usermod -K project=group.dba oradb
#usermod -K project=group.dba oragrid
#projmod -sK "project.max-shm-memory=(priv,150gb,deny)"  group.dba
#projmod -sK "process.max-stack-size=(privileged,33554432,deny)" group.dba
#projmod -sK "process.max-file-descriptor=(privileged,65536,deny)" group.dba
#projmod -sK "process.max-sem-nsems=(priv,1024,deny)" group.dba
#projmod -sK "project.max-sem-ids=(priv,512,deny)" group.dba
#projmod -sK "project.max-shm-ids=(priv,512,deny)" group.dba


# Oracle:
set shmsys:shminfo_shmmax=75161927680
set semsys:seminfo_semmsl=1024
set semsys:seminfo_semmni=512
set shmsys:shminfo_shmmni=512

set noexec_user_stack=1

==========================================================================

$ulimit -n

256

$prctl -n process.max-file-descriptor -v 20480 -r -i process $$

$

$ulimit -n

20480

$

$ulimit -s

8192

$prctl -n process.max-stack-size -v 9223372036854775807 -r -i process $$

$

$ulimit -s

unlimited

$

==========================================================================

Установить значения параметров:

prctl -n project.max-shm-memory      -v 150gb                 -r -i process $$
prctl -n process.max-stack-size      -v 9223372036854775807   -r -i process $$
prctl -n process.max-file-descriptor -v 20480                 -r -i process $$
prctl -n process.max-sem-nsems       -v 1024                  -r -i process $$
prctl -n project.max-sem-ids         -v 512                   -r -i process $$
prctl -n project.max-shm-ids         -v 512                   -r -i process $$

Просмотр текущих значений параметров:

prctl -n project.max-shm-memory       -i process $$
prctl -n process.max-stack-size       -i process $$
prctl -n process.max-file-descriptor  -i process $$
prctl -n process.max-sem-nsems        -i process $$
prctl -n project.max-sem-ids          -i process $$
prctl -n project.max-shm-ids          -i process $$

Чтобы после перезагрузки их не потерять создадим проект:

projdel user.oradb

projadd -U oradb -K "project.max-shm-memory=(priv,150gb,deny)"  user.oradb
projmod -sK "process.max-stack-size=(privileged,9223372036854775807,deny)" user.oradb
projmod -sK "process.max-file-descriptor=(privileged,20480,deny)" user.oradb
projmod -sK "process.max-sem-nsems=(priv,1024,deny)" user.oradb
projmod -sK "project.max-sem-ids=(priv,512,deny)" user.oradb
projmod -sK "project.max-shm-ids=(priv,512,deny)" user.oradb


Или так:

# projadd group.dba
# usermod -K project=group.dba oradb
# usermod -K project=group.dba oragrid

# more /etc/project

system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
group.dba:100::::

# projmod -sK "project.max-shm-memory=(priv,150gb,deny)"  group.dba
# projmod -sK "process.max-stack-size=(privileged,9223372036854775807,deny)" group.dba
# projmod -sK "process.max-file-descriptor=(privileged,20480,deny)" group.dba
# projmod -sK "process.max-sem-nsems=(priv,1024,deny)" group.dba
# projmod -sK "project.max-sem-ids=(priv,512,deny)" group.dba
# projmod -sK "project.max-shm-ids=(priv,512,deny)" group.dba


Проверяем так:
# prctl -i project group.dba

# vi /etc/system
# Oracle:
set noexec_user_stack=1