We are going to see about manual database creation on oracle 12c nonCDB

When we get the request to create a database on a Linux server. The basic prerequisite we need to check

DBNAME
SGA and PGA parameters
controlfile and logfile multiplexing
How many logfiles (Groups and members)
Alter log destination
User datafiles  and its size. 
Archive log destination

Create a pfile in $ORACLE_HOME/dbs/initdemodb.ora( assuming dbname as demodb)

Generally i used to give the below parameters

*.audit_file_dest='/DATA/app/oracle/admin/demodb/adump'
*.compatible='12.2.0.0.0'
*.control_files='/DATA/app/oracle/oradata/demodb/control01.ctl','/orapsr12tr2/app/oracle/oradata/demodb/control02.ctl','/orapsr12tr3/app/oracle/oradata/demodb/control03.ctl'
*.db_block_size=16384
*.db_cache_size=2048M
*.db_domain='world'
*.db_file_multiblock_read_count=16
*.db_files=1000
*.db_name='demodb'
*.diagnostic_dest='/DATA/app/oracle'
*.filesystemio_options='SETALL'
*.LOG_ARCHIVE_DEST_1='LOCATION=/orapsr12tr3/app/oracle/admin/demodb/archive'
*.LOG_ARCHIVE_FORMAT='demodb_%t_%s_%r.arc'
*.open_cursors=1200
*.pga_aggregate_target=1024M
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.resumable_timeout=21600
*.sessions=3305
*.sga_max_size=21474836480
*.sga_target=21474836480
*.shared_pool_size=1024M
*.streams_pool_size=100M
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

 

Create appropriate directories and then

[oracle@sdbt1 dbs]$ vi initdemodb.ora
[oracle@sdbt1 dbs]$ export ORACLE_SID=demodb
[oracle@sdbt1 dbs]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 25 00:16:05 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount
bash: Source: command not found
[oracle@sdbt1 dbs]$ mv inidemodb.ora initdemodb.ora
[oracle@sdbt1 dbs]$ exit

SQL> startup nomount
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 226496448 bytes
Database Buffers 599785472 bytes
Redo Buffers 3952640 bytes
SQL>

Then create the database using the below script.

CREATE DATABASE demodb 
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 1168
EXTENT MANAGEMENT LOCAL
LOGFILE
GROUP 1 (
'/DATA/app/oracle/oradata/demodb/redo01_a.log',
'/DATA/app/oracle/oradata/demodb/redo01_b.log'
) SIZE 1000M BLOCKSIZE 512,
GROUP 2 (
'/DATA/app/oracle/oradata/demodb/redo02_a.log',
'/DATA/app/oracle/oradata/demodb/redo02_b.log'
) SIZE 1000M BLOCKSIZE 512,
GROUP 3 (
'/DATA/app/oracle/oradata/demodb/redo03_a.log',
'/DATA/app/oracle/oradata/demodb/redo03_b.log'
) SIZE 1000M BLOCKSIZE 512,
GROUP 4 (
'/DATA/app/oracle/oradata/demodb/redo04_a.log',
'/DATA/app/oracle/oradata/demodb/redo04_b.log'
) SIZE 1000M BLOCKSIZE 512,
GROUP 5 (
'/DATA/app/oracle/oradata/demodb/redo05_a.log',
'/DATA/app/oracle/oradata/demodb/redo05_b.log'
) SIZE 1000M BLOCKSIZE 512
DATAFILE
'/DATA/app/oracle/oradata/demodb/system01.dbf' size 1000M extend management local
SYSAUX DATAFILE '/DATA/app/oracle/oradata/demodb/sysaux01.dbf' size 1000M
UNDO TABLESPACE UNDOTBS1 DATAFILE '/DATA/app/oracle/oradata/demodb/undotbs01.dbf' size 1000M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/DATA/app/oracle/oradata/demodb/temp01.dbf' size 500M
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
;

 

Then run the below scripts from SQL*Plus as sysdba

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

 

Check if the above scripts ran fine

SQL> col compname format a30
SQL> col comp_name format a30
SQL> l
1 select
2 comp_name,
3 status,
4 version
5* from dba_registry
SQL> /

COMP_NAME STATUS VERSION
------------------------------ ----------- ------------------------------
Oracle Database Catalog Views VALID 12.2.0.1.0
Oracle Database Packages and T VALID 12.2.0.1.0
ypes

Oracle XML Database VALID 12.2.0.1.0

 

After this run the below script as system .

SQL> @?/sqlplus/admin/pupbld

Session altered.


Synonym dropped.


PL/SQL procedure successfully completed.


View dropped.


View created.


Grant succeeded.


Synonym dropped.


Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

Synonym created.


Synonym dropped.


Synonym created.


Session altered.

SQL>
Recommended Posts

Start typing and press Enter to search