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