Crerating an OCM Response File for a Grid Infrastructure (GI) Home Prior to Patching

Crerating an OCM Response File for a Grid Infrastructure (GI) Home Prior to Patching
Before applying any patches to the GI home, an ocm.rsp file should be created.  As the GI owner, set the GI environment:

$ export ORACLE_BASE=/u01/app
$ export ORACLE_HOME=$ORACLE_HOME/11.2.0/grid

Create the ocm.rsp file:



grid@said-db-201$ $ORACLE_HOME/OPatch/ocm/bin/emocmrsp

When prompted for an email address, either leave blank or provide an email address.  The file will be created in the directory
from which the above command was executed.

For example, when applying a patch to GI and the patch files have been unzipped to a directory, the opatch command will be executed as follows:

# opatch auto -oh

grid@said-db-201$./opatch auto /orasoft/soft/Patch/Patch/24436338 -ocmrf /export/home/grid/ocm.rsp

When pompted:
OPatch is bundled with OCM, Enter the absolute OCM response file path:

Enter the patch of the ocm.rsp file e.g.
/export/home/grid/ocm.rsp



Reference: http://anotherdatabaseblog.blogspot.com/2011/10/crerating-ocm-response-file-for-gi-home.html

Import Method using IMPDP to apply only incremental rows

Import Method using IMPDP to apply only incremental rows 

Step 1: Take a full backup of emp table

[oracle@oracledb ~]$ expdp system/passwd direcctory=dpump tables=scott.emp dumpfile=emp.dmp logfile=emp.log
LRM-00101: unknown parameter name 'direcctory'

[oracle@oracledb ~]$ expdp system/sys123 directory=dpump tables=scott.emp dumpfile=emp.dmp logfile=emp.log

Export: Release 11.2.0.3.0 - Production on Sat Mar 25 12:11:41 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dpump tables=scott.emp dumpfile=emp.dmp logfile=emp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/emp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 12:11:57

SQL> select empno from emp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.

Step 2: Now insert one row into EMP table

SQL> insert into emp values(1001,'sirat','MGR','7369',sysdate,1000,null,20);

1 row created.

SQL> commit;

Commit complete.

Step 3: Now take a incremental backup using QUERY and DATA_ONLY parameter in IMPDP command

[oracle@oracledb ~]$ cat data.par
tables=SCOTT.EMP
directory=dpump
DUMPFILE=emp_incre.dmp
logfile=emp_incre.log
query=SCOTT.EMP:"where empno=1001"
content=DATA_ONLY

[oracle@oracledb ~]$

[oracle@oracledb ~]$ expdp system/passwd parfile=data.par

Export: Release 11.2.0.3.0 - Production on Sat Mar 25 21:32:14 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=data.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "SCOTT"."EMP"                               8.031 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/emp_incre.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 21:32:16

[oracle@oracledb ~]$


Step 4: Delete one row from emp table

SQL> delete from emp where empno=1001;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select empno from emp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.

SQL>


Step 5: Apply incremental backup APPEND option in IMPDP command

[oracle@oracledb ~]$ impdp system/passwd directory=dpump dumpfile=emp_incre.dmp logfile=imp1.log table_exists_action=append

Import: Release 11.2.0.3.0 - Production on Sat Mar 25 21:36:31 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dpump dumpfile=emp_incre.dmp logfile=imp1.log table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                               8.031 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 21:36:34

[oracle@oracledb ~]$

Now check Data append in emp table or not

SQL> select empno from emp;

     EMPNO
----------
      1001
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844

     EMPNO
----------
      7876
      7900
      7902
      7934

15 rows selected.

SQL>


Thats it!!!!!!!!!!!!!!!!!!!!!!!!!

Reference :  http://www.acehints.com/2012/05/datapump-impdp-tableexistsaction-append.html

Export/Import in Datapump using query parameter

Purpose

Enables you to filter the data that is exported by specifying a clause for a SELECT statement, which is applied to all/multiple tables in the export job or to a specific table.

Solution

[oracle@said ~]$ cat bak.sh
expdp system/passwd@orcl parfile=/home/oracle/parameter.par
[oracle@said ~]$


[oracle@said ~]$ cat parameter.par
tables=SCOTT.EMP
query=SCOTT.EMP:"WHERE (liability_issued - nvl(liability_reversed, 0) != 0
or  paid_margin_amount_lcy - nvl(refunded_margin_lcy, 0) != 0
or  indem_liability_issued - nvl(indem_liability_reversed, 0) != 0)"
directory=dpump
dumpfile=emp.dmp
logfile=emp.log
[oracle@said ~]$  ./back.sh

Export: Release 11.2.0.3.0 - Production on Thu Mar 16 14:39:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orcl parfile=/home/oracle/parameter.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 683.1 GB
Processing object type TABLE_EXPORT/TABLE/TABLE

.

.

.

.

.

.