Creating a new database
Usually when you install Oracle, it automatically creates a database for you (though you need to specify a database name for this). Otherwise, you usually use graphical Database Configuration Assistant (DBCA) to create/manage databases. So, you may not even require creating a database from command prompt. However, in case you need to, the steps are shown below.
In OS command prompt, use
ORADIM -NEW -SID sid_name -INTPWD sys_password -MAXUSERS 10
In Windows, go to Control Panel – Services
Check service named OracleServiceSID started.
In My Computer, Environment tab, define
ORACLE_SID=sid_name
Create folders where you want to keep database files eg.
F:\MYDB\DATAFILES, UDUMP, BDUMP, CDUMP, LOGS, ARCHIVE etc.
Create a new initSID.ora file with relevant parameters
BACKGROUND_DUMP_DEST=F:\MYDB\BDUMP
CORE_DUMP_DEST=F:\MYDB\CDUMP
USER_DUMP_DEST=F:\MYDB\UDUMP
DB_NAME= sid_name
INSTANCE_NAME= sid_name
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDO01
Start SQL Plus
/ AS SYSDBA
STARTUP NOMOUNT
CREATE DATABASE MYDB
MAXDATAFILES 30
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXINSTANCES 1
MAXLOGHISTORY 1
DATAFILE 'F:\MYDB\DATAFILES\SYSTEM01.DBF' SIZE 100M
LOGFILE
GROUP 1 'F:\MYDB\LOG\LOG01.DBF' SIZE 10M,
GROUP 2 'F:\MYDB\LOG\LOG02.DBF' SIZE 10M
UNDO TABLESPACE UNDO01
DATAFILE 'F:\MYDB\UNDO\UNDO01.DBF' SIZE 50M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 'F:\MYDB\TEMP\TEMP01.DBF' SIZE 10M
CHARACTER SET WE8MSWIN1252
/
CREATE SPFILE FROM PFILE;
Note: Above command was for Oracle 9i. From 10g, you need Sysaux table space as well.
Now run calalog.sql (creates data dictionary views) and catproc.sql (creates PL/SQL packages) from $ORACLE_HOME\RDBMS\ADMIN
After installing Oracle, several services are registered in the server computer. In Windows, (for Oracle 9.2) following services must be started as a minimum to run Oracle – OracleOraHome92Agent, OracleOraHome92TNSListener and OracleServiceSID (where SID is the name of database you created).
In Windows Vista running Oracle 11g, I usually start/stop my test database via following batch file (say StartOracle.bat – MYDB is name of my database)
net start OracleServiceMYDB
net start OracleOraDb11g_home1TNSListener
net start OracleVssWriterMYDB
net start OracleDBConsoleMYDB
To stop database services, you can create a similar batch file by replacing start with stop. In Vista, you need to run these batch files as administrator.
If you do not start OracleDBConsole<SID> service, then you won’t be able to start web based Enterprise Manager – which you usually start from https://localhost:1158/em (where localhost may be substituted for your computer name)
You usually log on to Oracle server from “SQL Plus” as – SYS/PASSWORD@DATABASENAME AS SYSDBA. Sometimes you can also log in as simply “/ AS SYSDBA” only when you are physically in the same computer where Oracle server is installed. Please note that Server Manager tool is no longer available from Oracle 9i onwards. You can do everything using SQL Plus!
For various day-to-day database works, you may find SQL Plus cumbersome to work with. For this, several 3rd party GUI tools are available. Two most popular tools are – PL/SQL Developers and TOAD. From 11g, Windows SQL Plus has been replaced by SQL Developer suite bundled with 11g. DOS version of SQL Plus is still available though!
In OS command prompt, use
ORADIM -NEW -SID sid_name -INTPWD sys_password -MAXUSERS 10
In Windows, go to Control Panel – Services
Check service named OracleServiceSID started.
In My Computer, Environment tab, define
ORACLE_SID=sid_name
Create folders where you want to keep database files eg.
F:\MYDB\DATAFILES, UDUMP, BDUMP, CDUMP, LOGS, ARCHIVE etc.
Create a new initSID.ora file with relevant parameters
BACKGROUND_DUMP_DEST=F:\MYDB\BDUMP
CORE_DUMP_DEST=F:\MYDB\CDUMP
USER_DUMP_DEST=F:\MYDB\UDUMP
DB_NAME= sid_name
INSTANCE_NAME= sid_name
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDO01
Start SQL Plus
/ AS SYSDBA
STARTUP NOMOUNT
CREATE DATABASE MYDB
MAXDATAFILES 30
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXINSTANCES 1
MAXLOGHISTORY 1
DATAFILE 'F:\MYDB\DATAFILES\SYSTEM01.DBF' SIZE 100M
LOGFILE
GROUP 1 'F:\MYDB\LOG\LOG01.DBF' SIZE 10M,
GROUP 2 'F:\MYDB\LOG\LOG02.DBF' SIZE 10M
UNDO TABLESPACE UNDO01
DATAFILE 'F:\MYDB\UNDO\UNDO01.DBF' SIZE 50M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 'F:\MYDB\TEMP\TEMP01.DBF' SIZE 10M
CHARACTER SET WE8MSWIN1252
/
CREATE SPFILE FROM PFILE;
Note: Above command was for Oracle 9i. From 10g, you need Sysaux table space as well.
Now run calalog.sql (creates data dictionary views) and catproc.sql (creates PL/SQL packages) from $ORACLE_HOME\RDBMS\ADMIN
After installing Oracle, several services are registered in the server computer. In Windows, (for Oracle 9.2) following services must be started as a minimum to run Oracle – OracleOraHome92Agent, OracleOraHome92TNSListener and OracleServiceSID (where SID is the name of database you created).
In Windows Vista running Oracle 11g, I usually start/stop my test database via following batch file (say StartOracle.bat – MYDB is name of my database)
net start OracleServiceMYDB
net start OracleOraDb11g_home1TNSListener
net start OracleVssWriterMYDB
net start OracleDBConsoleMYDB
To stop database services, you can create a similar batch file by replacing start with stop. In Vista, you need to run these batch files as administrator.
If you do not start OracleDBConsole<SID> service, then you won’t be able to start web based Enterprise Manager – which you usually start from https://localhost:1158/em (where localhost may be substituted for your computer name)
You usually log on to Oracle server from “SQL Plus” as – SYS/PASSWORD@DATABASENAME AS SYSDBA. Sometimes you can also log in as simply “/ AS SYSDBA” only when you are physically in the same computer where Oracle server is installed. Please note that Server Manager tool is no longer available from Oracle 9i onwards. You can do everything using SQL Plus!
For various day-to-day database works, you may find SQL Plus cumbersome to work with. For this, several 3rd party GUI tools are available. Two most popular tools are – PL/SQL Developers and TOAD. From 11g, Windows SQL Plus has been replaced by SQL Developer suite bundled with 11g. DOS version of SQL Plus is still available though!
No comments:
Post a Comment