Saturday 25 February 2017

Manual CDB-PDB database creation steps for oracle 12c

Today I’ll show you how we can create a CDB(With PDB-pdb$seed) database without DBCA(Database Configuration Assistant).

Environment Details:

Database Server OS: Cents OS 6.7 (64 Bit)
Database Software Version: Oracle 12c (12.1.0.2) 64 Bit Enterprise version

Assumption: Oracle database software 12.1.0.2 is installed on the database server with required read/write directory permission to oracle user


Steps:

1)      Add below line in .bash_profile of oracle database server Linux user

export TMPDIR=/tmp
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin/:$HOME/bin:$PATH


2) Make Directories for datafiles, control file, audit_file_dest and fast_recovery area

 $ mkdir –p /u01/app/oracle/fast_recovery_area/orcl
 $ mkdir –p /u01/app/oracle/admin/orcl/adump
 $ mkdir –p  /u01/app/oracle/oradata/orcl/pdbseed 
 $ mkdir –p /u01/app/oracle/oradata/orcl/pdborcl/

3)  Make init.ora file in “/u01/app/oracle/product/12c/db_1/dbs” path with below parameters

Example of init.ora file :

db_block_size=8192
open_cursors=300
db_domain=""
db_name="orcl"
control_files=("/u01/app/oracle/oradata/orcl/control01.ctl", "/u01/app/oracle/fast_recovery_area/orcl/control02.ctl")
db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size=4560m
compatible=12.1.0.2.0
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
memory_target=720m
local_listener=LISTENER
processes=300
audit_file_dest="/u01/app/oracle/admin/orcl/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"
undo_tablespace=UNDOTBS1

Note: “enable_pluggable_database=true” is the main parameter through that we can create CDB database 

4) Create listener.ora file in “/u01/app/oracle/product/12c/db_1/network/admin/” location with below parameters

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <OS_HOSTNAME>)(PORT = 1521))))

Note: update <OS_HOSTNAME> with database server OS hostname

5) Start database in no mount mode with the help of  pfile 

$ sqlplus / as sysdba

SQL > startup nomount pfile=’/u01/app/oracle/product/12c/db_1/dbs/init.ora’;

ORACLE instance started.

Total System Global Area  754974720 bytes
Fixed Size                  2928968 bytes
Variable Size             524291768 bytes
Database Buffers          222298112 bytes
Redo Buffers                5455872 bytes

6) Create database cdb database with pdb$seed template with below command

SQL > CREATE DATABASE "orcl"
            MAXINSTANCES 8
            MAXLOGHISTORY 1 
            MAXLOGFILES 16 
            MAXLOGMEMBERS 3 
            MAXDATAFILES 1024
            DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 1024M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL
            SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 1024M REUSE AUTOEXTEND ON
            SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON
            SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u01/app/oracle/oradata/orcl/undotbs01.dbf' SIZE 200M REUSE  AUTOEXTEND ON
            CHARACTER SET AL32UTF8
            NATIONAL CHARACTER SET AL16UTF16
             LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 50M, 
             GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 50M,
             GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 50M
             USER SYS IDENTIFIED BY pdbadmin USER SYSTEM IDENTIFIED BY welcome
              enable pluggable database
              seed file_name_convert=('/u01/app/oracle/oradata/orcl/system01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf',         '/u01/app/oracle/oradata/orcl/sysaux01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/temp01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/temp01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf'); 

Note: “enable pluggable database” is required command to create CDB database in which we can create PDB database.

7) Your database is up and running now . now rRun below script from cdb database

$ Sqlplus / as sysdba

SQL > @$ORACLE_HOME/rdbms/admin/catalog.sql;
SQL> @$ORACLE_HOME/rdbms/admin/catblock.sql;
SQL>@$ORACLE_HOME/ctx/admin/catctx.sql <password> sysaux temp NOLOCK;
SQL >@$ORACLE_HOME/rdbms/admin/catproc.sql;
SQL > @$ORACLE_HOME/rdbms/admin/catoctk.sql;
SQL >@$ORACLE_HOME/rdbms/admin/owminst.plb;
SQL >@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catcdb.sql

NOTE: update ctxsys user password instead on <password> 


8)  Make Pluggable database from pdb$seed database template with the help of below command: 

Login with cdb database 

$ sqlplus / as sysdba

SQL > CREATE PLUGGABLE DATABASE pdborcl ADMIN USER pdbadmin IDENTIFIED BY welcome FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/pdborcl/');


No comments:

Post a Comment