Manual Database Creation in Oracle 10g

Manual Creation of database in windows with oracle 10g

STEP: 1

Now open a command prompt and set oracle SID as set oracle_sid=mandb

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>set oracle_sid=mandb
--------------------------------------------------------------------------------

STEP: 2

Start a windows service with internal password oradim –new –sid
intpwd is the syntax.

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>oradim -new -sid mandb -intpwd mandb
Instance created.
--------------------------------------------------------------------------------

STEP: 3

Create a directory called mandb. In my case I created it in d:\ drive
D:\mandb\data
D:\mandb\redo
D:\mandb\control
D:\mandb\bdump
D:\mandb\cdump
D:\mandb\udump
D:\mandb\pfile
D:\mandb\spfile
D:\mandb\temp

(Note: all my parameter files and .sql file that are going to discuss following are based on my
location, you can change the location according to yours)

STEP: 4

Now create a pfile for the database namely initmandb.ora in D:\mandb\pfile
Give the necessary parameter in the pfile.I gave thelease amount of parameters.

----Contents of initmandb.ora-----

db_name=mandb
instance_name=mandb

db_block_size=8192
db_cache_size=25165824

background_dump_dest=D:\mandb\bdump
core_dump_dest=D:\mandb\cdump
user_dump_dest=D:\mandb\udump

control_files=("D:\mandb\control\control01.ctl", "D:\mandb\control\CONTROL02.ctl",
"D:\mandb\control\CONTROL03.ctl")

shared_pool_size=50331648

undo_management=AUTO

STEP: 5

Now type following in your current command prompt sqlplus/nolog and in sql
prompt type conn sys/mandb as sysdba then you should see that you
are connected to an idle instance

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>sqlplus/nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 1 15:18:36 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys/mandb as sysdba
Connected to an idle instance.
SQL>
--------------------------------------------------------------------------------

STEP: 6

Now start the instance in nomount mode as,

startup nomount pfile=D:\mandb\pfile\initmandb.ora

why are you starting the database in nomount mode ?
The reason is still we are not created control files. “An instance would be started in
the NOMOUNT stage only during database creation or the re-creation of control files.

------------------------------------------------------------------------------------
SQL> startup nomount pfile=D:\mandb\pfile\initmandb.ora
ORACLE instance started.

Total System Global Area 109051904 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
SQL>

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

STEP: 7

Now create the database by the following command...

CREATE DATABASE mandb
LOGFILE
GROUP 1('D:\mandb\redo\redo01.log') SIZE 50M,
GROUP 2('D:\mandb\redo\redo02.log') SIZE 50M,
GROUP 3('D:\mandb\redo\redo03.log') SIZE 50M
DATAFILE 'D:\mandb\data\system01.dbf' SIZE 200M autoextend on
SYSAUX DATAFILE 'D:\mandb\data\sysaux01.dbf' SIZE 200M
UNDO TABLESPACE UNDOTBS
DATAFILE 'D:\mandb\data\UNDOTBS.dbf' SIZE 100M
default temporary tablespace TEMP
tempfile 'D:\mandb\temp\temp_01_db01.dbf' SIZE 50M
extent management local uniform size 128k
;

Run the script in the SQL prompt
--------------------------------------------------------------------------------------
SQL> CREATE DATABASE mandb
2 LOGFILE
3 GROUP 1('D:\mandb\redo\redo01.log') SIZE 50M,
4 GROUP 2('D:\mandb\redo\redo02.log') SIZE 50M,
5 GROUP 3('D:\mandb\redo\redo03.log') SIZE 50M
6 DATAFILE 'D:\mandb\data\system01.dbf' SIZE 200M autoextend on
7 SYSAUX DATAFILE 'D:\mandb\data\sysaux01.dbf' SIZE 200M
8 UNDO TABLESPACE UNDOTBS
9 DATAFILE 'D:\mandb\data\UNDOTBS.dbf' SIZE 100M
10 default temporary tablespace TEMP
11 tempfile 'D:\mandb\temp\temp_01_db01.dbf' SIZE 50M
12 extent management local uniform size 128k
13 ;

Database created.

SQL>

once you run this you can see the control files, redo log file, Alert log file, .dbf files and .trc
(Background Trace files & User Trace files) files are created in D:\mandb\ folder.

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

STEP: 8

Now you can shutdown the database using shutdown command.

---------------------------------
Database dismounted.
ORACLE instance shut down.
SQL>
---------------------------------

Once the database shutdown reboot your PC

STEP: 9

Connect again as sysdba to default database

---------------------------------
sqlplus/nolog,
SQL>conn sys/mandb as sysdba
---------------------------------

check which database you are in by using the following command

show parameter service_name

STEP: 10

Now place the pfile initmandb.ora in this lacation
D:\Oracle\product\10.2.0\db_1\database\

Then try to connect like this

SQL>conn sys/mandb as sysdba

and startup your database.

You dont have to mention your pfile in th startup command since you have placed it in the
default pfile location

D:\Oracle\product\10.2.0\db_1\database\

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

C:\Documents and Settings\administrator.SUVIN>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 1 15:48:15 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys/mandb as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 109051904 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL>

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

STEP: 11

Execute catalog.sql SQL>@d:\ORANT\rdbms\admin\catalog.sql
Execute catproc.sql SQL>@d:\ORANT\rdbms\admin\catproc.sql

STEP: 12



Now you need to edit the following files

D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora

Here are the files that I used…you can change those accordingly
# TNSNAMES.ORA Network Configuration File: D:\ORANT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
SURANGA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = suranga)
)
)
mandb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inal075)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mandb)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
6
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)



# LISTENER.ORA Network Configuration File: D:\ORANT\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\ORANT)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = suranga)
(ORACLE_HOME = D:\ORANT)
(SID_NAME = suranga)
)
(SID_DESC =
(GLOBAL_DBNAME = mandb)
(ORACLE_HOME = D:\mandb)
(SID_NAME = mandb)
)
)
Now start configuring tns Service Name in Client System from where you need to
access the Oracle 10g Enterprise Server
Open Net Manager
Start->Programs->Oracle10gHome->Configuration and Migration Tools->Net Manager

Create a User in mandb Database and test the connection in Net Manager
by changing the login.

How to recover dropped table in Oracle 10g using flashback feature

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Feb 17 11:07:27 2010

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter user-name: suvin@test
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
STEP 1 : WE ARE GOING TO DROP TABLE TABLE2.
SQL> desc table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(3)

SQL> select * from table2;

ID
----------
2
2
3
4
4
5

6 rows selected.

STEP 2 : NOW DROP THE TABLE.

SQL> drop table table2;

Table dropped.

SQL> show user
USER is "SUVIN"

SQL> select * from table2;
select * from table2
*
ERROR at line 1:
ORA-00942: table or view does not exist

Now We are confirmed that table has been dropped.

SQL> show user
USER is "SUVIN"

STEP 2 : CHECK WHEATHER THE TABLE IS AVAILABLE IN RECYCLEBIN.

SQL> select object_name,original_name from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$T0xRBK9YSomiRRmhwn/xPA==$0 TABLE2

STEP 2 : USE THIS FLASHBACK COMMAND TO RECOVER THE DROPPED TABLE.

SQL> flashback table table2 to before drop;

Flashback complete.

SQL> select * from table2;

ID
----------
2
2
3
4
4
5

6 rows selected.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit

IF THE DROPPED TABLE IS NOT IN RECYCLEBIN THEN YOU CANT USE FLASHBACK FEATURE TO RECOVER THE TABLE.

Else, you'll need a Backup or a dump to restore your Table.

For instance, by duplicating your database elsewhere and get back the Table by DataPump (expdp/impdp).

Exporting and Importing Tables in Oracle 10g

SQL> $exp porting@test file=c:/activity1.dmp log=c:/activity1.log tables=activity_master

Export: Release 9.2.0.1.0 - Production on Wed Feb 10 16:20:12 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table ACTIVITY_MASTER 13658 rows exported
Export terminated successfully without warnings.

SQL> conn
Enter user-name: raja@test
Enter password:
Connected.


SQL> drop table activity_master;
drop table activity_master
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop table activity_master cascade constraints;

Table dropped.


SQL> $imp raja@test file=c:/activity1.dmp log=c:/activity1.log tables=activity_master fromuser=porting touser=raja;

Import: Release 9.2.0.1.0 - Production on Wed Feb 10 16:33:14 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by PORTING, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "ACTIVITY_MASTER" 13658 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL>