Feeds:
Posts
Comments

Posts Tagged ‘sqlplus’

Manual Creation of database in windows with oracle 10g

STEP: 1

Now open a command prompt and set oracle SID as

C:Documents and Settingsadministrator.SUVIN>set oracle_sid=samdb

STEP: 2

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

C:Documents and Settingsadministrator.SUVIN>oradim -new -sid samdb -intpwd samdb Instance created.

STEP: 3

Create a directory called mandb. In my case I created it in d: drive
D:samdbdata
D:samdbredo
D:samdbcontrol
D:samdbbdump
D:samdbcdump
D:samdbudump
D:samdbpfile
D:samdbspfile
D:samdbtemp

(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 initsamdb.ora in D:mandbpfile
Give the necessary parameter in the pfile.I gave thelease amount of parameters.

—-Contents of initmandb.ora—–

db_name=samdb
instance_name=samdb

db_block_size=8192
db_cache_size=25165824

background_dump_dest=D:samdbbdump
core_dump_dest=D:samdbcdump
user_dump_dest=D:samdbudump

control_files=(“D:samdbcontrolcontrol01.ctl”, “D:samdbcontrolCONTROL02.ctl”,
“D:samdbcontrolCONTROL03.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/samdb as sysdba then you should see that you
are connected to an idle instance

C:Documents and Settingsadministrator.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/samdb as sysdba
Connected to an idle instance.
SQL>
STEP: 6

Now start the instance in nomount mode as,

startup nomount pfile=D:samdbpfileinitmandb.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:samdbpfileinitsamdb.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:samdbredoredo01.log’) SIZE 50M,
GROUP 2(‘D:samdbredoredo02.log’) SIZE 50M,
GROUP 3(‘D:samdbredoredo03.log’) SIZE 50M
DATAFILE ‘D:samdbdatasystem01.dbf’ SIZE 200M autoextend on
SYSAUX DATAFILE ‘D:samdbdatasysaux01.dbf’ SIZE 200M
UNDO TABLESPACE UNDOTBS
DATAFILE ‘D:samdbdataUNDOTBS.dbf’ SIZE 100M
default temporary tablespace TEMP
tempfile ‘D:samdbtemptemp_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:samdbredoredo01.log’) SIZE 50M,
4 GROUP 2(‘D:samdbredoredo02.log’) SIZE 50M,
5 GROUP 3(‘D:samdbredoredo03.log’) SIZE 50M
6 DATAFILE ‘D:samdbdatasystem01.dbf’ SIZE 200M autoextend on
7 SYSAUX DATAFILE ‘D:samdbdatasysaux01.dbf’ SIZE 200M
8 UNDO TABLESPACE UNDOTBS
9 DATAFILE ‘D:samdbdataUNDOTBS.dbf’ SIZE 100M
10 default temporary tablespace TEMP
11 tempfile ‘D:samdbtemptemp_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:samdb 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/samdb as sysdba

check which database you are in by using the following command

show parameter service_name

STEP: 10

Now place the pfile initsamdb.ora in this lacation
D:Oracleproduct10.2.0db_1database

Then try to connect like this

SQL>conn sys/samdb 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:Oracleproduct10.2.0db_1database

C:Documents and Settingsadministrator.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/samdb 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:ORANTrdbmsadmincatalog.sql
Execute catproc.sql SQL>@d:ORANTrdbmsadmincatproc.sql

STEP: 12

Now you need to edit the following files

D:Oracleproduct10.2.0db_1NETWORKADMINtnsnames.ora
D:Oracleproduct10.2.0db_1NETWORKADMINlistener.ora

Here are the files that I used…you can change those accordingly
# TNSNAMES.ORA Network Configuration File: D:ORANTnetworkadmintnsnames.ora
# Generated by Oracle configuration tools.
SAMADHAN=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = samadhan)
)
)
samdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inal075)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = samdb)
)
)
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:ORANTnetworkadminlistener.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 = samadhan)
(ORACLE_HOME = D:ORANT)
(SID_NAME = samadhan)
)
(SID_DESC =
(GLOBAL_DBNAME = samdb)
(ORACLE_HOME = D:samdb)
(SID_NAME = samdb)
)
)
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

Expert are always welcome for their valuable comment or suggestion for the above post.

Advertisements

Read Full Post »

SQL> $exp visynapse@uploaddb file=E:/uploaddb_19_10_09.dmp log=E:/uploadlog.log;

Export: Release 10.1.0.2.0 – Production on Mon Oct 19 11:18:48 2009

Copyright (c) 1982, 2004, Oracle. 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
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user VISYNAPSE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user VISYNAPSE
About to export VISYNAPSE’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export VISYNAPSE’s tables via Conventional Path …
. . exporting table CALCULATION_SECURITY 199 rows exported
. . exporting table CALCULATION_TABLE 207 rows exported
. . exporting table COLOUR 1 rows exported
. . exporting table CUBE 8 rows exported
. . exporting table DASHBOARD 2 rows exported
. . exporting table DASHBOARD_METRIC 8 rows exported
. . exporting table DASH_TABLE 3 rows exported
. . exporting table DATA_DICTIONARY 48 rows exported
. . exporting table DEFINITIONS_21 21 rows exported
. . exporting table DEFINITIONS_22 12 rows exported
. . exporting table DEFINITIONS_23 17 rows exported
. . exporting table DEFINITIONS_24 30 rows exported
. . exporting table DEFINITIONS_25 22 rows exported
. . exporting table DEFINITIONS_26 25 rows exported
. . exporting table DEFINITIONS_27 23 rows exported
. . exporting table DEFINITIONS_28 28 rows exported
. . exporting table DIMENSION_SECURITY 19 rows exported
. . exporting table DOMAIN 1 rows exported
. . exporting table FILTERS 0 rows exported
. . exporting table GRAINS 6148 rows exported
. . exporting table HEADER_FOOTER 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR_CS 0 rows exported
. . exporting table KPI_DIMENSION 0 rows exported
. . exporting table KPI_DIMENSION_CS 0 rows exported
. . exporting table LINKMETRIC 0 rows exported
. . exporting table METRIC 8 rows exported
. . exporting table METRIC_CALCULATION_SET 207 rows exported
. . exporting table METRIC_CUBE_SET 8 rows exported
. . exporting table REPORT_CHANNEL 0 rows exported
. . exporting table REPORT_CONFIG 23 rows exported
. . exporting table REPORT_HEADER 0 rows exported
. . exporting table SCORECARD 0 rows exported
. . exporting table SELECTION_DIMENSION 0 rows exported
. . exporting table SELECTION_FILTERCRITERIA 0 rows exported
. . exporting table SELECTION_FORMULA 0 rows exported
. . exporting table SELECTION_SHAREDREPORT 0 rows exported
. . exporting table SELECTION_TABLE 0 rows exported
. . exporting table STATIC_REPORT_TABLE 8 rows exported
. . exporting table SUMMARY_CONFIG 0 rows exported
. . exporting table TIME_FREQUENCY 6 rows exported
. . exporting table USER_DASHBOARD_SECURITY 2 rows exported
. . exporting table USER_GROUP 1 rows exported
. . exporting table USER_PROFILES 1 rows exported
. . exporting table USER_TARGETS 8 rows exported
. . exporting table VALUES_28_CONTROL 196 rows exported
. . exporting table VALUE_SECURITY 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

SQL>

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »