EXPORT AND IMPORT USING DATAPUMP

C:\Documents and Settings\Chetan>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 30 11:55:29 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME

---------

ORA9

SQL> create directory data_p_test as 'c;\data_p_test';

Directory created.

Create manually a directory in the same location with same name ..

SQL> grant read ,write on directory data_p_test to scott;

Grant succeeded.

SQL> select * from dba_directories;

to check you directory is created or not.........................

SQL> grant exp_full_database to scott;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options.

C:\Documents and Settings\Chetan>expdp scott/tiger@ora9 full=y directory=data_p_test dumpfile=full.dmp logfile=full.log

Now connect with your new database where u wanna import .

my database name is "newtest"

SQL> create directory data_p_test as 'c:\data_p_test';

Directory created.

SQL> grant read ,write on directory data_p_test to scott;

Grant succeeded.

SQL> grant imp_full_database to scott;

Grant succeeded.

SQL> alter tablespace temp add tempfile 'tempfilelocation' size 200M autoextend on next 30M;

Come on your command prompt

C:\Documents and Settings\Chetan>impdp scott/tiger@newtest  full=y directory=data_p_test dumpfile=full.dmp logfile=full2.log

for export and import table  use all above command and just use the blow command for imp and exp

 export
SQL>expdp scott/tiger@new test tables=CHETAN directory=table_test dumpfile=table.dmp logfile=tableimp.log;

import

SQL>impdp scott/tiger@ora9 tables=CHETAN directory=table_test dumpfile=table.dmp logfile=tableimp2.log;

any mistake is there or u wanna give any suggestion so please write to me ............

how to create a controlfile

CREATE CONTROLFILE SET DATABASE "fsdemo" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'E:\oracle\product\10.2.0\oradata\fsdemo\redo01.log' SIZE 200M,
GROUP 2 'E:\oracle\product\10.2.0\oradata\fsdemo\redo02.log' SIZE 200M,
GROUP 3 'E:\oracle\product\10.2.0\oradata\fsdemo\redo03.log' SIZE 200M
DATAFILE
'E:\oracle\product\10.2.0\oradata\fsdemo\system01.dbf',
'E:\oracle\product\10.2.0\oradata\fsdemo\undotbs01.dbf',
'E:\oracle\product\10.2.0\oradata\fsdemo\users01.dbf'
CHARACTER SET WE8ISO8859P1;

"fsdemo" is the  database name for which u wanna create controlfile

"E:\oracle\product\10.2.0\oradata\fsdemo\" this is the path where your logfile and datafile are store .
just past this file and edit path and name . and save it as "control.sql' in notepad
run this file from sql promt
SQL>@control.sql

remember your database should be in no mount stage  for creating controlfile

any mistake is there or u wanna give any suggestion so please write to me ............

RENAME DATABASE AND INSTACE

RENAME DATABASE AND INSTACE

Let suppose  your db name is TEST and wanna change your db to NEWTEST

C:\> set ORACLE_SID=TEST

C:\> sqlplus  sys/sys as sysdba

SQL>select name from v$database;

Name

----------

TEST

SQL>alter database backup controlfile to trace as 'c:\c.txt' reuse;

SQL> shutdown immediate;

copy and past your old controlfile to another loction and edit your INITTEST.ora in this change your  "db_name=NEWTEST"

SQL>startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\inittest.ora'

(the path where you save your pfile )

now create the new control file ..........

SQL>alter database open resetlogs;

SQL>select name from v$database;

Name

----------

NEWTEST

****this will change only your db name not instance name 

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

TO CHANGE  INSTANCE  INSTANCE NAME 

SQL> show parameter db_name;

NAME                                 TYPE        VALUE

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

db_name                              string           newtest

SQL>show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
instance_name                        string      test 

if we are using spfile  we need to do ........

SQL> alter system reset instance_name scope= spfile sid ='*";

SQL> startup force ; 

BUT If we are using PFILE then....

In this case:
 remove instance_name parameter from PFILE

 shutdown/restart instance 

c:\> set ORACLE_SID=NEWTEST

if instance is  running in LINUX then every thing is done .

But in windows we need to crate through ORADIM

for ORADIM

create new instance -

c:\>E:\oracle\product\10.2.0\db_1\bin\oradim -new -sid NEWTEST -SRVC OracleServiceNEWTEST 

    -STARTMODE auto -SRVCSTART system -PFILE

                 e:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITTEST.ORA

         

 "    e:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITTEST.ORA"  this is the path where your pfile is saved.

    This will create a new instance .

Now delete your old instance 

c:\>E:\oracle\product\10.2.0\db_1\bin\oradim -DELETE -SID TEST

Until you did not delete your old instance  you always got error so first delete your old instance and then check your db name and instance name ...

any mistake is there or u wanna give any suggestion so please write to me ............