Creating an Oracle database and importing a dump file

It's not too often that I need to create a new database. Since I rarely do this, I wanted to jot down some notes for next time. Thanks to the developer/dba for the help. Of course, these steps will vary a little depending on what you want to do.

One way to create an Oracle db using Windows:

(I created an 11g db)

1. Open DBCA (probably in C:\Oracle\product\11.1.0\db_1\BIN).
2. Hit Next.
3. Select Create a Database and hit Next.
4. Select General Purpose or Transaction Processing and hit Next.
5. Enter a Global Database Name (use this as your SID) and hit Next.
6. Unselect Configure Enterprise Manager and hit Next.
7. Select Use the Same Administrative Password for All Accounts and enter ‘password’ in each box. Click Next.
8. Click Finish.
9. Click OK on the box that pops up.
10. After the database creates successfully, exit DBCA.

After creating the database, I needed to import a dump file. So first I had to set up the import directory, and some tablespaces and roles needed for the import.

In this example, 'C:\tmp' is my import directory, where the dump file is located:

CREATE OR REPLACE DIRECTORY import_dir AS 'C:\tmp';

COMMIT;

I needed to setup a profile for my db:

CREATE PROFILE APP_PROFILE

LIMIT FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LIFE_TIME 180

PASSWORD_LOCK_TIME 1

PASSWORD_GRACE_TIME 7;

COMMIT;

Examples of setting up tablespaces and roles:

CREATE TABLESPACE TABLE_INDEX01 DATAFILE 'C:\Oracle\oradata\[insert SID here]\table_index01.dbf' SIZE 1000m AUTOEXTEND ON MAXSIZE 2000m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5m;

COMMIT;

CREATE ROLE TEST_DBA IDENTIFIED BY TEST_DBA;

COMMIT;

Then I ran the following command to import the dump file:

%ORACLE_HOME%\bin\impdp system/password full=y directory=import_dir dumpfile=name_of_dump_file.dmp nologfile=Y

You can use the DBCA tool to delete databases also.

When I tried to connect to my new database in SQL Developer, I ran into some errors. You need to make sure your Listener is running before you can connect. At a command prompt:

> LSNRCTL

> status

If you receive an error, start the listener:

> start