Manual Creation of Non Container 19c DB



Let us stqart Manual Creation of Non Container 19c DB Now:

Create Folder where we need to create a Database

1. Pre requisites : Create the required directories

Here we are creating the directories that are required in their respective path

	[oracle@tesdb ~]$ mkdir tesdb
	[oracle@tesdb ~]$ cd tesdb

	[oracle@tesdb tesdb]$ mkdir admin oradata
	[oracle@tesdb tesdb]$ cd admin

	[oracle@tesdb admin]$  mkdir pfile diag adump
	[oracle@tesdb admin]$ cd ../oradata

	[oracle@tesdb oradata]$ mkdir data control log arch

	[oracle@tesdb oradata]$ ll
	total 0
	drwxr-xr-x. 2 oracle oinstall 6 Nov  3 15:36 arch
	drwxr-xr-x. 2 oracle oinstall 6 Nov  3 15:36 control
	drwxr-xr-x. 2 oracle oinstall 6 Nov  3 15:36 data
	drwxr-xr-x. 2 oracle oinstall 6 Nov  3 15:36 log

	[oracle@tesdb oradata]$ pwd
	/home/oracle/tesdb/oradata

	[oracle@tesdb oradata]$ cd ../admin
	[oracle@tesdb admin]$ cd pfile

	[oracle@tesdb pfile]$ ll
	total 0
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@tesdb pfile]$ vi inittesdb.ora
	[oracle@tesdb pfile]$ cat inittesdb.ora

	db_name=tesdb
	sga_target=600m

	pga_aggregate_target=300m
	diagnostic_dest=/home/oracle/tesdb/admin/adump
	control_files='/home/oracle/tesdb/oradata/control/control01.ctl'

	[oracle@tesdb pfile]$ cd /home/oracle/tesdb/oradata/control/
	[oracle@tesdb control]$ ll
	total 0
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 con.sql.

	[oracle@tesdb control]$ vi con.sql
	[oracle@tesdb control]$ cat con.sql

	create database tesdb

	datafile '/home/oracle/tesdb/oradata/data/system01.dbf' size 600m

	sysaux datafile '/home/oracle/tesdb/oradata/data/sysaux.dbf' size 500m

	undo tablespace undotbs datafile
	'/home/oracle/tesdb/oradata/data/undo01.dbf' size 400m

	default temporary tablespace temp tempfile
	'/home/oracle/tesdb/oradata/data/temp01.dbf' size 100m

	default tablespace users datafile
	'/home/oracle/tesdb/oradata/data/users01.dbf' size 100m

	logfile group 1
	'/home/oracle/tesdb/oradata/log/log01.log' size 50m,
		group 2 '/home/oracle/tesdb/oradata/log/log02.log'
		size 50m;

	[oracle@tesdb control]$ export ORACLE_SID=tesdb
	[oracle@tesdb control]$
Bring up the Database in nomount mode by specifying pfile path: 
	[oracle@tesdb control]$ export ORACLE_SID=tesdb
	[oracle@tesdb control]$ sqlplus / as sysdba

	SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 3
	15:39:39 2023
	Version 19.3.0.0.0
	Copyright (c) 1982, 2019, Oracle.  All rights reserved.
	Connected to an idle instance.
	SQL> startup nomount pfile=
	'/home/oracle/tesdb/admin/pfile/inittesdb.ora'
	ORACLE instance started.

	Total System Global Area  629144664 bytes
	Fixed Size		    8899672 bytes
	Variable Size		  176160768 bytes
	Database Buffers	  436207616 bytes
	Redo Buffers		    7876608 bytes
Execute the created script 
	SQL> @/home/oracle/tesdb/oradata/control/con.sql;
	Database created.

	SQL>
Execute the below - POST SCRIPTS: 
	SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql;
	SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql;
	SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

	PL/SQL procedure successfully completed.
	Grant succeeded.
	Synonym created.
	Session altered.
	Session altered.

	PL/SQL procedure successfully completed.

	TIMESTAMP
	---------------------------------------------
	COMP_TIMESTAMP CATALOG	  2023-11-03 15:55:06
	Session altered.
	Session altered.

	SQL>
	SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql;
	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>#
	SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
	SQL> -- Copyright (c) 1988, 2017, Oracle and/or its affiliates.
	All rights reserved.

	SQL> --
	SQL> -- NAME
	SQL> --   pupbld.sql
	SQL> --
DESCRIPTION 
	SQL> --   Script to install the SQL*Plus PRODUCT_USER_PROFILE tables.  These
	SQL> --   tables allow SQL*Plus to disable commands per user.  The tables
	SQL> --   are used only by SQL*Plus and do not affect other client tools
	SQL> --   that access the database.  Refer to the SQL*Plus manual for table
	SQL> --   usage information.
	SQL> --
	SQL> --   This script should be run on every database that SQL*Plus connects
	SQL> --   to, even if the tables are not used to restrict commands.
	SQL>
USAGE 
	SQL> --   sqlplus system/ @pupbld
	SQL> --
	Find the status of instance
	SQL>select status from v$instance;

	STATUS
	------------
	OPEN

	1 row selected.

	SQL> select name,dbid,open_mode,log_mode from v$database;

	NAME            DBID OPEN_MODE            LOG_MODE
	--------- ---------- -------------------- ------------
	TESDB     1905581437 READ WRITE           NOARCHIVELOG