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

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

CHECK POINTS

SOME POINTS ON CHECK POINTS

Incremental Checkpoint: Incremental checkpointing improves the performance of crash and instance recovery (but not media recovery). An incremental checkpoint records the position in the redo thread (log) from which crash/instance recovery needs to begin. This log position is determined by the oldest dirty buffer in the buffer cache. The incremental checkpoint information is maintained periodically with minimal or no overhead during normal processing.

Partial Checkpoint: When backup of a tablespace in an open database begins, a partial checkpoint is performed by every instance for the datafiles in that tablespace

Full Checkpoint: In a full checkpoint, the database server flushes all modified pages in the shared-memory buffer pool to disk. When a full checkpoint completes, all physical operations are complete, the MLRU queue is empty, and the database server is said to be physically consistent.

Fast_Start_MTTR_Target cause database checkpoint to occur whenever the expected time to recover the instance exceeds 5 seconds. So just check out your database for it.

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

Background process

10g new background processes

With 10g many new background processes were introduced.
This note highlights those.

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.

RVWR(Recovery Writer.)
Process responsible for writing flashback logs which stores pre-image of data-blocks.

These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL

The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.


M000

MMON background slave (m000) processes.

RBAL
RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.----------------------------------------------------------------------------------------------------------
11g:

The following process are added in 11g as new background processes.

1 dbrm DB resource manager
2 dia0 Diagnosability process
3 fbda Flashback data archiver process
4 vktm Virtual Timekeeper
5 w000 Space Management Co-ordination process
6 smc0 Space Manager process

NOTE : The above six are mandatory processes.

But 11g has 56 new processes added which can be queried using

select name,description from V$bgprocess;

10g background processes:

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.


RVWR

Process responsible for writing flashback logs which stores pre-image of data-blocks.
These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL

The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.

M000

MMON background slave (m000) processes.

RBAL

RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx

These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB

The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.


 use link to see how architecture work
http://www.youtube.com/watch?v=meWIB3yt96U

Errors

GETTING THIS ERROR
ORA-01157:cannot identify/lock data file 1 - see DBWR trace file
ORA-01110:data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HRMSD91\SYSTEM01.DBF'

__________________________________________________________________________________


ERROR due to flash recovery area  and solution for it :



SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1292036 bytes
Variable Size             167774460 bytes
Database Buffers          436207616 bytes
Redo Buffers                2899968 bytes
Database mounted.
ORA-16038: log 3 sequence# 430 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HRM\REDO03.LOG'


SQL> alter system switch logfile
  2  ;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     430
Next log sequence to archive   430
Current log sequence           432

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\product\10.2.0\flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 3G
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\Sundaresh>set oracle_sid=hrm

C:\Documents and Settings\Sundaresh>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 28 12:00:44 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
---------
HRM


SQL> alter system set db_recovery_file_dest_size=2G scope=both;

System altered.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-16014: log 3 sequence# 430 not archived, no available destinations
ORA-00312: online log 3 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HRM\REDO03.LOG'


SQL> alter system set db_recovery_file_dest_size=5g
  2  ;

System altered.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> alter database open ;

Database altered.

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 .

steps for Installation oracle 10 g

Installation of oracle 10g on windows

Oracle 10g PC Minimum Hardware Requirements:
 

Physical memory (RAM)	512 MB recommended
Virtual memory	Double the amount of RAM
Temp disk space	Hard disk space 1.5 GB
Video adapter	256 colors
CPU Processor	200 MHz minimum

For installation you need CD orcale10g or dumps of oracle10g and if u dont hav oracle you can download it from below link :

Download oracle

 if u have already oracle in your system and you need to uninstall it then check out this steps :

 How to uninstall Oracle database software :

1.       Uninstall all Oracle components using the Oracle Universal Installer (OUI).

2.       Run regedit.exe and delete the HKEY_LOCAL_MACHINE/ SOFTWARE/ORACLE key. This contains registry entire for all Oracle products.

3.       Delete any references to Oracle services left behind in the following part of the registry: HKEY LOCAL MACHINE/ SYSTEM/ CurrentControlsSet/ Services/Ora*. It should be pretty obvious which ones relate to Oracle

4.       Reboot your machine.

5.       Delete the C: \Oracle directory, or whatever directory is your Oracle_Base.

6.       Delete the C:\Program Files \Oracle directory.

7.       Empty the contents of your c:\temp directory.

8.       Empty your recycle bin.

Installing Oracle 10g database software:

 

1.      Insert Oracle CD , the autorun window opens automatically. If you are installing from network or hard disk, click setup.exe in the installation folder. 

2.   The Oracle Universal Installer (OUI) will run and display the Select Installation Method Window 

 3. Choose Basic Installation:
Select this option to quickly install Oracle Database 10g. This method requires minimal user input. It installs the software and optionally creates a general-purpose database based on the information you provide.
For basic installation, you specify the following:Oracle Home Location — Enter the directory in which to install the Oracle Database 10g software. You must specify a new Oracle home directory for each new installation of Oracle Database 10g. Use the default value, which is :

c:\oracle\product\10.2.0\db_1

Installation Type — Select Enterprise Edition :
If you have limited space, select standard edition. Personal edition installs the same software as the Enterprise Edition, but supports only a single-user development and deployment environment.Create Starter Database — Check this box to create a database during installation. Oracle recommends that you create a starter database for first Create Starter Database — time installations. Choose a Global Database Name, like cs157b, or just use the default value.
Type a password. Don’t lose this password, since you will need it to connect to the database server.
Click next

4. The Product-Specific Prerequisite Checks window appears: Click next  

 5. A summary screen appears showing information such as your global settings, space requirements and the new products to be installed. Click Install to start the installation.

 
6. The Install window appears showing installation progress
  

  
7. At the end of the installation phase, the Configuration Assistants window appears. This window lists the configuration assistants that are started automatically.  If you are creating a database, then the Database Configuration Assistant starts automatically in a separate window.               

                                            

At the end of database creation, you are prompted to unlock user accounts to make the accounts accessible. The SYS and SYSTEM accounts are already unlocked. Click OK to bypass password management.   

Note: Oracle 10g still keeps scott / tiger username and password (UID=scott, PWD=tiger) from the old version of oracle. In the old version of oracle, scott/tiger user ID is available by default, but not in oracle 10g. If you want to use scott /tiger account, you must unlock it by clicking “Password Management” at the last window.
Password Management window will appear like the one shown below. Find the user name “Scott” and uncheck the “Lock Account?” column for the user name.      

8. Your installation and database creation is now complete. The End of Installation window displays several important URLs, one of which is for Enterprise Manager.

 

  9. You can navigate to this URL in your browser and log in as the SYS user with the associated password, and connect as SYSDBA. You use Enterprise Manager to perform common database administration tasks .

i hope this will help you to install oracle in your machine if any quire is in your mind regarding this installation please write to me