Before we look at the SQL statement to create the database, remember all of the scripts and processes that were done as part of the DBCA. So, there are steps to follow to create the database manually using a SQL statement. These of course can be made into a script based on the scripts that were provided. Before running the CREATE DATABASE statement, work through these steps:
1. Set the OS variables.
2. Configure the initialization file.
3. Create the required directories.
4. Create the database.
5. Create a data dictionary.
The sample init.ora file from the software installation should be the starting point for the new release of the database. There are parameters that are deprecated and parameters that might not need to be set or have default values that have improved,
so you should start with the sample init.ora file for that release. Later we will look at managing parameters so many of these values change over time with the database.
Here is an init.ora example:
The file needs to be named properly init<SID>.ora (pfile), and before we run the create database, it should be used to create an spfile, spfile<SID>.ora. The spfile is used to modify the contents with an ALTER SYSTEM statement, and more dynamic parameters can be set without downtime.
Set the environment variables, create the init.ora file, and create the directories for the data files and log files. Verify that the correct group and user own these directories.
Now we are ready to run the CREATE DATABASE statement. The STARTUP NOMOUNT statement reads the initialization file and instantiates the background processes and memory areas used by Oracle. At this point you have an Oracle instance, but not yet a database.
Here is an example:
You can also use ASM with a CREATE DATABASE statement instead of file system directories. This would just require that you have the +ASM instance running and available. The database files will be created in the ASM diskgroup, and the naming is simplified because you don’t have to create the directories and include them specifically in the statements. You can also use Oracle Managed Files so that the Oracle Database manages where the files are created based on a destination parameter. This also simplifies the statements and reduces errors in typing or passing in the full paths of the
directories. To use Oracle Managed Files, set DB_CREATE_FILE_DEST to the directory for the data files in the init.ora file. This can be used for both ASM and file system directories.
Set the parameter in init.ora:
or using Oracle ASM
So, why didn’t we use the simplified example? Well, there is now the understanding of what is happening on the back end when defining where the files are being placed and where all of the pieces fit for the database. Oracle Managed Files also provides an easier way to meet the standard by setting the parameter to tell the database the standard to use and keep it consistent.
If there were any issues creating the database, they will be tracked in the alert log.
After the database is created in SQL*Plus, you can instantiate the data dictionary by running scripts that were created when you install the Oracle binaries. These scripts
must be run as SYS and run in the container database (CDB). The question mark (?) is a SQL*Plus variable for ORACLE_HOME.
You will be prompted for parameter 1, which is the temporary log file directory, and parameter 2, which is the log filename for the output of these scripts.
The database has now been created and the data dictionary implemented. The log file should be reviewed for any issues or failures. If there are any issues, the best place for researching it and troubleshooting would be MyOracleSupport (MOS) as these are scripts coming directly from Oracle for this release. Issues such as running out of space or faulty permissions are ones that you should address, but if the planning and pre-steps were completed ahead of creation, these types of issues should not be showing up here.