This post is also available in:
Português
In the previous article, I showed how you can build a Gold Image of your Oracle Database 21c in DBaaS to play and study the new features in your own VMs (Playing with new Oracle Database 21c in your VirtualBox).
Now that you have successfully deployed the binaries, the next step is to create a database.
Generally speaking, you would have 3 options to go..
- Create a RMAN backup on DBaaS and restore it on your VM.
- Create the Database using catalog / catproc approach.
- Create with DBCA.
Option 3 is the easiest one and would be my first choice. However, as you may have seen in the previous article, the DBaaS Gold Image does not contain the General Purpose templates. Only a single one named "seed_db.dbc" which is bases on ASM and cannot be used in this lab scenario.
So, we are only left with option 1 or 2. I will go with option 2 as I want a brand new and clean one.
The overall steps are:
- Create the parameter file.
- Create the database using CREATE DATABASE syntax.
- Run catalog & catproc on CDB e PDBS.
Create the SPFILE
cat > initdb21c.ora <<'EOF' db_name='db21c' memory_target=2G processes = 150 db_block_size=8192 db_domain='' db_create_file_dest='/u01/app/oracle/oradata' db_create_online_log_dest_1='/u01/app/oracle/oradata' db_recovery_file_dest='/u01/app/oracle/fra' db_recovery_file_dest_size=2G diagnostic_dest='/u01/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=db21cXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' control_files = '/u01/app/oracle/oradata/control01.ctl' enable_pluggable_database=TRUE encrypt_new_tablespaces=DDL wallet_root='/u01/app/oracle/wallet' tde_configuration="KEYSTORE_CONFIGURATION=FILE" EOF
Prepare the Instance
First create the folders for oradata, fra and DB wallet:
[oracle@lab21c ~]$ mkdir /u01/app/oracle/{oradata,fra,wallet}
[oracle@lab21c ~]$ mkdir /u01/app/oracle/wallet/tde
Now start the DB in nomount:
[oracle@lab21c dbs]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 14 12:15:00 2020 Version 21.1.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup nomount; ORACLE instance started. Total System Global Area 2147480016 bytes Fixed Size 9687504 bytes Variable Size 1207959552 bytes Database Buffers 922746880 bytes Redo Buffers 7086080 bytes SQL>
Create your Wallet
It's usually recommended to have the Oracle Wallet properly configured before creating the database
SQL> administer key management create keystore '/u01/app/oracle/wallet/tde' identified by welcome1; keystore altered. SQL> set lines 1000 pages 1000 SQL> col WRL_PARAMETER for a30 SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS CON_ID -------------------- ------------------------------ ------------------------------ ---------- FILE /u01/app/oracle/wallet/tde/ CLOSED 1 SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/wallet/tde' identified by welcome1; keystore altered. SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS CON_ID -------------------- ------------------------------ ------------------------------ ---------- FILE /u01/app/oracle/wallet/tde/ OPEN_NO_MASTER_KEY 1
Create the Database
I will run the following command to create my 21c database control file:
CREATE DATABASE db21c USER SYS IDENTIFIED BY Oracle11__ USER SYSTEM IDENTIFIED BY Oracle11__ LOGFILE GROUP 1 SIZE 100M BLOCKSIZE 512, GROUP 2 SIZE 100M BLOCKSIZE 512, GROUP 3 SIZE 100M BLOCKSIZE 512 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 1024 CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED SYSAUX DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED DEFAULT TABLESPACE users DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED UNDO TABLESPACE undotbs1 DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED USER_DATA TABLESPACE usertbs DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ENABLE PLUGGABLE DATABASE SEED SYSTEM DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED SYSAUX DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ;
SQL> @create_database.sql Database created. SQL>
Create the Catalog
Finally, I will create and setup my catalog.
First start the DB in upgrade mode:
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 2147480016 bytes
Fixed Size 9687504 bytes
Variable Size 1207959552 bytes
Database Buffers 922746880 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
SQL>
Now call the catctl:
[oracle@lab21c ~]$ mkdir /home/oracle/log [oracle@lab21c ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl \ -d $ORACLE_HOME/rdbms/admin \ -n 2 \ -c 'CDB$ROOT PDB$SEED' \ -l /home/oracle/log \ catpcat.sql
And after some time:
Argument list for [/u01/app/oracle/product/21.0.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = CDB$ROOT PDB$SEED
Do not run in C = 0
Input Directory d = /u01/app/oracle/product/21.0.0.0/dbhome_1/rdbms/admin
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle/log
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 2
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
Classic Upgrade t = 0
RO User Tablespaces T = 0
Upgrade PDBs in Upgrade mode x = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [21.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_21.0.0.0.0_LINUX.X64_201030]
...
...
...
Grand Total Time: 1527s [PDB$SEED]
Time: 1397s For CDB$ROOT
Time: 1534s For PDB(s)
Grand Total Time: 2931s
Now that the process is completed. Restart the database to remove the upgrade mode and recompile any invalid objects:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2147480016 bytes
Fixed Size 9687504 bytes
Variable Size 1291845632 bytes
Database Buffers 838860800 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> select count(*) from cdb_objects where status='INVALID';
COUNT(*)
----------
0
Finally, you can create your first PDB from Seed:
SQL> CREATE PLUGGABLE DATABASE pdb01 ADMIN USER pdb_adm IDENTIFIED BY Password1;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL>
That's it. Now you can create your labs and play with the new 21c features.
Have you enjoyed? Please leave a comment or give a 👍!
