Oracle 12c implements manual database creation instead of CDB and CDB creation.

Oracle 12c implements manual database creation instead of CDB and CDB creation.

Preface

I believe everyone knows that for Oracle DATABASE creation, apart from dbca (GUI interface), Oracle also supports manual DATABASE creation, that is, using the CREATE DATABASE statement to CREATE a DATABASE. One advantage of using this statement for DBCA is that you can create a database from the script.

In Oracle version 12c, non-CDB databases and CDB container databases before 12c are supported. Therefore, the creation method is slightly different. This document also describes two methods for creating different databases. I won't talk much about it below. Let's take a look at the detailed introduction.

1. manually create a non-CDB database in 12c

Step 1: Specify the instance id (SID)

The environment variables of ORACLE_SID are used to distinguish other Oracle Database instances that may be created later and run on the same host computer at the same time.

$ export ORACLE_SID=nocdb$ export ORACLE_UNQNAME=nocdb

Step 2: Make sure to set the required environment variables

Depending on your platform, before starting SQL * Plus (as required by subsequent steps), you may have to set environment variables or at least verify that they are correctly set. Check the currently set environment variables as follows. If not set, use the export command.

$ env|grep ORAORACLE_UNQNAME=nocdbORACLE_SID=nocdbORACLE_BASE=/app/oracle/ora12cORACLE_HOSTNAME=ydq05.ydq.comORACLE_HOME=/app/oracle/ora12c/db_1

Step 3: select the database administrator authentication method

You must perform authentication and grant the corresponding system permissions to create a database.

In general, the Administrator with the required permissions performs authentication in the following ways:

Use password files

Operating system authentication (operating system authentication is used in this demonstration)

$ id oracleuid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

Step 4: Create an initialization parameter file

When an Oracle instance is started, it reads an initialization parameter file. This file can be a text file and can be created and modified using a text editor or binary file. This file is created and dynamically modified by the database. The preferred binary file is the server parameter file. In this step, you will create a text initialization parameter file. In the subsequent steps, you can create a server parameter file from a text file.

$ Mkdir-pv/app/oracle/ora12c/oradata/nocdb # create a directory for storing data files $ vi $ ORACLE_HOME/dbs/initnocdb. oraDB_NAME = nocdbCONTROL_FILES = '/app/oracle/ora12c/oradata/nocdb/contorl01.ctl' MEMORY _ TARGET = 380 m

Step 5: (Windows only) Create an instance

On Windows, you must manually create an instance if it does not exist. The ORADIM command creates an Oracle database instance by creating a new Windows service.

oradim -NEW -SID sid -STARTMODE MANUAL -PFILE file

Step 6: connect to the instance

Start SQL * Plus and connect to the Oracle database instance SYSDBA with administrative permissions.

$ Sqlplus/as sysdba -- the demo environment is SQL * Plus: Release 12.1.0.1.0 Production on Wed May 31 10:44:41 12.1 Copyright (c) 2017 3, Oracle. all rights reserved. connected to an idle instance. SQL>

Step 7: Create a server parameter file

The server parameter file allows you to use the alter system command to change the initialization parameters and retain the changes when the database is shut down and started. You can create a server parameter file from the edited text initialization file.

SQL> create spfile from pfile='/app/oracle/ora12c/db_1/dbs/initnocdb.ora';

Step 8: Start the instance

Start the instance without installing the database.

SQL> startup nomount;ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size     2296576 bytesVariable Size    641729792 bytesDatabase Buffers   419430400 bytesRedo Buffers    5480448 bytes

Step 9: Issue the create database statement

To create a new database, useCREATE DATABASEStatement.

SQL> CREATE DATABASE nocdb USER SYS IDENTIFIED BY pass USER SYSTEM IDENTIFIED BY pass LOGFILE GROUP 1 ('/app/oracle/ora12c/oradata/nocdb/redo01a.log','/app/oracle/ora12c/oradata/nocdb/redo01b.log') SIZE 100M BLOCKSIZE 512,   GROUP 2 ('/app/oracle/ora12c/oradata/nocdb/redo02a.log','/app/oracle/ora12c/oradata/nocdb/redo02b.log') SIZE 100M BLOCKSIZE 512 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 1024 CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/app/oracle/ora12c/oradata/nocdb/system01.dbf' SIZE 700M SYSAUX DATAFILE '/app/oracle/ora12c/oradata/nocdb/sysaux01.dbf' SIZE 550M  DEFAULT TABLESPACE users  DATAFILE '/app/oracle/ora12c/oradata/nocdb/users01.dbf' SIZE 500M  DEFAULT TEMPORARY TABLESPACE tempts1  TEMPFILE '/app/oracle/ora12c/oradata/nocdb/temp01.dbf' SIZE 20M  UNDO TABLESPACE undotbs1  DATAFILE '/app/oracle/ora12c/oradata/nocdb/undotbs01.dbf' SIZE 200M USER_DATA TABLESPACE usertbs  DATAFILE '/app/oracle/ora12c/oradata/nocdb/usertbs01.dbf' SIZE 200M;

Step 10: create other tablespaces

To make the database work, you must create other tablespaces for the application data.

SQL> CREATE TABLESPACE apps_tbs LOGGING   DATAFILE '/app/oracle/ora12c/oradata/nocdb/apps01.dbf'   SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED   EXTENT MANAGEMENT LOCAL;

Step 11: run the script to build the data dictionary View

Run the scripts necessary to build a data dictionary view, synonym, and PL/SQL package, and support the normal operation of SQL * Plus.

Run the following script with sysdba permission

SQL> @? /Rdbms/admin/catalog. SQL -- database dictionary, dynamic view creation, and other SQL> @? /Rdbms/admin/catproc. SQL -- PL/SQL stored Process Code SQL> @? /Rdbms/admin/utlrp. SQL -- compile

Use the SYSTEM user to execute the following script

SQL> @? /Sqlplus/admin/pupbld. SQL -- SQL * Plus -- Verification Result SQL> SELECT name, 2 DECODE (cdb, 3 'yes', 'multitenant Option enabled', 4' Regular 12c Database :') 5 "Multitenant Option", 6 open_mode, 7 con_id 8 FROM v $ database; NAME Multitenant Option OPEN_MODE CON_ID --------- ------------------------ ------------------ ------------ NOCDB Regular 12c Database: read write 0

Step 12: (optional) run the script to install other options (omitted here)

You may need to run other scripts. The script you run is determined by the features and options you choose to use or install.

Step 13: Back up the database to the database (omitted here)

Complete backup to ensure that you have a complete set of files, which can be recovered in case of a media failure.

Step 14: (optional) Enable Automatic instance startup (omitted here)

You may want to configure the Oracle instance to automatically start when its host restarts.

2. manually create a CDB database in 12c

1. CDB creation instructions

UseCREATE DATABASESQLStatement to create a CDB is very similar to creating a non-CDB. UseCREATE DATABASESQLWhen creating a CDB statement, you must enable PDB and specify the name and location of the root file and the seed file.

To CREATE a CDB, the create database statement must containENABLE PLUGGABLE DATABASEClause. When this clause is included, the statement uses the root and seed to create CDB. If not specifiedENABLE PLUGGABLE DATABASEThe newly created database is non-CDB. This statement does not create roots and seeds, and non-CDB will never contain PDB.

The main differences are as follows:

1. When creating a CDB database, specifyENABLE PLUGGABLE DATABASEClause

2. the name and location of the root file and seed file should be specified in the CDB database.

  CREATE DATABASEStatement. You must specify the name and location of the root file and the seed file.

  CREATE DATABASEAfter the statement is completed, you can use the seeds and their files to create a new PDB. The seed cannot be modified after it is created.

You can specify the name and location of the seed file in one of the following ways:

Zookeeper zookeeperseed FILE_NAME_CONVERTClause

SuchSEED FILE_NAME_CONVERT = (‘/app/oracle/ora12c/oradata/cdb1/','/app/oracle/ora12c/oradata/pdbseed/')Converts files in the cdb1 directory to the pdbseed directory.

Oracle-based file hosting (OMF)

Zookeeper zookeeperPDB_FILE_NAME_CONVERTInitialization parameters

3. different attributes can be created for the PDB seed database.

The attributes of the data files in the tablespace of the zookeeper root SYSTEM and SYSAUX may not be suitable for seeds. In this case, you can use the clause to specify different properties for the data file of the seed tablespace_datafile. Use these clauses to specify the attributes in the SYSTEM and SYSAUX tablespaces of all data files. The value inherited from the root is used for any attribute that does not provide its value.

Example

DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf 'size 325 m reusesysaux datafile'/u01/app/oracle/oradata/newcdb/sysaux01.dbf' SIZE 325 m reuse -- below the seed Section of is different from the preceding root container configuration, -- for example, the size of the system data file is 125 MB, while that of the root container is 352 MB -- automatic expansion is enabled in seed, sysaux also uses some properties different from those of the root container-for attributes not explicitly specified in seed, it inherits the data file attribute seedsystem datafiles size 125 m autoextend on next 10 m maxsize unlimitedsysaux datafiles size 100 M

2. Create a cdb Database

For detailed steps, see create a non-cdb database manually under 12c. The following sections omit the detailed descriptions of each step and list the differences later.

-- Set the environment variable $ export ORACLE_SID = cdb1 $ export ORACLE_UNQNAME = cdb1 -- create the corresponding directory $ mkdir-pv/app/oracle/ora12c/oradata/cdb1 $ mkdir-pv/app/oracle /ora12c/oradata/pdbseed -- generate the parameter file $ vi $ ORACLE_HOME/dbs/export _ NAME = cdb1CONTROL_FILES = '/app/oracle/ora12c/oradata/cdb1/contorl01.ctl 'memory _ TARGET = 380mENABLE_PLUGGABLE_DATABASE = true -- start database creation $ sqlplus/as sysdbaSQL> create spfile from pfile = '$ ORACLE_HOME/dbs/initcd B1.ora '; SQL> startup nomount; SQL> CREATE DATABASE cdb1 USER SYS IDENTIFIED BY pass USER SYSTEM IDENTIFIED BY pass LOGFILE GROUP 1 ('/app/oracle/ora12c/oradata/cdb1/redo01a. log', '/app/oracle/ora12c/oradata/cdb1/redo01b. log ') SIZE 100 m blocksize 512, GROUP 2 ('/app/oracle/ora12c/oradata/cdb1/redo02a. log', '/app/oracle/ora12c/oradata/cdb1/redo02b. log') SIZE 100 m blocksize 512 MAXLOGHISTORY 1 MAXLOGFIL ES 16 MAXLOGMEMBERS 3 MAXDATAFILES 1024 character set AL32UTF8 national character set extends extent management local datafile '/app/oracle/ora12c/oradata/cdb1/system01.dbf 'size 700 m sysaux datafile'/app/ oracle/ora12c/oradata/cdb1/sysaux01.dbf 'size 550 m default tablespace deftbs datafile'/app/oracle/ora12c/oradata/cdb1/comment 'size 500 m default temportablespace tempts1 tempfile'/ Pp/oracle/ora12c/oradata/cdb1/temp01.dbf 'size 20 m undo tablespace undotbs1 datafile'/app/oracle/ora12c/oradata/cdb1/comment 'size 200 M ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT = ('/app/oracle/ora12c/oradata/cdb1 /', '/app/oracle/ora12c/oradata/pdbseed/') system datafiles size 125 m autoextend on next 10 m maxsize unlimited sysaux datafiles size 100 M USER_DATA TABLESPACE usertbs DATAFI LE '/app/oracle/ora12c/oradata/pdbseed/usertbs01.dbf' SIZE 200 M; -- fill in the data dictionary. if the version is 12cR2, run catcdb directly. SQL replaces the following script-execute the following script SQL with sysdba permissions> @? /Rdbms/admin/catalog. SQL -- database dictionary, dynamic view creation, and other SQL> @? /Rdbms/admin/catproc. SQL -- PL/SQL stored Process Code SQL> @? /Rdbms/admin/utlrp. SQL -- compile -- use the SYSTEM user to execute the following script SQL> @? /Sqlplus/admin/pupbld. SQL # SQL * Plus related SQL> select 'lesham' Author, 'HTTP: // blog.csdn.net/leshami' Blog, 2 '000000' QQ from dual; author blog qq ------- ------------------------------- Leshami http://blog.csdn.net/leshami 645746311 -- verification results SQL> SELECT name, 2 DECODE (cdb, 3 'yes', 'multitenant Option enabled', 4 'regular 12c Database: ') 5 "Multitenant Option", 6 open_mode, 7 con_id 8 FROM v $ database; NAME Multitenant Option OPEN_MODE CON_ID --------- ------------------------ ---------------------- CDB1 Multitenant Option enabled read write 0

Iii. More references

Oracle 12c multi-tenant architecture and advantages and disadvantages

Http://docs.oracle.com/database/122/ADMIN/creating-and-configuring-an-oracle-database.htm

Http://docs.oracle.com/database/122/ADMIN/creating-and-configuring-a-cdb.htm

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.