Wednesday, November 26, 2008

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 content=metadata_only full=y

-- Schema's

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

-- Excluding DBA Scheduler JOBS

expdp system directory=exp_dir content=metadata_only "exclude=procobj:\"in(SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,66,67,68,69,71,72,74))\"" schemas=STAGING,STARSAMA,STARFIN dumpfile=dwh_schemas_metadata.dmp logifle=dwh_schemas_metadata.log


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'

Unsecuring dbconsole(10gR2)

After applying 10gR2 Patch Set 3 (10.2.0.4) the dbconsole is always configured as secured. If you have problem with https then you must unsecure the dbconsole.

emctl unsecure dbconsole

Warning : Unsecuring the dbconsole does not change the prot number to the unsecure default (5500). It remains as the https port (1158).

E.g

https://hostname:1158/em/console -> http://hostname:1158/em/console

10gR2 EMCA FAILS WITH ERROR ORA-06502

There is a bug during the create of the EM Repository.
When hostname exceeds 32 characters emca will fail with
ORA-06502
PL/SQL: numeric or value error: character string buffer too small

Verify if this is the case.

sqlplus / as sysdba
SQL> set line 200
SQL> select host_name,length(host_name),instance_name from v$instance where rownum=1;
SQL>exit;

If length hostname is more than 32 chars you hitting this bug.

In order to bypass this bug apply the following steps:

1) Drop the repository with emca
emca -deconfig dbcontrol db -repos drop

2) cd to $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/

3) Make a copy of self_monitor_post_creation.sql
cp self_monitor_post_creation.sql self_monitor_post_creation.sql.bak

4) Edit the script and change the l_host_name from varchar2(32) to varchar2(128).
Note: The l_host_name variable is declared 2 times in the script.

5) Create repository with emca
emca -config dbcontrol db -repos create

And you are done.

Oracle 10gR2 installation on RHEL5

Oracle 10gR2 installation on RHEL5.X

1) System Memory
# grep MemTotal /proc/meminfo -- At least 512Mb

2) Swap Space
# grep SwapTotal /proc/meminfo
Up to 512 MB 2 times the size of RAM
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 8192 MB Equal to the size of RAM
More than 8192 MB 0.75 times the size of RAM

3) Disk space Requirement
# df -h
400 MB of disk space in the /tmp directory
Between 1.5 GB and 3.5 GB of disk space for the Oracle software

4) Kernel Version
# uname -r
The system must be running 2.6.18-8 kernel version or a later.

5) Required Packages

i) gcc-c++-4.1.1-52.el5.x86_64.rpm and all its dependent packages.
The dependent packages are:
libstdc++-devel-4.1.1-52.el5.x86_64.rpm
glibc-headers-2.5-12.x86_64.rpm
glibc-devel-2.5-12.x86_64.rpm
libgomp-4.1.1-52.el5.x86_64.rpm
gcc-4.1.1-52.el5.x86_64.rpm
ii) glibc-devel-2.5-12.i386.rpm
iii) compat-libstdc++-33-3.2.3-61.x86_64.rpm
iv) compat-libstdc++-33-3.2.3-61.i386.rpm
v) compat-libstdc++-296-2.96-138.i386.rpm
vi) libXp-1.0.0-8.i386.rpm
vii) sysstat-7.0.0-3.el5.x86_64.rpm
NOTE: i386 packages might require the --force option during installation if the 64-bit version of the same package is already installed. For example, "rpm -ivh --force glibc-devel-2.5-12.i386.rpm" may be required.

6) Set the kernel parameters

Add the following the lines in the file /etc/sysctl.conf
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 262144 262144 262144

To place these changes into effect, execute the command
# sysctl -p

7) Create Oracle user and groups
# groupadd oinstall
# groupadd dba
# useradd -g oinstall -G dba oracle
# passwd oracle

8) Create the required directories for Oracle database and change the ownership

# mkdir -p /u01/app/oracle -- ORACLE_BASE
# chown -R oracle:oinstall /u01/app/oracle

9) Set the session limits for Oracle user

Add the following lines to the /etc/security/limits.conf file
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add the following line in the /etc/pam.d/login file
session required pam_limits.so

Add the following lines to the /etc/profile.
-- This assumes that oracle user is using bash shell which is given by default.
-- Change this accordingly if the oracle user has a different shell
if [ $USER = "oracle" ]; then
ulimit -u 16384
ulimit -n 65536
fi

10) Configure the Oracle user's environment

For example, if oracle user is using bash shell, and has the home directory as /home/oracle,
The following lines to be added to /home/oracle/.bash_profile
export ORACLE_BASE=/u01/app/oracle

11) Invoking OUI to install database software
-- Install Software Only
Login as Oracle User
$ ./runInstaller -ignoreSysPrereqs

12) Invoking OUI to install companion products on the same ORACLE_HOME
$ ./runInstaller -ignoreSysPrereqs

13) Invoking OUI to install Latest Patch Set on the ORACLE_HOME
$ ./runInstaller -ignoreSysPrereqs

14) Set ORACLE_HOME and ORACLE_SID on _bash_profile

15) Run dbca to proceed with database creation


Tuesday, November 11, 2008

Creating numbers range

Ordered range of numbers

select rownum r from dual connect by level <=max_number order by 1

Randomly ordered range of numbers

select rownum r from dual connect by level <=max_number order by dbms_random.value;