Create Database and Drop Database

Database Creation:

To Start/Open a database we need some of the mandatory files they are 

• SPfile/Pfile (Nomount stage)
• Control files (Mount stage) and 
• Datafiles (read_write)

So, to create a database we need to create a SPfile/Pfile as SPfile is binary file we cannot create it so create a Pfile with the name init<database_name>.ora

For reference refer the Pfile below(for container database):

database_name.__data_transfer_cache_size=0

database_name.__db_cache_size=721420288

database_name.__inmemory_ext_roarea=0

database_name.__inmemory_ext_rwarea=0

database_name.__java_pool_size=0

database_name.__large_pool_size=16777216

database_name.__oracle_base='/data/app/oracle'#ORACLE_BASE set from environment

database_name.__pga_aggregate_target=402653184

database_name.__sga_target=1207959552

database_name.__shared_io_pool_size=67108864

database_name.__shared_pool_size=352321536

database_name.__streams_pool_size=33554432

database_name.__unified_pga_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/database_name/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/u01/app/oracle/oradata/container/database_name/control01.ctl','/u01/app/oracle/oradata/container/database_name/control02.ctl'

*.db_block_size=8192

*.db_name='database_name'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=database_nameXDB)'

*.enable_pluggable_database=true

*.local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=172.16.12.84)(PORT=1522))'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=384m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1152m

*.undo_tablespace='UNDOTBS1'

Once after creating pfile make sure the paths present in pfile are present in the server.

~> Now, set the environment like ORACLE_SID, ORACLE_HOME, ORACLE_BASE for the database you are going to create.
~> log into sqlplus command line like sqlplus / as sysdba
~> which says connected to idle instance, with the pfile which we have created open database in nomount stage with the below command

SQL> startup nomount
or 
SQL> startup nomount pfile="$ORACLE_HOME/dbs/init<dbname>.ora"

Once after the database started in nomount stage we can execute create database command as below

SQL> CREATE DATABASE <database_name>

USER SYS IDENTIFIED BY <password>
USER SYSTEM IDENTIFIED BY <password>
MAXLOGFILES 5 
MAXLOGMEMBERS 5 
MAXDATAFILES 100 
LOGFILE 
GROUP 1 '/u01/app/oracle/oradata/container/database_name/redo1.log' SIZE 50M, 
GROUP 2 '/u01/app/oracle/oradata/container/database_name/redo2.log' SIZE 50M, 
GROUP 3 '/u01/app/oracle/oradata/container/database_name/redo3.log' SIZE 50M 
DATAFILE 
'/u01/app/oracle/oradata/container/database_name/system.dbf' SIZE 500m, 
'/u01/app/oracle/oradata/container/database_name/user01.dbf' SIZE 500m 
SYSAUX DATAFILE '/u01/app/oracle/oradata/container/database_name/sysaux.dbf' SIZE 500m 
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/oradata/container/database_name/undotbs1.dbf' SIZE 1000m 
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/container/database_name','/u01/app/oracle/oradata/container/database_name/pdbseed/')
CHARACTER SET WE8MSWIN1252

Once after the execution of the above lines successfully,
Execute the below three plsql blocks one after the another

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/sqlplus/admin/pupbld.sql

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Drop Database:

~> Now, set the environment like ORACLE_SID, ORACLE_HOME, ORACLE_BASE for the database you are going to drop.

~>Connect to RMAN as target database

RMAN> CONNECT TARGET SYS@dbname

target database Password: password

connected to target database: database_name (DBID=39525561)

RMAN> STARTUP FORCE MOUNT

RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;