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 ............

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 ............

HOT BACKUP

set your archive log on

SQL> shutdown abort ;

SQL> startup mount;

SQL>alter database archivelog;

you got an  error cose  we use shutdown abort so never use shutdown abort if u wanna set archive log
SQL>shutdown immediate ;

SQL>startup mount ;

SQL >alter database archivelog;

SQL>alter database open;

SQL>alter system switch logfile;

SQL>exit

SQL>rman target/

RMAN> backup database plus archivelog;


done

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

Restore database 

SQL >  SHUTDOWN

SQL > EXIT

C:/>RMAN TARGET /

RMAN>   ALTER DATABASE  NOMOUNT ;

RMAN>  RESTORE CONTROLFILE FROM  'D:\BACKUP\C-33354'

** 'D:\BACKUP\C-33354' this is the path where u store ur backup .and C-33354 this the controll file use ur contoll file name .

controll file name always start with 'c-'

RMAN>ALTER DATABASE MOUNT ;

RMAN>RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN>ALTER DATABASE OPEN RESETLOGS;

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

cold backup database

Cold Backup using RMAN

Database should be in archive mode
check the database is up or not

c:\> RMAN TARGET /

RMAN:\> SHOW ALL ;

to check configuration of RMAN  . Configure three things in this configuration

RMAN:\>  CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN:\>  CONFIGURE DEVICE TYPE DISK FORMAT   'D:\BACKUP\%F';

** this is the path where u wanna take backup

RMAN:\>  CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\BACKUP\%U';

RMAN:\>  SHUTDOWN IMMEDIATE ;

RMAN:\>  STARTUP MOUNT;

RMAN:\>  BACKUP DATABASE;

RMAN:\>  ALTER DATABASE OPEN;

--------------------------------------------------------------------------------------------------------
Restore database 



SQL >  SHUTDOWN

SQL > EXIT

C:/>RMAN TARGET /

RMAN>   ALTER DATABASE  NOMOUNT ;

RMAN>  RESTORE CONTROLFILE FROM  'D:\BACKUP\C-33354'

** 'D:\BACKUP\C-33354' this is the path where u store ur backup .and C-33354 this the controll file use ur contoll file name .

controll file name always start with 'c-'

RMAN>ALTER DATABASE MOUNT ;

RMAN>RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN>ALTER DATABASE OPEN RESETLOGS;





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

CLONING ORACLE DATA BASE

STEPS FOR CLONING 
THROUGH COLD BACKUP
 


My old database name is "HRDEMO91"
My clone database name is "FSCLON"
  While using this steps fellow your database name instead or mine
1. Get the file path information using below query
                 select name from v$datafile;
                 select member from v$logfile;
                select name from v$controlfile;
2. Parameter file backup
               If HRDEMO91 database running on spfile then create pfile
                "Create pfile=E:\oracle\product\10.2.0\db_1\database\SPFILEHRDEMO91.ora  from spfile;"
              Now copy this pfile in another location .
3. Taken the control file backup
               "Alter database backup controlfile to trace ;"
4. Shutdown immediate
5. Create a folder with new db name (eg. db name is 'fsdemo')path to create the folder is
              "E:\oracle\product\10.2.0\oradata\"
                1} copy all tablespace and redo logs in this folder (except control file )
6. Now create folder with db name in another location .
                1} The location is "E:\oracle\product\10.2.0\admin\"
                2} In folder fsdemo create adump, bdump,cdump,udump,dpdump ,pfile .
           So you got this folder in "E:\oracle\product\10.2.0\admin\fsdemo\"
7. Now edit that pfile which we copy in another location edit db name file and save it with
           new name like "initfsdemo.ora"
                    1} copy this file and past it in "E:\oracle\product\10.2.0\db_1\database\"
                       2}past another copy in "E:\oracle\product\10.2.0\admin\fsdemo\pfile\"
8. Run this command from your c:\ prompt
             " E:\oracle\product\10.2.0\db_1\bin\oradim -new -sid FSDEMO -SRVC OracleServiceFSDEMO
              -STARTMODE auto -SRVCSTART system -PFILE
                 e:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITFSDEMO.ORA"
             This will create a new instance .

9. Now from "c:\ set oracle_sid=fsdemo"
10. Startup the Database in NOMOUNT stage .
11. Create the control file for cloning database .
             In udump of your old db i.e.HEREMO91 u got acurrent trace control file open it delete
             everything Excep control file ,logfile & datafile.
             Make sure you use SET instead REUSE and use your cloned database
             name on place of old DB and use "RESETLOGS" instead of "NORESETLOGS". Change all path of your dbf and log files according to your cloned
             DB and save it in sql file like c.sql and run it from sql promt
             "SQL> @E:\clone\c.sql"
     User this script for creating control file.
12. Now open database with
            "alter database open resetlogs;"
13.Create a TNS name i.e. Listner for the cloned database name
open     "E:\oracle\product\10.2.0\db_1\network\ADMIN\tnsname.ora"
FSDEMO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.129.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = FSDEMO)
)
)
Copy this script in in your tnsname.ora and save it .

congratulation  your cloning is done 


hoping this information will help you . if any query then please reply me .