Export and Import Help

[oracle@oraser Desktop]$ exp -help
Export: Release 11.2.0.1.0 - Production on Wed Jun 15 23:01:58 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
     Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword    Description (Default) 
---------------------------------------------------------
USERID     username/password                  

FULL         export entire file (N)

BUFFER     size of data buffer              

OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names

COMPRESS   import into one extent (Y) 

RECORDLENGTH length of IO record

GRANTS     export grants (Y)          

INCTYPE      incremental export type
INDEXES    export indexes (Y)         

RECORD       track incr. export (Y)
DIRECT     direct path (N)            

TRIGGERS     export triggers (Y)
LOG        log file of screen output  

STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       

PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)
OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)

FILESIZE             maximum size of each dump file

FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
VOLSIZE              number of bytes to write to each tape volume
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export
Export terminated successfully without warnings.
[oracle@oraser Desktop]$ 

[oracle@oraser Desktop]$ imp -help
Import: Release 11.2.0.1.0 - Production on Wed Jun 15 23:02:42 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

You can let Import prompt you for parameters by entering the IMP
command followed by your username/password:
     Example: IMP SCOTT/TIGER
Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords:
     Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword  Description (Default)     
--------------------------------------------------------------
USERID   username/password           

FULL         import entire file (N)
BUFFER   size of data buffer         

FROMUSER     list of owner usernames
FILE     input files (EXPDAT.DMP)    

TOUSER       list of usernames
SHOW     just list file contents (N) 

TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           

INCTYPE      incremental import type
INDEXES  import indexes (Y)          

COMMIT       commit array insert (N)
ROWS     import data rows (Y)        

PARFILE      parameter filename
LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
DESTROY                overwrite tablespace data file (N)
INDEXFILE              write table/index info to specified file
SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)
FEEDBACK               display progress every x rows(0)
TOID_NOVALIDATE        skip validation of specified type ids
FILESIZE               maximum size of each dump file
STATISTICS             import precomputed statistics (always)
RESUMABLE              suspend when a space related error is encountered(N)
RESUMABLE_NAME         text string used to identify resumable statement
RESUMABLE_TIMEOUT      wait time for RESUMABLE
COMPILE                compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION  import streams general metadata (Y)
STREAMS_INSTANTIATION  import streams instantiation metadata (N)
DATA_ONLY              import only data (N)
VOLSIZE                number of bytes in file on each volume of a file on tape
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set
Import terminated successfully without warnings.

 

ASMCMD Help

        commands:
        --------
        md_backup, md_restore
        lsattr, setattr
        cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias
        mkdir, pwd, rm, rmalias
        chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount
        offline, online, rebal, remap, umount
        dsget, dsset, lsop, shutdown, spbackup, spcopy, spget
        spmove, spset, startup
        chtmpl, lstmpl, mktmpl, rmtmpl
        chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr
        mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr
        volcreate, voldelete, voldisable, volenable, volinfo
        volresize, volset, volstat

 

Important parameters in EXPORT AND IMPORT

INCLUDE and EXCLUDE:

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/import.

 

When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import.

 

The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same.

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

 

The following code shows how they can be used as command line parameters.

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

 

INCLUDE=TABLE,VIEW,PACKAGE:"LIKE '%API'"
INCLUDE=TABLE
INCLUDE=VIEW
INCLUDE=PACKAGE:"LIKE '%API'"

 

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'"

directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

 

EXCLUDE=SCHEMA:"LIKE 'SYS%'"
EXCLUDE=SCHEMA:"IN ('OUTLN','SYSTEM','SYSMAN','FLOWS_FILES','APEX_030200',

'APEX_PUBLIC_USER','ANONYMOUS')"

 

CONTENT and QUERY:

To capture the data without the metadata use the DATA_ONLY parameter value.

expdp system/password@db10g schemas=SCOTT directory=TEST_DIR

dumpfile=scott_data.dmp logfile=expdp.log content=DATA_ONLY

 

The QUERY parameter allows you to alter the rows exported from one or more tables. The following example does a full database export, but doesn't include the data for the EMP and DEPT tables

 

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=full.dmp logfile=expdp_full.log query=SCOTT.EMP,SCOTT.DEPT:'"WHERE ROWNUM = 0"'

 

Flashback Exports:

The exp utility used the CONSISTENT=Y parameter to indicate the export should be consistent to a point in time. By default the expdp utility exports are only consistent on a per table basis. If you want all tables in the export to be consistent to the same point in time, you need to use the FLASHBACK_SCN or FLASHBACK_TIME parameter.

 

The FLASHBACK_TIME parameter value is converted to the approximate SCN for the specified time.

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read. The same wildcard can be used during the import to allow you to reference multiple files.

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4

dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4

dumpfile=SCOTT_%U.dmp logfile=impdpSCOTT.log

COMPRESS

-----------------
Exp compress=y :

Specifies how Export and Import manage the initial extent for table data.

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon Import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.

If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.

w.r.to oracle

oracle calulates all currently allocated space and uses that as size

of the initial extent for the target object.

consider

originally initial=10m next=5m minextents=6.

segment size=10+(5*5)=35m

you export this with compress=y.

import it.

all the extents are consolidated into one.

you will see that,

initial=35,next=5 and minextents=6.

segmentsize=35+(5*5)=60m

Now probably your import may fail, you may not have allocated a 60m segment!.

Case 1: COMPRESS=Y

So the actual data in the table is only 4 MB but its occupying 7 MB.

Case 2: COMPRESS=n

The size of INITIAL extent has not been altered and the space occupied is equal to the total data in the table is 4MB

 

Exp Consistent=n :

-----------------------

Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command. You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.

If you use CONSISTENT=n, each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. If a table is partitioned, each partition is exported as a separate transaction.

Therefore, if nested tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.

Table 1-2 shows a sequence of events by two users: user1 exports partitions in a table and user2 updates data in that table.

Table 1-2 Sequence of Events During Updates by Two Users

TIme Sequence User1 User2

1

Begins export of TAB:P1

No activity

2

No activity

Updates TAB:P2

Updates TAB:P1

Commits transaction

3

Ends export of TAB:P1

No activity

4

Exports TAB:P2

No activity

If the export uses CONSISTENT=y, none of the updates by user2 are written to the export file.

If the export uses CONSISTENT=n, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file because the update transaction is committed before the export of TAB:P2 begins. As a result, the user2 transaction is only partially recorded in the export file, making it inconsistent.

If you use CONSISTENT=y and the volume of updates is large, the rollback segment usage will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.

Keep in mind the following points about using CONSISTENT=y:

CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.

Export of certain metadata may require the use of the SYS schema within recursive SQL. In such situations, the use of CONSISTENT=y will be ignored. Oracle Corporation recommends that you avoid making metadata changes during an export process in which CONSISTENT=y is selected.

To minimize the time and space required for such exports, you should export tables that need to remain consistent separately from those that do not.

For example, export the emp and dept tables together in a consistent export, and then export the remainder of the database in a second pass.

A "snapshot too old" error occurs when rollback space is used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.

If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results.

To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.

 

Imp DIRECT=N

---------------------
Imp direct=n

Default: n
Specifies whether you use direct path or conventional path Export.

DIRECT=y
causes Export to extract data by reading the data directly, bypassing the SQL command-processing layer (evaluating buffer). This method can be much faster than a conventional path Export


Difference between Conventional path Export & Direct path Export

Conventional path Export.
Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into the buffer cache, and rows are transferred to the evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file.

 Direct path Export.
When using a direct path Export, the data is read from disk directly into the export session's program global area (PGA): the rows are transferred directly to the Export session's private buffer. This also means that the SQL command-processing layer (evaluation buffer) can be bypassed, because the data is already in the format that Export expects. As a result, unnecessary data conversion is avoided. The data is transferred to the Export client, which then writes the data into the export file.
. The parameter DIRECT specifies whether you use the direct path Export (DIRECT=Y) or the conventional path Export (DIRECT=N).

 You may be able to improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export.  Your exact performance gain depends upon the following factors:
- DB_BLOCK_SIZE
- the types of columns in your table
- your I/O layout (the drive receiving the export file should be separate from the disk drive where the database files reside)

For example, invoking a Direct path Export with a maximum I/O buffer of 64kb can improve the performance of the Export with almost 50%. This can be achieved by specifying the additional Export parameters DIRECT and RECORDLENGTH.

LIMITATIONS
1)  A Direct path Export does not influence the time it takes to Import the data. That is, an export file created using direct path Export or Conventional path Export, will take the same amount of time to Import.
2) You cannot use the DIRECT=Y parameter when exporting in transportable tablespace mode.  You can use the DIRECT=Y parameter when exporting in full, user or table mode
3) The parameter QUERY applies ONLY to conventional path Export. It cannot be specified in a direct path export (DIRECT=Y).
4) A Direct path Export can only export the data when the NLS_LANG environment variable of the session who is invoking the export, is equal to the database character set. If NLS_LANG is not set (default is AMERICAN_AMERICA.US7ASCII) and/or NLS_LANG is different, Export will display the warning EXP-41 and abort with EXP-0.

 

Cloning the RAC database to Non-RAC databse

To replicate your production envirionment to that patch testing can be done in the cloned environment.

soruce                                                   traget 

-------------------------------------------------------------------------
rac                                                         non-rac
oracle:oinstall                                     oracle:dba
11.2.0.2.0                                            11.2.0.2.0
/u01/app/orcle/product/11.2.0       /u02/app/oracle/11.2.0
alpha, beta                                          gama
ASM                                                     non-ASM

steps:
1. Ensure that the target server(gama) meets oracle software requirements.
 a. os must be certified
 b. kernel parameters must be set
 c. mandatory os packages should be installed
 d. os limits should be set
2. create the group/user (dba/oracle)
3. copy the software(oralce_home) from the source(either from alpha or beta)
to the target server(gama) and change the ownership if required( do not user cp/scp).
4. extract the software on the target host(gama) in the desired path (/tmp) and create directories
   a. mkdir -p /u02/app/oracle/11.2.0
   b. chown -R oralce:dba /u02/app/oracle/11.2.0
 c. ssh root@alpha  "(cd /u02/app/oracle/11.2.0 : tar -cf - 11.2.0|gzip)" |gunzip | tar -xf -
5. set the environment variables (ORACLE_HOME, ORACLE_BASE, ORACLE_SID, PATH, LD_LIBRARY_PATH etc) on the target host(gama)
-------------------------------
problem 1:
UID/GID of the owner is different
Try to login to sqlplus / as sysdba => if complains "Insufficient privileges"
 because groupNames between source(oinstall) and target (dba) are different.
Fix it:
a. take a backup of $ORACLE_HOME/rdbms/lib/config.o file.
this is where the groupName(oinstall) is hardcoded. its a binary file.
corrent the text version: vi $ORACLE_HOME/rdbms/lib/config.c (in linux)
generate a new config.o from modified config.c
  $ORACLE_HOME/bin
 #relink all
try to login to sqlplus / as sysdba  => it will work.
--------------------------------
problem 2:
Try to set a dummy pfile (initdevdb.ora) and startup nomount the dummay db.
it cant start even in nomount ==> ORA-29702 error occurs because cluster group service operation
why?  software is rac enabled but on the target nod (gama) there is no GI for cluster installed.
fix it:
ADD                                                         REMOVE
rac_on      RAC_APPLI_CLUSTER        rac_off
                  GCS and GES 

                (provided by libskgxp and libskgxn2)
rat_on      REAL_APPLI_TESTING       rat_off
part_on    PARTITIONING                    part_off
olap_on   OLAP                                     olap_off
dm_on     DATA_MINING                    dm_off
                 CORE RDBMS ENGINE

b. How can we enable/disable a specific feature?
 oracle has provied you a make script.
 $ORACLE_HOME/rdbms/lib/ins_rdbms.mk
c. How to disable RAC feature?
 cd $ORACLE_HOME/rdbms/lib
 make -f ins_rdbms.mk rac_off
d. what do i need to do next after adding/removing a specific feature?
 you must relink the oracle executable after enabling/disabling a specific feature.
 cd $ORACLE_HOME/rdbms/lib
 make -f ins_rdbms.mk ioracle or "relink all"
e. try to startup nomount the dummy instance and it should work.
-----------------------------------------------
problem 3:
a. Oracle Home path is different
Multiple script/files under ORACLE_HOME would point to invalid source path.
ex: dbca, dbua, netmgr
some softlinks also point to invalid source path.
b. After copying the software from SOURCE TO TARGET node.
The CENTROL INVENTARY (oraInventory) is not created/updated.
fix it:
a. Oracle provides a clining utility $ORACLE_HOME/clone/bin/clone.pl.
its a perl script
b. run the cloning s riipt to fix all the problems related to
 - groupName mismatch
 - path mispatch and invalid softlinks
 - Inventory updates
 - relinking
c. on the target host(gama) set the environment and goto $ORACLE_HOME/clone/bin
d. perl clone.pl ORACLE_BASE=/u02/app/oracle ORACLE_HOME=/u02/app/oracle/11.2.0 ORACLE_HOME_NAME=XXXX OS_DBA_GRP=oracle OS_OPER_GRP=dba
        - oraInventory/orainstRoot.sh
 - /u02/app/oracle/11.2.0/root.sh
you have to manually rectify the $oraInventory/ContentXML/inventory.xml
and remove the "NODES" section from it.
opatch -lsinventory
Loacl inventory still has a file: $ORACLE_HOME/inventory/ContentsXML/

oraclehomeproperties.xml
and remove the cluster_info secton from it.
-------------------------------------------
problem 4:
Choosing Cloneing Method
fix it:
a. Either invoke a 11g method to clone -- Active database clone
b. ensure the backup are locally (on gama) accessible in the same path from the target host(gama) and then invoke rman duplicate
-----------------------------------------------
DATABASE Backup:
On alpha take a hot backup of the database RACDB.
NOTE: it must be in ARCHIVELOG mode for this to happen.
rman target /
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup a compressed backupset format='/tmp/clone_db_%U.rman' tag='DBF_CLONE' database;
backup a compressed backupset format='/tmp/clone_arc_%U.rman' tag=ARC_CLONE' archivelog all skip inaccessible;
release channel ch1;
release channel ch2;
}
copy the backup pieces to the same location on the target host (gama)
start the target instance (devdb) with a dummy pfile in NOMOUNT state on the target host (gama).
ensure that the pfile contains the parameter : "_no_recovery_through_resetlogs"=true
   to recovery of database using redolog files fails using one NODE.
Define a TNS Entry on the target host(gama) which points to the SOURCE INSTANCE (RACDB1) running on alpha.
test that the TNS Entry works on gama and allows you a remote SYSDBA login to RACDB1 running on alpha.
sqlplus sys/<password>@TNSEntry as sysdba
- if this fails then fix it by creating a password file on RACDB@alpha
bounce the instance if required.
- ensure that the parameter on RACDB1 "remote_login_passwordfile" is set appropriately
now start the rman sessoin from gama.
rman target sys/<password>@TNSentry auxiliary /
-------------------------------------------------------
RAC to Non-RAC clones could fail due to
RMAN-06136: oracle error from auxiliary database
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread2) as enabled.
fix it :
add a hidden parameter "_no_recovery_through_resetlogs"=true
PROD1 : redo thread1
PROD2 : redo thered2
-------------------------------------
for PITR "set until scn '4545453';"
run{
allocate channel ch1 device type disk;
allocate auxiliary channel aux1 device type disk;
set newname datafile 1 to '/u02/app/oradata/system.dbf';
set newname datafile 2 to '/u02/app/oradata/sysaux.dbf';
set newname datafile 3 to '/u02/app/oradata/users.dbf';
set newname datafile 4 to '/u02/app/oradata/example.dbf';
set newname datafile 5 to '/u02/app/oradata/undotbs1.dbf';
set newname datafile 6 to '/u02/app/oradata/undotbs2.dbf';
set newname datafile '+DATA/racdb/tempfile/temp.263.83654525' to '/u02/app/oradata/temp01.dbf';
duplicate target database to 'devdb'
logfile
group 1 ('/u02/app/oradata/redo01.log') size 50M,
group 2 ('/u02/app/oradata/redo02.log') size 50M,
group 3 ('/u02/app/oradata/redo03.log') size 50M,
group 4 ('/u02/app/oradata/redo04.log') size 50M;
}
---------------------------------------------

Post requisites after clone
1. if required DBID is same then change it for the target database using $ORACLE_HOME/bin/nid command.
2. if ORACLE_HOME path has changed between the source and the target, the library objects would fail.
 sql>select owner, library_name, file_spec from dba_libraries where file_spce is not null;
3. if libraries are pointing to invalid files change them on the target database to appropriate paths
   create or replace library ORDSYS.ORADIMLIBS AS '/u02/app/oracle/11.2.0/lib/libordim11.so';
4. compile the invalid objects.
  sql> exec utl_recomp.recomp_parallel(2);
  sql> select count(*) from dba_objects where status='INVALID';
 this should match between source and target database.
5. correct all the DIRECTORY objects dba_directories;
 sql> select dierectory_name, directory_path from dba_directories;
 sql> create or replace directory <dirname> as <corrent_path>;
6. fix all db_links. if not taken care of it could lead to disaster.
7. drop unwanted UNDO tablespaces.
8. register the target database (devdb) with rman recovery catalog (optional)

 

Schema Refresh acvity

1. In the target database
Get the target schema metadata and privileges.

2. drop target schema.
drop user user_name cascade.
3. In the source database
Check dba_directories for the datapump directory.
4. Check datapump directory mount point has enough space or not.
note: use expdp with estimate_only to know datapunp dump size.
5. export source schema dump file to target server datapunp directory.
6. recreate target schema using target schema metadata.
7. import source schema data your target schema
impdp parfile=impdp.par

export or impport errors or warnings.
-----------------------------------
1. temparary tablespace errors.
2. index or table already exist.
3. indexes are created with warnings.
4. fine grain previliges.

=========== Get DDL of USER ===========

set long 9999999
set lines 100
set page size 0
set head off
set feedback off
SPOOL MDM_ETL_STG_ddl.sql
execute dbms_METADATA.SET_TRANSFORM_PARAM

(DBMS_METADATA.SESSION_TRANSFORM,  'SQLTERMINATOR',true);
select (case
        when ((select count(*)
               from dba_users
               where username = 'MDM_ETL_STG') > 0)
        then dbms_metadata.get_ddl ('USER', 'MDM_ETL_STG')
        else to_clob (' -- Note: User not found!')
        end ) Extracted_DDL from dual
UNION ALL
select (case
        when ((select count(*)
               from dba_ts_quotas
               where username = 'MDM_ETL_STG') > 0)
        then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'MDM_ETL_STG')
        else to_clob (' -- Note: No TS Quotas found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from dba_role_privs
               where grantee = 'MDM_ETL_STG') > 0)
        then dbms_metadata.get_granted_ddl ('ROLE_GRANT', 'MDM_ETL_STG')
        else to_clob (' -- Note: No granted Roles found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from dba_sys_privs
               where grantee = 'MDM_ETL_STG') > 0)
        then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', 'MDM_ETL_STG')
        else to_clob (' -- Note: No System Privileges found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from dba_tab_privs
               where grantee = 'MDM_ETL_STG') > 0)
        then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', 'MDM_ETL_STG')
        else to_clob (' -- Note: No Object Privileges found!')
        end ) from dual
/
spool off;
 

DATAPUMP (Expdp/Impdp) Help

EXPDP:
----------

Export: Release 11.2.0.4.0 - Production on Wed Jun 15 22:41:03 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC.
Valid keyword values are: [Y] and N.
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS
Data layer option flags.
Valid keyword values are: XML_CLOBS.
DIRECTORY
Directory object to be used for dump and log files.
DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.
ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.
ESTIMATE_ONLY
Calculate job estimates without performing the export.
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".
FILESIZE
Specify the size of each dump file in units of bytes.
FLASHBACK_SCN
SCN used to reset session snapshot.
FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
FULL
Export entire database [N].
HELP
Display Help messages [N].
INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.
JOB_NAME
Name of export job to create.
LOGFILE
Specify log file name [export.log].
NETWORK_LINK
Name of remote database link to the source system.
NOLOGFILE
Do not write log file [N].
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file name.
QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].
SAMPLE
Percentage of data to be exported.
SCHEMAS
List of schemas to export [login schema].
SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.
SOURCE_EDITION
Edition to be used for extracting metadata.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES
Identifies a list of tablespaces to export.
TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].
TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].
TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.
VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
ADD_FILE
Add dumpfile to dumpfile set.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].
START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.




IMPDP
---------


Import: Release 11.2.0.4.0 - Production on Wed Jun 15 22:41:31 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:
     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC.
Valid keyword values are: [Y] and N.
CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS
Data layer option flags.
Valid keywords are: SKIP_CONSTRAINT_ERRORS.
DIRECTORY
Directory object to be used for dump, log and SQL files.
DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.
ESTIMATE
Calculate job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".
FLASHBACK_SCN
SCN used to reset session snapshot.
FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
FULL
Import everything from source [Y].
HELP
Display help messages [N].
INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.
JOB_NAME
Name of import job to create.
LOGFILE
Log file name [import.log].
NETWORK_LINK
Name of remote database link to the source system.
NOLOGFILE
Do not write log file [N].
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file.
PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].
QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REMAP_DATAFILE
Redefine data file references in all DDL statements.
REMAP_SCHEMA
Objects from one schema are loaded into another schema.
REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.
REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.
REUSE_DATAFILES
Tablespace will be initialized if it already exists [N].
SCHEMAS
List of schemas to import.
SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.
SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.
SOURCE_EDITION
Edition to be used for extracting metadata.
SQLFILE
Write all the SQL DDL to a specified file.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STREAMS_CONFIGURATION
Enable the loading of Streams metadata
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES
Identifies a list of tablespaces to import.
TARGET_EDITION
Edition to be used for loading metadata.
TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.
TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.
TRANSPORT_DATAFILES
List of data files to be imported by transportable mode.
TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].
TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.
 

Database binaries cloning

From One Server To Other

 

1. Check the oracle home to clone

2. Check the oracle home size

3. Create the directory tree on the remote server and change ownership to oracle

4. On source execute tar command to copy over the network the ORACLE_HOME
directories to the target server

4. Once the tar finishes to copy on the target oracle home check the total size

5. Using the Perl script you can link soft links given in the directory
$ORACLE_HOME/clone/bin

6. First I need to detach the inventory because the server was itself a clone of the source.
 perl clone.pl
ORACLE_BASE=/u02/app/oracle
ORACLE_HOME=/u02/app/oracle/11.2.0
ORACLE_HOME_NAME=XXXX
OS_DBA_GRP=oracle 

OS_OPER_GRP=dba

7. Once the inventory was detached from the old server version I can run the cloning procedure

8. The last step is to execute as root the root.sh script:

9. Check the Cloned Home

10. Post Clone Checkups

 

How To Clone A Database Home From One Server To Other

 

1. Check the oracle home to clone

[oracle@lnx1 dbhome_1]$ opatch lsinv

 

2. Check the oracle home size

[oracle@lnx1 ~]$ cd /oracle/app/product/11.2.0/dbhome_1

[oracle@lnx1 dbhome_1]$

[oracle@lnx1 dbhome_1]$ du -h . | tail -1

4.5G

 

3. Create the directory tree on the remote server and change ownership to oracle

 [oracle@lnx2 ~]$ su -

Password:

[root@lnx2 ~]# mkdir -p /oracle/app/product/11.2.0/dbhome_1

[root@lnx2 ~]# chown -R oracle:dba /oracle

 

4. On source execute tar command to copy over the network the oracle_home

directories to the target server

[oracle@lnx1 ~]$ cd /oracle/app/product/11.2.0/dbhome_1

[oracle@lnx1 dbhome_1]$ tar cvf - . | ( ssh lnx2 "cd /oracle/app/product/11.2.0/dbhome_1 ; tar xvf -" )

Once the tar finishes to copy on the target oracle home check the total size

 

[oracle@lnx2 ~]$ cd /oracle/app/product/11.2.0/dbhome_1

[oracle@lnx2 dbhome_1]$ du -h . | tail -1

4.5G

 

5. Using the OUI to finish the cloning process

First I need to detach the inventory because the server was itself a clone of the

source

[oracle@lnx2 ~]$ cd /oracle/app/product/11.2.0/dbhome_1/oui/bin

 

6. [oracle@lnx2 bin]$

./runInstaller -detachHome

ORACLE_HOME="/oracle/app/product/11.2.0/dbhome_1"

Starting Oracle Universal Installer...

How To Clone A Database Home

From One Server To Other

Checking swap space: must be greater than 500 MB.   Actual 1992 MB        Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /home/oracle/oraInventory

'DetachHome' was successful.

Once the inventory was detached from the old server version I can run the

cloning procedure:

 

7. [oracle@lnx2 bin]$

./runInstaller -clone -silent -ignorePreReq

ORACLE_HOME="/oracle/app/product/11.2.0/dbhome_1" ORACLE_HOME_NAME="dbhome_11gR2"

ORACLE_BASE="/oracle/app" OSDBA_GROUP=dba OSOPER_GROUP=dba

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 1992 MB        Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-11-05_11-28-15AM. Please

wait ...[oracle@lnx2 bin]$ Oracle Universal Installer, Version 11.2.0.1.0 Production

Copyright (C) 1999, 2009, Oracle. All rights reserved.

You can find the log of this install session at:

 /home/oracle/oraInventory/logs/cloneActions2010-11-05_11-28-15AM.log

.................................................................................................... 100% Done.

Installation in progress (Friday, November 5, 2010 11:28:48 AM PDT)

..............................................................................                                                      78% Done.

Install successful

Linking in progress (Friday, November 5, 2010 11:29:21 AM PDT)

Link successful

Setup in progress (Friday, November 5, 2010 11:31:59 AM PDT)

Setup successful

End of install phases.(Friday, November 5, 2010 11:35:50 AM PDT)

Starting to execute configuration assistants

Configuration assistant "Oracle Configuration Manager Clone" succeeded

WARNING:

The following configuration scripts need to be executed as the "root" user.

/oracle/app/product/11.2.0/dbhome_1/root.sh

To execute the configuration scripts:

        1. Open a terminal window

        2. Log in as "root"

        3. Run the scripts

The cloning of dbhome_11gR2 was successful.

Please check '/home/oracle/oraInventory/logs/cloneActions2010-11-05_11-28-15AM.log' for more details.

The last step is to execute as root the root.sh script:

 

8. [root@lnx2 ~]# /oracle/app/product/11.2.0/dbhome_1/root.sh

Check /oracle/app/product/11.2.0/dbhome_1/install/root_lnx2_2010-11-05_11-39-06.log for the output of

root script

How To Clone A Database Home

From One Server To Other

Check the Cloned Home

[oracle@lnx2 ~]$ opatch lsinv

Invoking OPatch 11.2.0.1.3

Oracle Interim Patch Installer version 11.2.0.1.3

Copyright (c) 2010, Oracle Corporation.  All rights reserved.

Oracle Home           : /oracle/app/product/11.2.0/dbhome_1

Central Inventory : /home/oracle/oraInventory

   from               : /etc/oraInst.loc

OPatch version        : 11.2.0.1.3

OUI version           : 11.2.0.1.0

OUI location          : /oracle/app/product/11.2.0/dbhome_1/oui

Log file location : /oracle/app/product/11.2.0/dbhome_1/
cfgtoollogs/opatch/opatch2010-11-05_11-46-19AM.log

Patch history file: /oracle/app/product/11.2.0/dbhome_1/
cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle/app/product/11.2.0/dbhome_1/
cfgtoollogs/opatch/lsinv/lsinventory2010-

11-05_11-46-19AM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g                                                      11.2.0.1.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  9952216          : applied on Fri Nov 05 07:13:34 PDT 2010

Unique Patch ID:  12825033

   Created on 13 Sep 2010, 04:55:09 hrs PST8PDT

   Bugs fixed:

         9068088, 9363384, 8865718, 8898852, 8801119, 9054253, 8725286, 8974548

         9093300, 8909984, 8755082, 8780372, 9952216, 8664189, 8769569, 7519406

         9471411, 9302343, 8822531, 7705591, 8650719, 9637033, 8883722, 8639114

         8723477, 8729793, 8919682, 8856478, 9001453, 8733749, 8565708, 8735201

         8684517, 8870559, 8773383, 8981059, 8812705, 9488887, 8813366, 9242411

         8822832, 8897784, 8760714, 8775569, 8671349, 8898589, 9714832, 8642202

         9011088, 9369797, 9170608, 9165206, 8834636, 8891037, 8431487, 8570322

         8685253, 8872096, 8718952, 8799099, 9032717, 9399090, 9713537, 9546223

         8588519, 8783738, 8834425, 9454385, 8856497, 8890026, 8721315, 8818175

         8674263, 9145541, 8720447, 9272086, 9467635, 9010222, 9102860, 9197917

         8991997, 8661168, 8803762, 8769239, 9654983, 8546356, 8706590, 8778277

         9058865, 8815639, 9971778, 9971779, 9027691, 9454036, 9454037, 9454038

         9255542, 8761974, 9275072, 8496830, 8702892, 8818983, 8475069, 8875671

         9328668, 8891929, 8798317, 8782959, 9971780, 8774868, 8820324, 8544696

         8702535, 9406607, 9952260, 8268775, 9036013, 9363145, 8933870, 8405205

         9467727, 8822365, 9676419, 8761260, 8790767, 8795418, 8913269, 8717461

         8861700, 9531984, 8607693, 8330783, 8780281, 8784929, 8780711, 9341448

         9015983, 8828328, 9119194, 8832205, 8665189, 8717031, 9482399, 9676420

         9399991, 8821286, 8633358, 9321701, 9231605, 9655013, 8796511, 9167285

         8782971, 8756598, 9390484, 8703064, 9066116, 9007102, 9461782, 9382101

         8505803, 9352237, 8753903, 9216806, 8918433, 9057443, 8790561, 8795792

         8733225, 9067282, 8928276, 8837736, 9210925

How To Clone A Database Home

From One Server To Other

--------------------------------------------------------------------------------

OPatch succeeded.

Post Clone Checkups

It may be needed to update $ORACLE_HOME/dbs,  $ORACLE_HOME/network/admin files to make them match

the new server.

$ORACLE_HOME/perl/bin/perl clone.pl ORACLE_BASE="/u01/app/oracle/" ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
OSDBA_GROUP=dba OSOPER_GROUP=oper -defaultHomeName