You are required to export an existing database only if you intend to copy its contents to a new database.

If you are working with data from an earlier Oracle release, then you can use Export for this task. If you are using Oracle Database 10g Release 1 (10.1) or later data, then Oracle recommends that you use Data Pump Export because it supports new Oracle Database 10g Release 1 (10.1) or later features, such as floating points.

Although you can start Data Pump Export or Export in either the parameter mode or an interactive mode, Oracle recommends parameter mode. Interactive mode provides less functionality than the parameter mode and exists for backward compatibility only.

The syntax for Data Pump Export parameter mode is:

C:\> expdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOGFILE=myexp.log
Password: password

The syntax for Data Pump Export interactive mode is:

C:\> expdp SYSTEM 
Password: password

Enter only the command expdp SYSTEM to begin an interactive session and let Data Pump Export prompt you for information it needs.

The syntax for Export parameter mode is:

C:\> exp SYSTEM FILE=myexp.dmp FULL=y LOG=myexp.log
Password: password

The syntax for the Export interactive mode is:

C:\> exp SYSTEM
Password: password

Enter only the command exp SYSTEM to begin an interactive session and let Export prompt you for information it needs.

Topics:

• Exporting All Data from an Existing Database
  Describes how to export all data from an existing database to a new database.

Deleting database files is required only when you copy an existing database to a new database to replace the old database.

1. Shut down starter database orcl at the command prompt:

   C:\> oradim -SHUTDOWN -SID orcl -SHUTTYPE inst -SHUTMODE immediate
   

2. Delete the following files from the directory C:\app\username\oradata\orcl:

   control01.ctl
   control02.ctl
   control03.ctl
   index01.dbf
   drsys01.dbf
   cwmlite01.dbf
   example01.dbf
   system01.dbf
   temp01.dbf
   tools01.dbf
   undotbs01.dbf
   user01.dbf
   xdb01.dbf
   redo01.log
   redo02.log
   redo03.log

Describes how to modify the initialization parameter file.

Learn how to start an instance without mounting a database.

SQL> STARTUP NOMOUNT

The CREATE DATABASE statement is a SQL statement that creates the database.

CREATE DATABASE prod
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
MAXLOGFILES 5
MAXDATAFILES 100
DATAFILE 'C:\app\username\oradata\prod\system01.dbf' SIZE 325M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS" DATAFILE 'app\username\oradata\prod\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
logfile 'C:\app\username\oradata\prod\redo01.log' size 100M reuse,
        'C:\app\username\oradata\prod\redo02.log' size 100M reuse,
        'C:\app\username\oradata\prod\redo03.log' size 100M reuse
EXTENT MANAGEMENT LOCAL;

Use this procedure to run the CREATE DATABASE script.

1. Verify that the service is started in the Control Panel. In this example, the service name is OracleServicePROD, and its status column must display Started. If not, then select the service name and select Start.

   You can also check the status of the service by entering the following at the command prompt:

   C:\> net START
   

   A list of all the Windows services currently running on the system appears. If OracleServicePROD is missing from the list, then enter:

   C:\> net START OracleServicePROD
   

2. Make PROD the current SID:

   C:\> set ORACLE_SID=PROD
   

3. Add ORACLE_HOME\bin to your PATH environment variable:

   set PATH=ORACLE_BASE\ORACLE_HOME\bin;%PATH%
   

4. Start SQL*Plus from the command prompt, and connect to the database as SYSDBA:

   C:\> sqlplus /NOLOG
   SQL> CONNECT / AS SYSDBA 
   

   The message connected appears.

5. Turn on spooling to save messages:

   SQL> SPOOL script_name.log
   

6. Run the script script_name.sql that you created in Adding the CREATE DATABASE Statement in a Script.

   SQL> C:\app\username\product\12.2.0\dbhome_1\rdbms\admin\script_name.sql;
   

   If the database is successfully created, then the instance is started and the following message appears numerous times: Statement processed

Learn how to use Data Pump Import or Import.

You can use Data Pump Import (for Oracle Database 10g Release 1 (10.1) or later data) or Import (for earlier data) to import the full export created into the new database. Although you can start Data Pump Import or Import using either the parameter mode or the interactive mode, Oracle recommends the parameter mode because it provides more functionality. Interactive mode exists solely for backward compatibility.

The syntax for Data Pump Import parameter mode is:

C:\> impdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
Password: password

The syntax for Data Pump Import interactive mode is:

C:\> impdp SYSTEM 
Password: password

Enter only impdp SYSTEM to begin an interactive session and let Data Pump Import prompt you for information it needs.

If this is the first database on your computer or if you intend to make the new database the default database, then you must make a change in the registry.

1. Start Registry Editor at the command prompt:

   C:\> regedit

   The Registry Editor window appears.

2. Select the subkey \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 for the first Oracle home on your computer. For subsequent installations to different Oracle homes on the same computer, the path is \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID where ID is the unique number identifying the Oracle home.
3. Locate the parameter ORACLE_SID on the right side of the Registry Editor window.
4. Double-click the parameter name and change the data to the new SID, which is prod in this example.

If you do not yet have the parameter ORACLE_SID, because this is the first database on your system, then you must create it.

1. Select New from the Edit menu.
2. Select Expandable String Value from the menu list.
3. A New Value #1 expandable string value name is created on the right pane of the Registry Editor window of data type REG_EXPAND_SZ.
4. Right-click the parameter, select Rename to rename it to ORACLE_SID and press Enter.
5. Double-click the ORACLE_SID entry to change the value data to the new SID.

   An Edit String dialog box appears:

   
   Description of the illustration stringre.gif
6. Enter PROD in the Value data field.
7. Click OK.

   Registry Editor adds parameter ORACLE_SID.

8. Select Exit from the File menu.

   Registry Editor exits.

Use this procedure to prevent data loss.

1. Shut down the database instance and stop the service:

   C:\> oradim -SHUTDOWN -SID prod -SHUTTYPE srvc,inst -SHUTMODE immediate

   Note:

   Although ORADIM returns the prompt immediately, you must wait for the database and the service to stop completely before continuing to Step 2. Wait until the Control Panel indicates service OracleServicePROD has stopped. If you do not do this, then the backup is useless because it was taken while data was being written to data files.

2. Back up database files using the tool of your choice.

   Database files consist of the initialization parameter file, control files, online redo log files, and data files.

   When the backup is complete, you can start the database again, create users and objects, if necessary, make any other changes, and use the database.

   Back up the database after making any significant changes, such as switching into the archiving mode or adding a tablespace or a data file.

   See Also:

   • Oracle Database Concepts

   • Oracle Database Backup and Recovery User's Guide

   • Oracle Database 2 Day DBA