Manual Oracle database creation 19c on Oracle-Solaris_11.4
SunOS kolsol.localdomain 5.11 11.4.0.15.0 i86pc i386 i86pc
If we don't have an option to use GUI mode dbca, then we can create database manually. we can use dbca in silent mode also, but this is for our knowledge , that how the oracle is using internal processes (.bsq files) to create a database when we issue the command " CREATE DATABASE"
BSQ file is read by the instance and is used to create the database system tablespace and all of the tables, clusters, indexes, sequences, and initial data required by the kernel code to store and maintain the metadata for user-created objects.
Below are the prerequisites to create a database :
1.Create the required directories.
mkdir -p /u01/app/oracle/admin/testdb/adump
mkdir -p /u01/app/oracle/oradata/testdb/
mkdir -p /u01/app/oracle/fast_recovery_area
chmod 655 /u01/app/oracle/admin/testdb/adump
chmod 655 /u01/app/oracle/oradata/testdb/
chmod 655 /u01/app/oracle/fast_recovery_area
2. create the pfile in ORACLE_HOME/dbs location with name inittestdb.ora
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.compatible='19.5.0.0.0'
*.control_files='/u01/app/oracle/oradata/testdb/control01.ctl','/u01/app/oracle/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='testdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.processes=300
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
-bash-4.4$ export ORACLE_SID=testdb
-bash-4.4$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
-bash-4.4$ sqlplus / as sysdba
-bash: sqlplus: command not found
-bash-4.4$ export PATH=$PATH:$ORACLE_HOME/bin
-bash-4.4$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 17:01:48 2022
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 281016480 bytes
Fixed Size 8882336 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3698688 bytes
SQL>
check the alert log is created or not,
/u01/app/oracle/diag/rdbms/testdb/testdb/trace
-bash-4.4$ ls -ltr alert_testdb.log
-rw-r----- 1 oracle oinstall 6669 Jan 1 17:03 alert_testdb.log
it is created
from alert log , we can find the below details
ORACLE_HOME: /u01/app/oracle/product/19.0.0/dbhome_1
System name: SunOS
Node name: kolsol.localdomain
Release: 5.11
Version: 11.4.0.15.0
Machine: i86pc
Using parameter settings in server-side pfile /u01/app/oracle/product/19.0.0/dbhome_1/dbs/inittestdb.ora
After starting the database in nomount mode, then create the database now
SQL> CREATE DATABASE testdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SIZE 200M;
CREATE DATABASE testdb
*
ERROR at line 1:
ORA-01518: CREATE DATABASE must specify more than one log file
here we can see that , we require atleast two log files to create a database, means , one is for writing and another is for archiving .
lets add one more log file and create the database
SQL> CREATE DATABASE testdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/testdb/redo02.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
SIZE 200M;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE DATABASE testdb
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/oradata/testdb/control01.ctl'
ORA-27038: created file already exists
Additional information: 1
here in the first try , the control file and redolog files are already created, lets remove those files, and then try
SQL> startup nomount
ORACLE instance started.
Total System Global Area 281016480 bytes
Fixed Size 8882336 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3698688 bytes
SQL> CREATE DATABASE testdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/testdb/redo02.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
SIZE 200M; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Database created.
from alert log we can see db creation with some .bsq files
processing ?/rdbms/admin/dcore.bsq
create tablespace SYSTEM datafile '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
2022-01-01T17:18:00.134237+05:30
Endian type of dictionary set to little
Completed: create tablespace SYSTEM datafile '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
alter tablespace system force logging
Completed: alter tablespace system force logging
2022-01-01T17:18:03.687898+05:30
processing ?/rdbms/admin/dsqlddl.bsq
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING
2022-01-01T17:18:07.513338+05:30
Completed: CREATE TABLESPACE sysaux DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING
alter tablespace system default compress for all operations
Completed: alter tablespace system default compress for all operations
alter tablespace sysaux default compress for all operations
Completed: alter tablespace sysaux default compress for all operations
2022-01-01T17:18:07.676503+05:30
processing ?/rdbms/admin/dplsql.bsq
processing ?/rdbms/admin/dtxnspc.bsq
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
SIZE 200M
2022-01-01T17:18:15.295970+05:30
[1543] Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
SIZE 200M
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m
Completed: CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"
CREATE TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
SEGMENT SPACE MANAGEMENT AUTO
2022-01-01T17:18:18.232890+05:30
2022-01-01T17:18:18.232890+05:30
Completed: CREATE TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DEFAULT TABLESPACE "USERS"
Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2022-01-01T17:18:18.434077+05:30
processing ?/rdbms/admin/dfmap.bsq
processing ?/rdbms/admin/denv.bsq
processing ?/rdbms/admin/drac.bsq
processing ?/rdbms/admin/dsec.bsq
2022-01-01T17:18:21.857237+05:30
processing ?/rdbms/admin/doptim.bsq
processing ?/rdbms/admin/dobj.bsq
2022-01-01T17:18:23.372012+05:30
processing ?/rdbms/admin/djava.bsq
processing ?/rdbms/admin/dpart.bsq
processing ?/rdbms/admin/drep.bsq
2022-01-01T17:18:26.086262+05:30
processing ?/rdbms/admin/daw.bsq
processing ?/rdbms/admin/dsummgt.bsq
2022-01-01T17:18:27.439197+05:30
processing ?/rdbms/admin/dtools.bsq
processing ?/rdbms/admin/dexttab.bsq
processing ?/rdbms/admin/ddm.bsq
2022-01-01T17:18:28.683148+05:30
processing ?/rdbms/admin/dlmnr.bsq
processing ?/rdbms/admin/ddst.bsq
processing ?/rdbms/admin/dfba.bsq
2022-01-01T17:18:29.786777+05:30
processing ?/rdbms/admin/dpstdy.bsq
processing ?/rdbms/admin/drupg.bsq
processing ?/rdbms/admin/dtlog.bsq
processing ?/rdbms/admin/dmisc.bsq
processing ?/rdbms/admin/dhcs.bsq
Successfully created internal service SYS$BACKGROUND at open
Successfully created internal service SYS$USERS at open
Now, check in the db
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTDB READ WRITE
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
testdb OPEN
POST STEPS:
Below post scripts create database data dictionary views which is require to fully funtional database creation.
catalog.sql
catproc.sql
pupbld.sql
it creates default dictionary views, we have the dictionaries like dba_users, but accessing views are not available.
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
the below pupbld.sql run as system user.
@?/sqlplus/admin/pupbld.sql
generate password file
bash-4.4$ orapwd file=orapwdtestdb password=oracle321! force=y entries=10
OPW-00029: Password complexity failed for SYS user : Password must not be too simple.
bash-4.4$ orapwd file=orapwdtestdb password=oracle$321! force=y entries=10
OPW-00029: Password complexity failed for SYS user : Password must not be too simple.
by using format=12 , password is created with simple string
orapwd file=orapwdtestdb password=oracle force=y format=12 entries=10
-rw-r----- 1 oracle oinstall 2048 Jan 1 18:58 orapwtestdb
Now create spfile
bash-4.4$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 19:00:06 2022
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string testdb.localdomain
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile from pfile;
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281016480 bytes
Fixed Size 8882336 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3698688 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/spfiletestdb.ora
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 268M
sga_min_size big integer 0
sga_target big integer 0
unified_audit_sga_queue_size integer 1048576
SQL>
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 10M
I didnot mentioned the SGA and PGA target in the pfile, the above are the values it is created with
Now the database is ready, this is for testing purpose only.
No comments:
Post a Comment