When the database is created manually, there is complete control over the process. Tablespaces can be added or reduced and the flow of the creation process can be managed. Here are the steps to be followed to create a database manually.
- Make SID:-Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID environment variable accordingly. This identifier is used to avoid confusion with other Oracle instances that you may create later and run concurrently on your system. The value of the DB_NAME initialization parameter should match the SID setting.
- Preparing the Parameter File:- Modify the initSID.ora by editing the parameters . Store your initialization parameter file in Oracle's default location, using the default name. That way, when you start your database, it is not necessary to specify the PFILE parameter because Oracle automatically looks in the default location for the initialization parameter file. For UNIX $ORACLE_HOME/dbs is default location of parameter file and For Window the default location of parameter file is $ORACLE_HOME\database. The following is the initialization parameter file used to create new database.
db_name =dbname
shared_pool=52428800
db_block_size= 8192
Control_files=’/opt/db01/con.ctl’
background_dump_dest=’/opt/db01/bdump’
user_dump_dest=’/opt/db01/udump’
undo_management=auto
undo_tablespace=undo01
- Connect to the instance:- connect oracle instance as SYSDBA
Export_SID=dbname
Sqlplus ‘/as sysdba’
- Start the Instance:- Start an instance at nomount stage . Use the STARTUP command with the NOMOUNT option. Initialization parameter file is stored in the default location, you are not required to specify the PFILE clause: SQL>startup nomount
- Creating the Database:- after connecting the intance at nomount stage u execute create database statement in SQL prompt.
- Create database dbname
- Datafile ‘/opt/tt/data.dbf’ size 200m
- Sysaux datafile’/opt/tt/sysaux.dbf’ size 150m
- Logfile group1 ‘/opt/tt/log01.log’ size 10m
- group2 ‘/opt/tt/log02.dbf’ size 10m
- defaulttemporary tablespace temp tempfile’/opt/tt/temp.dbf’ size 50m
- undotablespace undo1 datafile ‘/opt/tt/undo.dbf’ size 50m
- Max instance 4
- MaxDatafiles 100
- MaxLogMembers 6
- MaxLogHistory 200
- Run necessary scripts to build views, synonyms, etc.
- Catalog.sql – create the views of data dictionary table and the dynamic performance views.
- Catproc.sql- establishes the usage of PL/SQL functionally and many of the Pl/SQL Oraclesupplied packages.
Catproc statement- @?/rdbms/admin/catproc.sql
- Create SPfile:- The SPFILE must initially be created from an initialization parameter file. You must have the SYSDBA or SYSOPER role to execute the CREATE SPFILE statement. Create spfile from pfile
- Database Backup:- You should make a full backup of the database to ensure that you have a complete set of files from which to recover if a media failure occurs.
Related Post:-
