Step by Step manually creation of an Oracle 19c Database on Linux 7
In the previous article, we have demonstrated Step by step silent installation of oracle 19c on Linux 7, now here we are going to create Oracle 19c standalone database. We can create an Oracle database by using the below methods.
1. DBCA(GUI)
2. Manually
Basically, some organizations, recommend using a manual method for creating a database, or due to less budget or not having GUI option available, we have to perform manual creation.
Here we are going to illustrate, how to create a database manually in oracle 19c on a Linux server.
Below are the pre-requisite to creating a database:
1. Create require directories
Here we are creating require directories that are required to match exactly as pfile and database creation command.
mkdir -p /u01/app/oracle/admin/test/adump
mkdir -p /u02/oradata/test/
mkdir -p /u01/app/oracle/fast_recovery_area
chmod 655 /u01/app/oracle/admin/test/adump
chmod 655 /u02/oradata/test/
chmod 655 /u01/app/oracle/fast_recovery_area
2. Pfile(init.ora) file
Whenever we start the instance, It will read the pfile(init.ora)/spfile and set the parameter value which is mentioned in the file, and then mount the instance accordingly.
Below are the example of pfile(init.ora) and the same has been created in default location $ORACLE_HOME/dbs
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.compatible='19.3.0.0.0'
*.control_files='/u02/oradata/test/control01.ctl','/u01/app/oracle/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='test'
*.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'
*.memory_max_target=754974720
*.memory_target=754974720
*.open_cursors=300
*.os_roles=FALSE
*.processes=300
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
Note: You can modify/add parameters according to your requirements along with required directories.
3. Create a database creation script.
In the database create a script, we are going to specify the datafile ,redo log files, temp files, and undo tablespace.
Below is the database creation script and made one SQL file dbcreate.sql.
CREATE DATABASE test
USER SYS IDENTIFIED BY manager
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/u02/oradata/test/redo01.log') SIZE 50M,
GROUP 2 ('/u02/oradata/test/redo02.log') SIZE 50M,
GROUP 3 ('/u02/oradata/test/redo03.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u02/oradata/test/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u02/oradata/test/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile '/u02/oradata/test/users01.dbf' size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/test/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1
DATAFILE '/u02/oradata/test/undotbs01.dbf'
SIZE 200M;
Now, we moving ahead to create a database. Below the steps that we followed to create a manual database in oracle 19c
Step1:-specify oracle environment
In this step, we specify ORACLE_SID and ORACLE_HOME
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [test] ? test
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
Step2:- Start database.
Here we are starting the database in the nomount stage from pfile that we created in pre-requisite above in step2.
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 02:27:08 2020
Version 19.3.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 754971128 bytes
Fixed Size 8901112 bytes
Variable Size 478150656 bytes
Database Buffers 264241152 bytes
Redo Buffers 3678208 bytes
Step3:- Create database
After starting the database in nomount mode, Now we run dbcreate.sql to create the database.
SQL> set echo on
SQL> @dbcreate.sql
SQL> CREATE DATABASE test
2 USER SYS IDENTIFIED BY manager
3 USER SYSTEM IDENTIFIED BY manager
4 LOGFILE GROUP 1 ('/u02/oradata/test/redo01.log') SIZE 50M,
5 GROUP 2 ('/u02/oradata/test/redo02.log') SIZE 50M,
6 GROUP 3 ('/u02/oradata/test/redo03.log') SIZE 50M
7 MAXLOGFILES 5
8 MAXLOGMEMBERS 5
9 MAXLOGHISTORY 50
10 MAXDATAFILES 100
11 MAXINSTANCES 1
12 DATAFILE '/u02/oradata/test/system01.dbf' SIZE 100M autoextend on
13 SYSAUX DATAFILE '/u02/oradata/test/sysaux01.dbf' SIZE 100M autoextend on
14 DEFAULT TABLESPACE users datafile '/u02/oradata/test/users01.dbf' size 100m autoextend on
15 DEFAULT TEMPORARY TABLESPACE temp
16 TEMPFILE '/u02/oradata/test/temp01.dbf' SIZE 50m
17 UNDO TABLESPACE undotbs1
18 DATAFILE '/u02/oradata/test/undotbs01.dbf'
19 SIZE 200M;
Database created.
Step4:- Verify.
After completion of the database creation script in step3, Now we have to verify the database is ready for use.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ WRITE
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
test OPEN
Post Steps
Step1:- Post Scripts.
Below post scripts create database data dictionary views which is require 100% database functional.
catalog.sql
catproc.sql
pupbld.sql
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
Session altered.
Session altered.
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error and terminate the SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
Session altered.
-------trimmed Data
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATALOG 2020-12-11 02:07:05
Session altered.
Session altered.
SQL> spool off
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
Session altered.
Session altered.
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
PL/SQL procedure successfully completed.
----- trimmed Data
SQL> @?/rdbms/admin/sqlsessend.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/3 2018/07/25 13:50:02 surman Exp $
SQL> Rem
SQL> Rem sqlsessend.sql
SQL> Rem
SQL> Rem Copyright (c) 2013, 2018, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem sqlsessend.sql - SQL session end
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem Any commands which should be run at the end of all oracle
SQL> Rem supplied scripts.
SQL> Rem
SQL> Rem NOTES
SQL> Rem See sqlsessstart.sql for the corresponding start script.
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/sqlsessend.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/sqlsessend.sql
SQL> Rem SQL_PHASE: MISC
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem surman 05/04/18 - 27464252: Update SQL_PHASE
SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
SQL> Rem surman 03/08/13 - Created
SQL> Rem
SQL>
SQL> alter session set "_ORACLE_SCRIPT" = false;
Session altered.
SQL>
SQL>
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catproc.sql
SQL> Rem *********************************************************************
SQL> $ORACLE_HOME/sqlplus/admin/pupbld.sql
Session altered.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
PL/SQL procedure successfully completed.
DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
Grant succeeded.
DROP PUBLIC SYNONYM PRODUCT_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Session altered.
Step2:- Password file (orapwd)
Create a password file under ($ORACLE_HOME/dbs)
oracle@localhost u02]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ orapwd file=orapwdtest password=test force=y entries=10
[oracle@localhost dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@localhost dbs]$ ls -lrt orapwdtest
-rw-r-----. 1 oracle oinstall 6144 Nov 28 09:29 orapwdtest
Step3:- create spfile.
Currently, the database is running from a pfile, Now we are creating spfile from pfile and start a database with spfile.
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> exit
Now starting database from spfile.
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 02:32:26 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 754971128 bytes
Fixed Size 8901112 bytes
Variable Size 478150656 bytes
Database Buffers 264241152 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/spfiletest.ora
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
test OPEN
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ WRITE
Click here for Step by step silent installation of oracle 19c on Linux 7
Click here for Step by Step Install Oracle Enterprise Linux (OEL) 7
So finally congratulation, 19c database to ready to rock.
This document is only for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.
Related Articles
- Oracle Critical Database Patch ID for October 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for July 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2023 along with enabled Download Link
- Oracle Critical Database Patch ID for January 2023 along with enabled Download Link
Very nice and very easy to understand
Thanks Rajesh!
Great to hear your feedback. Do keep following us for many more articles !
Regards,
Team DBsGuru.
Hi
Nice article… Looks like the PDB datafiles are missing
thanks
Much thanks Johan and appreciate your valuable time and feedback! This article is for manual standalone DB creation (non-container) so didn’t cover PDB. However, we accept it as a suggestion, and definitely, we will publish a separate article on it which will cover manual DB creation (CDB and PDB).
Thanks.
Team DBsGuru