Export /Import dump - Oracle 10g/11g -(Linux)

Scenario: I have a db and, it contains a schema called "hr". So i want to get a export dump of hr schema and import the same dump to some other schema in same db.

First of all you must create a file location for the dump files.

Log in as Oracle user and create a file wherever you want. My file name is "dumps"

[oracle@prod ~]$ mkdir /u01/dumps
[oracle@prod ~]$ ls /u01/
app database dumps
[oracle@prod ~]$


I have few schemas in my db and I gonna take the export for hr schema.
So first of all you must log into the database as the schema user.

[oracle@prod ~]$ sqlplus hr/hr

Then issue below command to create the directory

SQL> create or replace directory export_dp as '/u01/dumps/';

Directory created.

• export_dp is the logical name that you can give for your directory and the '/u01/dumps/' is the physical path.

You can check yous directory using below command

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
EXPORT_DP

now exit from sql promt and come back to oracle promt and issue below statement to take the export.

[oracle@prod ~]$ expdp hr/hr@DB11G directory=export_dp dumpfile=dump_hr.dmp
logfile=log_hr.log schemas=hr

Export: Release 11.1.0.6.0 - Production on Thursday, 19 April, 2012 13:20:26

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@DB11G directory=export_dp
dum pfile=dump_hr.dmp logfile=log_hr.log schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."COUNTRIES" 6.375 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.015 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.984 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.484 KB 4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/u01/dumps/dump_hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:23:42

[oracle@prod ~]$

As you can see tables have been exported.. Cross verify whether dump file is there in the location.
[oracle@prod ~]$ ls /u01/dumps/
dump_hr.dmp log_hr.log
[oracle@prod ~]$

Now login to database as SYS user and create the new schema.. First of all you must create the tablespace for the user and then create the user. My tablespace name is "fin" and the user is "finance".

[oracle@prod ~]$ sqlplus / as sysdba
SQL> create tablespace fin datafile 'fin.dat' size 20M autoextend on;

Tablespace created.

Then create user finance and assign fin tablespace for the user

SQL> create user finance identified by finance default tablespace fin
2 temporary tablespace temp
3 quota unlimited on fin;

User created.

Now grant whatever the access you need for the user...I give dba writes for the user.

SQL> grant dba to finance;

Grant succeeded.

SQL>

Now Log in as user and create import directory

SQL> connect finance/finance
Connected.

SQL> create or replace directory import_dp as '/u01/dumps/';

Directory created.

now exit from sql and come to oracle prompt and issue below.

[oracle@prod ~]$ impdp finance/finance@DB11G directory=import_dp dumpfile=dump_hr.dmp logfile=log_fin.log REMAP_SCHEMA=hr:finance

Import: Release 11.1.0.6.0 - Production on Thursday, 19 April, 2012 13:43:23

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "FINANCE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "FINANCE"."SYS_IMPORT_FULL_01": finance/********@DB11G directory=import_dp dumpfile=dump_hr.dmp logfile=log_fin.log REMAP_SCHEMA=hr:finance
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"FINANCE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "FINANCE"."COUNTRIES" 6.375 KB 25 rows
. . imported "FINANCE"."DEPARTMENTS" 7.015 KB 27 rows
. . imported "FINANCE"."EMPLOYEES" 16.80 KB 107 rows
. . imported "FINANCE"."JOBS" 6.984 KB 19 rows
. . imported "FINANCE"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "FINANCE"."LOCATIONS" 8.273 KB 23 rows
. . imported "FINANCE"."REGIONS" 5.484 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "FINANCE"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:44:04

[oracle@prod ~]$

Now connect as finance user and check one table to confirm the import

[oracle@prod ~]$ sqlplus finance/finance

SQL> desc employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

This is all about export/import using expdp and impdp command.....

Note: if you are importing to same user/schema in a different database, then you dont need to use remap_schema clause in impdp command. It's all up to you how you want to do the import.

How to Create New database using RMAN backup

scenario:

i have two database servers, 1 is my production db server and other
one is backup testing db server. in backup testing server i have
installed only oracle binaries.(server) haven't configured
any databases using dbca. ;)



connect to rman of production db and take the backup of controlfile/datafile/archivelogs.

oracle$ rman target /


take control file backup

rman> configure controlfile autobackup on;

now check it

rman > show all
rman > backup as copy current controlfile


now backup database full

rman>backup database plus archivelog;

or

rman> backup as compressed backupset database;
rman> backup as compressed backupset archivelog all;

--------------------------------------------------------------------
now get the paths of backup files taken

oracle$ sqlplus / as sysdba

sql>select name from v$datafile;

sql>select member from v$logfile;

sql>select name from v$controlfile;

sql>show parameter dump_dest

copy them to a textpad bcz we want to create same directories in
other server.

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

now connect to the backup testing server

create directories for files like backupset and dumpset


oracle$ cd $oracle_home
oracle$ mkdir oradata
oracle$ cd oradata
oracle$ mkdir .....
......
......

dump destination

oracle$ cd /u01/oracle/product/10.2.0/db_1/admin
oracle$ mkdir DR
oracle$ cd DR
oracle$ mkdir adump bdump cdump udump
oracle$ cd /tmp

to store rman backup
oracle$ mkdir backup

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

now connect to the prod db and copy all the backup files to
relevant location.

archivelogs,controlfile,datafile to /tmp/backup using scp command


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

now connect to backup server

first step is to restore spfile

oracle$ export oracle_sid=dr
oracle$ rman target /

start the database in nomount mode using dumy parameter file

rman> startup nomount

rman> restore spfile from '/tmp/backup/filename.bkp';

now restore control file


oracle$ rman target /

rman> startup nomount

rman> restore controlfile from '/tmp/backup/spfilename ekamai.nkp';

--------------------------------------------------------------
open database

rman> startup force mount


if this fails, then login sqlplus as sysdba

then create PFILE from SPFILE manually

sql> CREATE PFILE = '/u01/myinit.ora' FROM SPFILE = '/u01/app/oracle/product/11.2.0/db_1/dbs/hnbstg_spfile.ora';

then edit pfile as below------

+++++++++++++++++++++++++++++++++++++++++++++++

comment belows using # sign

#hnbstg.__db_cache_size=230686720
#hnbstg.__java_pool_size=4194304
#hnbstg.__large_pool_size=4194304
#hnbstg.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
#hnbstg.__pga_aggregate_target=327155712
#hnbstg.__sga_target=616562688
#hnbstg.__shared_io_pool_size=0
#hnbstg.__shared_pool_size=360710144
#hnbstg.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/hnbstg/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlhnbstg.dbf'
*.db_block_size=8192
*.db_domain=''
*.db_name='hnbstg'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=12884901888
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hnbstgXDB)'
*.job_queue_processes=1000
*.memory_target=943718400
*.open_cursors=300

++++create audit_file_dest as mentioned (ex:-/u01/app/oracle/admin/hnbstg/adump)
++++change control_files as mentioned (ex:- /u01/app/oracle/product/11.2.0/db_1/dbs/cntrlhnbstg.dbf)
after the recovery of controlfile it will be available in above directory
+++++create db_recovery_file_dest as mentioned (ex:-/u01/app/oracle/flash_recovery_area)

note:- all the paths and files should be replaced using oracle user.

+++++++++++++++++++++++++++++++++++++++++++++++

now startup the database using newly created PFILE

sql>startup force mount PFILE=/u01/myinit.ora;

now copy the backup datafiles and archivelog files to /u01/app/oracle/flash_recovery_area

now login to rman and restore the database

rman target /

restore database
rman> restore database;

if this fails catalog datafiles

rman> catalog start with '/u01/app/oracle/flash_recovery_area/backupset/2011-02-02';

rman> restore database;

recover database using logfiles
rman> recover database

it fails, so issue below
rman> alter database open resetlogs;
rman> exit

Issue above same command if you can't open the database


now create a spfile from pfile

oracle$sqlplus / as sysdba
sql>create spfile from pfile='/u01/myinit.ora';

sql> now down the db serve and start it again...

sql> check table whatever u want ;)

How to take a RMAN backup

first of all you need to switch the log files which are not currently switched. so first login to sqlplus as sys user and issue below commands:

>sqplplus / as sysdba
sql> alter system switch logfile;

Now login to rman using below command and issue below commands:

> rman target sys/sysadmin or >rman target=/

rman>run {
crosscheck archivelog all;
crosscheck backupset;
backup as copy current controlfile format 'control_%s.bak';
backup spfile;
backup as compressed backupset database;
backup as compressed backupset archivelog all;


DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'sysdate -1';
DELETE NOPROMPT OBSOLETE REDUNDANCY = 1;
}exit;

How to automate the process for windows.

create a windows batch file named mysysbackup.bat
insert below commands and save file

sqlplus /nolog @c:\sqlpluscom.sql
rman target=/ cmdfile="c:\backupdb.sql"

now create sqlpluscom.sql and backupdb.sql files in c:\ drive

sqlpluscom.sql

connect / as sysdba
alter system switch logfile;
exit;

backupdb.sql

run {
crosscheck archivelog all;
crosscheck backupset;
backup as copy current controlfile format 'control_%s.bak';
backup spfile;
backup as compressed backupset database;
backup as compressed backupset archivelog all;


DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'sysdate -1';
DELETE NOPROMPT OBSOLETE REDUNDANCY = 1;
}
exit;

now shedule the batch as a windows schedule task...

Oracle Apex installation on Windows

Before apex installation you should have oracle installed in your machine.(10g above)

download the Apex and unzip it in drive C:\. Then you will get a folder called apex. (C:\apex)

now set path to the apex folder

Then type below

set ORACLE_HOME=c:\oraclexe\app\oracle\product\10.2.0\server
set ORACLE_SID=XE
set PATH=%ORACLE_HOME%\bin;%PATH%


now loging to sqlplus as sysdba
>sqlplus / as sysdba

now type below sql commands

SQL>@apexins SYSAUX SYSAUX TEMP /i/


after installation log into sqlplus as sysdba
issue below

SQL>@apxldimg c:\ in my case it was C:\ not C:\apex XXXX


exit and relogin as sysdba and issue below to change password

SQL>@apxxepwd.sql


username ADMIN
password XXXXXX


######This is the end of installation #######

run below to access application from client machine

login as sysdba

sql> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Oracle Database 11g Release 2 (11.2) Installation On Enterprise Linux 5.0

OS configuration

Login as root and make a directory in root as u01
[charith@localhost ~]$ su - root
Password:
[root@localhost ~]# mkdir /u01

Copy below file into that folder and unzip
· linux_11gR1_database.zip

Check the copied file

[root@localhost ~]# cd /u01
[root@localhost u01]# ls
linux_11gR1_database_1013.zip


Unzip it and check the u01 folder
[root@localhost u01]# unzip linux_11gR1_database_1013.zip
[root@localhost u01]# ls
database linux_11gR1_database_1013.zip
You should now have a single directory called "database" containing installation files.

Configure the host file
Get the ip address first using below command
[root@localhost /]# ifconfig

eth0 Link encap:Ethernet HWaddr 00:0C:29:85:A3:07
inet addr:192.168.174.128 Bcast:192.168.174.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe85:a307/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:33 errors:0 dropped:0 overruns:0 frame:0
TX packets:44 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:4150 (4.0 KiB) TX bytes:8878 (8.6 KiB)
Interrupt:19 Base address:0x2000

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:1506 errors:0 dropped:0 overruns:0 frame:0
TX packets:1506 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:75460 (73.6 KiB) TX bytes:75460 (73.6 KiB)



The /etc/hosts file must contain a fully qualified name for the server
[root@localhost /]# vim /etc/hosts
Append the below ip address as shown in the figure
192.168.100.16 localhost.localdomain localhost

ex: 192.168.10.21 racn1.hnbassurance.com racn1
Then save and exit (:wq)

How to check kernel parameters

Set kernal parameters
The following lines should be appended to the "/etc/sysctl.conf" file.
[root@localhost ~]# vim /etc/sysctl.conf

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Then type below command to effect the change
[root@localhost ~]# /sbin/sysctl –p


Add the following lines to the /etc/security/limits.conf file
[root@localhost /]# vim /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add the following line to the /etc/pam.d/login file, if it does not already exist
[root@localhost /]# vim /etc/pam.d/login
session required pam_limits.so

Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows
SELINUX=disabled

How to install required packages using yum and rpm

Setup

Install the following packages
From Linux RHEL 5 DVD
Should already be installed if you selected the recommended package groups.

# From Enterprise Linux 5 DVD
cd /media/cdrom/Server
rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh compat-libstdc++-33*.i386.rpm
rpm -Uvh elfutils-libelf*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh ksh*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libgomp-4.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh make-3.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
cd /
eject



If Not available on LINUX DVD, but available from standard yum repository.
Ex:-
yum install libaio libaio-devel
yum install unixODBC-devel
yum install sysstat
yum install compat-libstdc++




Create the new groups and users
groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin

useradd -g oinstall -G dba,oper,asmadmin oracle
passwd oracle


Login as root user and issue below commands
[root@localhost /]# groupadd dba
[root@localhost /]# groupadd oinstall
[root@localhost /]# useradd -g oinstall -G dba oracle
[root@localhost /]# passwd oracle

Create the directories in which the Oracle software will be installed
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Login as root and issue the following command to turn on the graphics
[root@localhost /]# xhost +

Then Create oracle home
[root@localhost /]# mkdir -p /u01/app/oracle/product/11.2.0/db_1
[root@localhost /]# chown -R oracle:oinstall /u01
[root@localhost /]# chmod -R 775 /u01
Edit the /etc/redhat-release file replacing the current release information
type below
redhat release 4

Login as the oracle user and add the following lines at the end of the .bash_profile file

[oracle@localhost ~]$ vim .bash_profile

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=oel5-11gr2.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi


Exit from oracle and re login to effect the change

[oracle@localhost ~]$ exit
logout
[root@localhost /]# su - oracle

Then follow the below steps to run the installer
[root@localhost /]# su - oracle
[oracle@localhost ~]$ cd /u01/database
[oracle@localhost database]$ ls
doc install README runInstaller stage welcome.html
[oracle@localhost database]$ ./runInstaller

for more infomation
http://www.oracle-base.com/articles/11g/OracleDB11gR2InstallationOnEnterpriseLinux5.php

How to increase swap file

Determine the size of the new swap file and multiple by 1024 to determine the block size. For example, the block size of a 64 MB swap file is 65536.

At a shell prompt as root, type the following command with count being equal to the desired block size
dd if=/dev/zero of=/swapfile bs=1024 count=65536
Setup the swap file with the command
mkswap /swapfile
To enable the swap file immediately but not automatically at boot time
swapon /swapfile
To enable it at boot time, edit /etc/fstab to include
/swapfile swap swap defaults 0 0
The next time the system boots, it will enable the new swap file.
After adding the new swap file and enabling it, make sure it is enabled by viewing the output of the command
cat /proc/swaps or free.