What does Oracle do when you issue “Create Database” command in Oracle 10g?

Well, before we dive into the gory details, lets first create an init.ora file and start the instance in nomount state. Here’s how we do it: 1) Create init.ora I choose my SID as TEST, you may opt anything you like. Following is my initTEST.ora file: ########################################### # Cache and I/O ########################################### db_block_size=8192 db_files=60 db_cache_size =256M db_file_multiblock_read_count=8 log_buffer=3145728 ########################################### # Database Identification ########################################### db_name=TEST instance_name=TEST ########################################### # Diagnostics and Statistics ########################################### background_dump_dest=c:\oracle\ora10g\admin\TEST\bdump core_dump_dest=c:\oracle\ora10g\admin\TEST\cdump timed_statistics=TRUE user_dump_dest=c:\oracle\ora10g\admin\TEST\udump ########################################### # File Configuration ########################################### control_files=("c:\oracle\ora10g\admin\TEST\control1\control01.ctl", "c:\oracle\ora10g\admin\TEST\control2\control02.ctl") ########################################### # Miscellaneous ########################################### compatible=10.2.0.1 ########################################### # Pools ########################################### shared_pool_size=256M ########################################### # Security and Auditing ########################################### remote_login_passwordfile=EXCLUSIVE ########################################### # System Managed Undo and Rollback Segments ########################################### undo_management=AUTO undo_retention=10800 undo_tablespace=UNDOTBS1 ############################################### # PGA Settings ############################################### pga_aggregate_target=200M ############################################### # MISC ############################################### GLOBAL_NAMES=TRUE 2) Start the instance

Now, we will start our instance in NOMOUNT state.

SQL> startup nomount pfile=C:\oracle\ora10g\admin\TEST\pfile\initTEST.ora ORACLE instance started. Total System Global Area 570425344 bytes Fixed Size 1250188 bytes Variable Size 293604468 bytes Database Buffers 268435456 bytes Redo Buffers 7135232 bytes This will start the following processes PMON, MMAN, DBW0, LGWR, CKPT, and SMON. 3) Create database:

Finally, issue the Create database command:

SQL> @Create_Database_TEST.sql

Database Created.

########################################### #Create_Database_TEST.sql

###########################################

CREATE DATABASE TEST USER SYS IDENTIFIED BY mgrTEST USER SYSTEM IDENTIFIED BY mgrTEST LOGFILE GROUP 1 ('C:\oracle\ora10g\admin\TEST\data\redo01.log') SIZE 128M, GROUP 2 ('C:\oracle\ora10g\admin\TEST\data\redo02.log') SIZE 128M, GROUP 3 ('C:\oracle\ora10g\admin\TEST\data\redo03.log') SIZE 128M MAXLOGFILES 10 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 60 MAXINSTANCES 1 CHARACTER SET AR8ISO8859P6 DATAFILE 'C:\oracle\ora10g\admin\TEST\data\system01.dbf' SIZE 257M autoextend on EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE 'C:\oracle\ora10g\admin\TEST\data\sysaux01.dbf' SIZE 257M autoextend on DEFAULT TEMPORARY TABLESPACE TBS_1 TEMPFILE 'C:\oracle\ora10g\admin\TEST\data\temp01.dbf' SIZE 257M UNDO TABLESPACE undotbs1 DATAFILE 'C:\oracle\ora10g\admin\TEST\data\undotbs01.dbf' Size 257M AUTOEXTEND ON MAXSIZE UNLIMITED; ###########################################

Let’s see what Oracle actually does when you issue the Create Database command.

1. Database is Mounted in Exclusive Mode.
2. Mounts Redo Thread 1 and opens the log at sequence 1.
3. Cache recovery process is enabled by SMON.
4. Oracle then creates the SYSTEM tablespace
5. Creates a rollback segment named SYSTEM in SYSTEM tablespace. As there is no UNDO tablespace yet created in the database, this tablespace is used by Oracle during the rest of the creation process. It’s size is also very small Initial = 50K and Next = 50K. Once the database is created you can issue the following query SQL> select owner, segment_type, tablespace_name, bytes, blocks, extents, initial_extent, next_extent 2 from dba_segments 3 where segment_name = 'SYSTEM'; OWNER SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT ----- ------------ --------------- ---------- ---------- ---------- -------------- ----------- SYS ROLLBACK SYSTEM 393216 48 6 114688 1048576
6. Oracle now creates the UNDO tablespace and makes it online.
7. Creates SYSAUX tablespace.
8. Creates temporary tablespace
9. Alters the temporary tablespace of the database to the newly created temporary tablespace.
10. Alters the database to SYSTEM tablespace.
11. SMON process enables the transaction recovery.
12. QMNC process is started.
13. Database is opened.