MULTIPLE QUERIES WITH EXPDP DATA PUMP

Reason:
If you don't want to pump big tables you can use multiple QUERY predicate.
expdp  "'/ as sysdba'"   parfile=expdp.dat
or
expdp  system/manager@DB parfile=expdp.dat 

Note:
cat expdp.dat file

DUMPFILE="dat.dmp"
LOGFILE="exp_dat.log"
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=('RMS_ACQ_B')
QUERY='RMS_ACQ_B.RISKMAN_AUTH_TMP:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.MSG_HEAD_ARMS:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.RISKMAN_SLIPS_TMP:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.RISKMAN_SLP_FULL:"WHERE rownum<1"'
CONTENT=ALL
JOB_NAME='v1'

REUSE_DUMPFILES=Y #COMPATIBLE=11.2 CAN BE OVERWRITTEN
#COMPRESSION=ALL #COMPATIBLE=11.2

example:

[oracle@test ~]$ expdp SYSTEM/manager parfile=expdp.dat

Export: Release 11.2.0.2.0 - Production on Mon Sep 26 17:19:27 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Starting "RMS_PREV"."V2":  system/******** parfile=expdp.dat
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32.11 GB
Processing object type SCHEMA_EXPORT/USER
[...]
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . exported "RMS_ACQ_B"."RISKMAN_AUTH_TMP"              16.21 KB       0 rows
. . exported "RMS_ACQ_B"."MSG_HEAD_ARMS"                 20.10 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_SLIPS_TMP"             14.89 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_SLP_FULL"              13.17 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_AUTH_TMP_NEW"          320.7 MB  723385 rows
 

If you need to  exclude TEST tables
(syntax note add \ if QUERY= not on Windows or not in parfile; on Linux must prefix with \ most punctuation charactersexample:expdp user/password QUERY='user.table:\"WHERE rownum\<1\"')

example:

expdp SYSTEM/manager DUMPFILE=dat.dmp EXCLUDE=table:\"like \'%TEST%\'\" QUERY=RMS_ACQ_B.RISKMAN_AUTH_TMP:\"WHERE rownum\<1\" LOGFILE=exp_dat.log SCHEMAS=RMS_ACQ_B

11gR2 Oracle Grid infrastructure with ASM for a Standalone Server with Virtual RAW disks

REASON

1.                   I have RAC(Real Application Cluster) 11gR2 in production. For DR(Disaster Recovery) we don't need RAC. So I need to install Grid Infrastructure with ASM for Standalone serve on remote server and there is only one server with file system ext3 one mount point /opt. I need to configure ASM before we switching primary RAC DB to DR Single instance with Grid Infrastructure. Main idea we mount to DR server all our ASM disks.

Create Virtual RAW disk on Single DR server, to able install GRID infrastructure;

mkdir /opt/asm

dd if=/dev/zero of=/opt/asm/disk1 bs=1024 count=2097152
dd if=/dev/zero of=/opt/asm/disk2 bs=1024 count=2097152
dd if=/dev/zero of=/opt/asm/disk3 bs=1024 count=2097152

chown grid:asmadmin: /opt/asm/disk*

losetup /dev/loop1 /opt/asm/disk1
losetup /dev/loop2 /opt/asm/disk2
losetup /dev/loop3 /opt/asm/disk3

raw /dev/raw/raw1 /dev/loop1
raw /dev/raw/raw2 /dev/loop2
raw /dev/raw/raw3 /dev/loop3

chown grid:asmadmin /dev/raw/raw[1-3]

install Grid Infrastructure and Oracle database with ASM support

 

[root@lv-dr-rtpsdb3 oracle]# /opt/app/oraInventory/orainstRoot.sh

Changing permissions of /opt/app/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

Changing groupname of /opt/app/oraInventory to oinstall.

The execution of the script is complete.

[root@lv-dr-rtpsdb3 oracle]# /opt/app/11.2.0/grid/root.sh

Running Oracle 11g root script...

The following environment variables are set as:

ORACLE_OWNER= grid

ORACLE_HOME=  /opt/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Using configuration parameter file: /opt/app/11.2.0/grid/crs/install/crsconfig_p                   arams

Creating trace directory

LOCAL ADD MODE

Creating OCR keys for user 'grid', privgrp 'oinstall'..

Operation successful.

LOCAL ONLY MODE

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

CRS-4664: Node lv-dr-rtpsdb3 successfully pinned.

Adding daemon to inittab

ACFS-9300: ADVM/ACFS distribution files found.

ACFS-9307: Installing requested ADVM/ACFS software.

ACFS-9308: Loading installed ADVM/ACFS drivers.

ACFS-9321: Creating udev for ADVM/ACFS.

ACFS-9323: Creating module dependencies - this may take some time.

ACFS-9327: Verifying ADVM/ACFS devices.

ACFS-9309: ADVM/ACFS installation correctness verified.

lv-dr-rtpsdb3     2011/08/30 15:09:48     /opt/app/11.2.0/grid/cdata/lv-dr-rtpsd                   b3/backup_20110830_150948.olr

Successfully configured Oracle Grid Infrastructure for a Standalone Server

[root@lv-dr-rtpsdb3 oracle]#

Than substitute ASM init+ASM.ora with your production RAC ASM configuration file, restart HSA

under user grid or root

crsctl stop has
crsctl start has
CRS-4123: Oracle High Availability Services has been started.

than under oracle check

if ASYNC is ON?, if you see output it is linked, it should be linked
ldd $ORACLE_HOME/bin/oracle | grep libaio
nm $ORACLE_HOME/bin/oracle | grep io_getevent
 
if RAC is LINEKED to ORACLE, 0 is not linked, it should be 0. To swith off cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk rac_off ioracle

cd $ORACLE_HOME/rdbms/lib
nm -r libknlopt.a | grep -c kcsm.o
 
take initDB.ora file switch off claster configuration

and startup your DB on ASM as a single instance;

Install Oracle 11gR2 Express Edition on RHEL5 x64

Configure OS and the database






id
root


groupadd -g 550 dba
groupadd -g 551 oinstall
useradd -c "Oracle software owner" -g oinstall -G dba -u 550 oracle -d /opt/oracle
mkdir /opt/oracle
chown oracle.oinstall /opt/oracle
passwd oracle

vi /etc/sysctl.conf

kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576


 /etc/init.d/oracle-xe configure



Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press <Enter> to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:n

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

su - oracle

vi .bach_profile

export ORACLE_SID=XE
export ORACLE_BASE=$HOME
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export NLS_LANG=american_america.utf8
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH