We finally started creating some Databases once we made sure Marvin stopped whining about why the automatic doors of the spaceship had to be so cheerful.
You can read more about creating DBs on DBCA: Software + DB Creation, about Marvin and the automatic doors.. lets just leave there for now..
Sometimes however, what you want is to personalize a new DB you need to create or even clone a DB you already have in your cluster.
That is what we are going to explore today, some personalization and cloning using DBCA templates.
Create a template from a running Database
Sometimes you want to create a new Database keeping the same configuration you have in a DB you have already in the system, but that doesn't mean you want to close its content.
For that, we can use the DBCA argument "createTemplateFromDB"
To make sure we are not using a "default" Database, lets modify the Database and add some data (this will helpful later for another part of the guide) into our already existing database "db112"
## Parameters changed ##
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 150
db_recovery_file_dest_size big integer 4407M
SQL> alter system set processes = 200 scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_size=10G scope=BOTH SID='*';
System altered.
(After DB Restarted):
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 200
db_recovery_file_dest_size big integer 10G
## Create Schema and Table TEST##
sys@db1121>SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;
TABLESPACE_NAME STATUS CONTENTS
----------------- -------- -------------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
UNDOTBS2 ONLINE UNDO
TEST ONLINE PERMANENT
7 rows selected.
sys@db1121>
sys@db1121> CREATE USER TEST
IDENTIFIED BY Welcome1
DEFAULT TABLESPACE TEST
TEMPORARY TABLESPACE TEMP
QUOTA 200M on TEST; 2 3 4 5
User created.
sys@db1121> GRANT CONNECT TO TEST;
Grant succeeded.
sys@db1121> grant create session, create procedure,create table to TEST;
Grant succeeded.
sys@db1121> connect TEST
Enter password:
Connected.
sys@db1121> create table TEST
(
ID VARCHAR2(4 BYTE) NOT NULL primary key,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
)
/ 2 3 4 5 6 7 8 9 10 11 12
Table created.
sys@db1121> BEGIN
FOR v_LoopCounter IN 1..50 LOOP
INSERT INTO TEST (id)
VALUES (v_LoopCounter);
END LOOP;
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
sys@db1121> commit;
Commit complete.
sys@db1121> select count(*) from test;
COUNT(*)
----------
50
sys@db1121>
Lets create the Template from the Database "db112":
## Command Example ##
dbca -silent \
-createTemplateFromDB \
-sourceSID db112 \
-templateName db112_template \
-sysDBAUserName SYS \
-sysDBAPassword Welcome1
## Output ##
[oracle@rac1-node1 templates]$ time dbca -silent -createTemplateFromDB \
> -sourceSID db112 \
> -templateName db112_template \
> -sysDBAUserName SYS \
> -sysDBAPassword Welcome1
Creating a template from the database
10% complete
20% complete
30% complete
40% complete
50% complete
60% complete
70% complete
80% complete
90% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2019-01-06_07-14-58-PM" for further details.
real 0m34.074s
user 0m9.702s
sys 0m0.589s
[oracle@rac1-node1 templates]$
You can find our new Template below:
[oracle@rac1-node1 dbhome_1]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
[root@rac1-node1 ~]# locate db112_template
/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/db112_template.dbt
[oracle@rac1-node1 admin]$ ls -lrth /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/
total 610M
-rw-r--r-- 1 oracle oinstall 12K May 1 2013 New_Database.dbt
-rw-r--r-- 1 oracle oinstall 5.0K Aug 24 2013 Data_Warehouse.dbc
-rw-r--r-- 1 oracle oinstall 4.9K Aug 24 2013 General_Purpose.dbc
-rwxr-xr-x 1 oracle oinstall 1.5M Aug 27 2013 example.dmp
-rwxr-xr-x 1 oracle oinstall 21M Aug 27 2013 example01.dfb
-rwxr-xr-x 1 oracle oinstall 9.3M Aug 27 2013 Seed_Database.ctl
-rwxr-xr-x 1 oracle oinstall 263M Aug 27 2013 Seed_Database.dfb
-rw-r----- 1 oracle oinstall 17K Jan 6 19:15 db112_template.dbt
-rw-r----- 1 oracle oinstall 466 Jan 6 20:11 LOG_cat_owb.TXT
-rw-r----- 1 oracle oinstall 297M Jan 6 20:20 db112_clone.dfb
-rw-r----- 1 oracle oinstall 18M Jan 6 20:20 db112_clone.ctl
-rw-r----- 1 oracle oinstall 6.0K Jan 6 20:21 db112_clone.dbc
[oracle@rac1-node1 admin]$
Lets now create a new Database using that new template and confirm is created with "db112" configuration. The new Database will be called "test112" (I know.. the names are super originals, right?)
## Command Example ##
dbca \
-silent \
-createDatabase \
-templateName db112_template.dbt \
-sid test112 \
-gdbName test112 \
-SysPassword Welcome1 \
-SystemPassword Welcome1 \
-emConfiguration NONE \
-storageType ASM \
-diskGroupName DATA1 \
-recoveryGroupName RECO \
-nodelist rac1-node1,rac1-node2 \
-initParams db_unique_name=test112
## Output ##
[oracle@rac1-node1 templates]$ time dbca -silent \
> -createDatabase \
> -templateName db112_template.dbt \
> -sid test112 \
> -gdbName test112 \
> -SysPassword Welcome1 \
> -SystemPassword Welcome1 \
> -emConfiguration NONE \
> -storageType ASM \
> -diskGroupName DATA1 \
> -recoveryGroupName RECO \
> -nodelist rac1-node1,rac1-node2 \
> -initParams db_unique_name=test112
Creating and starting Oracle instance
1% complete
3% complete
Creating database files
4% complete
6% complete
Creating data dictionary views
7% complete
8% complete
9% complete
10% complete
11% complete
12% complete
13% complete
15% complete
16% complete
17% complete
18% complete
Adding Oracle JVM
23% complete
28% complete
33% complete
35% complete
Adding Oracle Text
36% complete
38% complete
Adding Oracle XML DB
40% complete
41% complete
42% complete
45% complete
Adding Oracle Multimedia
46% complete
47% complete
56% complete
Adding Oracle OLAP
57% complete
58% complete
59% complete
Adding Oracle Spatial
60% complete
61% complete
62% complete
66% complete
Adding Enterprise Manager Repository
68% complete
70% complete
Adding Oracle Application Express
73% complete
76% complete
Adding Oracle Warehouse Builder
80% complete
83% complete
Creating cluster database views
84% complete
90% complete
Completing Database Creation
91% complete
92% complete
95% complete
98% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/test112/test1120.log" for further details.
real 55m50.522s
user 1m37.506s
sys 0m8.181s
[oracle@rac1-node1 templates]$
Lets check the Database created and how "processes" / "db_recovery_file_dest_size" parameters are the same we did set in "db112"
[oracle@rac1-node1 ~]$ srvctl status database -d test112 -v
Instance test1121 is running on node rac1-node1. Instance status: Open,HOME=/u01/app/oracle/product/11.2.0/dbhome_1.
Instance test1122 is running on node rac1-node2. Instance status: Open,HOME=/u01/app/oracle/product/11.2.0/dbhome_1.
[oracle@rac1-node1 ~]$
[oracle@rac1-node1 dbs]$ srvctl config database -d test112
Database unique name: test112
Database name: test112
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA1/test112/spfiletest112.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test112
Database instances: test1121,test1122
Disk Groups: DATA1,RECO
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@rac1-node1 dbs]$
DB_NAME INSTANCE_NAME HOST_NAME STARTUP DATABASE_ROLE OPEN_MODE STATUS
--------- ---------------- ----------------------------------- ----------------------------- ---------------- -------------------- ------------
TEST112 test1121 rac1-node1.raclab.local 06-JAN-2019 16:31:45 PRIMARY READ WRITE OPEN
TEST112 test1122 rac1-node2.raclab.local 06-JAN-2019 16:31:45 PRIMARY READ WRITE OPEN
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 200
db_recovery_file_dest_size big integer 10G
Ok, lets be more accurate and find the real differences here..
Lets export the spfile and compare them side by side:
SQL> create pfile='/tmp/test112.ora' from spfile;
[[.....]]
SQL> create pfile='/tmp/db112.ora' from spfile;
The Below table is the result of comparing both files, so what is not there, is already the same on both systems:
[oracle@rac1-node1 ~]$ diff /tmp/db112.ora /tmp/test112.ora
*.control_files='+DATA1/db112/controlfile/current.261.994939157','+DATA1/db112/controlfile/current.260.994939157'
*.db_name='db112'
*.db_recovery_file_dest='+DATA1'
===============================================
*.control_files='+DATA1/test112/controlfile/current.292.996853187','+RECO/test112/controlfile/current.321.996853187'
*.db_name='test112'
*.db_recovery_file_dest='+RECO'
*.db_unique_name='test112'
Notice how the only real difference are the Diskgroups for the Controlfiles and Recovery Files Destination and db_unique_name, which were all specified during the creation of the Database test112.
So, even if we use a template, we can still personalize parameters using the different DBCA attributes.
Let's talk a bit more about personalization in the following example.
Create your Personal Template
When we create a DB using DBCA, we use template with the most general configurations items setup in order for the Database to start.
In case we want to create a Database and the default configuration is not enough for us, we can edit or create one of these templates (they are just xml files) and execute DBCA with it.
See below the Default templates for 11.2:
[oracle@rac1-node1 dbhome_1]$ cd $ORACLE_HOME/assistants/dbca/templates
[oracle@rac1-node1 templates]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates
[oracle@rac1-node1 templates]$ ls -lrth
total 295M
-rw-r--r-- 1 oracle oinstall 12K May 1 2013 New_Database.dbt
-rw-r--r-- 1 oracle oinstall 5.0K Aug 24 2013 Data_Warehouse.dbc
-rw-r--r-- 1 oracle oinstall 4.9K Aug 24 2013 General_Purpose.dbc
-rwxr-xr-x 1 oracle oinstall 1.5M Aug 27 2013 example.dmp
-rwxr-xr-x 1 oracle oinstall 21M Aug 27 2013 example01.dfb
-rwxr-xr-x 1 oracle oinstall 9.3M Aug 27 2013 Seed_Database.ctl
-rwxr-xr-x 1 oracle oinstall 263M Aug 27 2013 Seed_Database.dfb
[oracle@rac1-node1 templates]$
Lets create a new version of the default Template "New_Database.dbt" called "P42_Database.dbt" and modify (on this case we will set them to false) some of the components DBCA install when you create a DB like Spatial, OMS, JServer...:
P42_Database.dbt
<DatabaseTemplate name="P42 Database" description="" version="11.2.0.0.0">
<CommonAttributes>
<option name="OMS" value="false"/>
<option name="JSERVER" value="false"/>
<option name="SPATIAL" value="false"/>
<option name="IMEDIA" value="false"/>
<option name="ORACLE_TEXT" value="false">
<tablespace id="SYSAUX"/>
</option>
<option name="XDB_PROTOCOLS" value="true">
<tablespace id="SYSAUX"/>
</option>
<option name="CWMLITE" value="true">
<tablespace id="SYSAUX"/>
</option>
<option name="EM_REPOSITORY" value="false">
<tablespace id="SYSAUX"/>
</option>
<option name="SAMPLE_SCHEMA" value="false"/>
<option name="APEX" value="false"/>
<option name="OWB" value="false"/>
<option name="DV" value="false"/>
</CommonAttributes>
<Variables/>
<CustomScripts Execute="false"/>
<InitParamAttributes>
<InitParams>
<initParam name="db_block_size" value="8" unit="KB"/>
<initParam name="memory_target" value="250" unit="MB"/>
New_Database.dbt
<DatabaseTemplate name="New Database" description="" version="11.2.0.0.0">
<CommonAttributes>
<option name="OMS" value="true"/>
<option name="JSERVER" value="true"/>
<option name="SPATIAL" value="true"/>
<option name="IMEDIA" value="true"/>
<option name="ORACLE_TEXT" value="true">
<tablespace id="SYSAUX"/>
</option>
<option name="XDB_PROTOCOLS" value="true">
<tablespace id="SYSAUX"/>
</option>
<option name="CWMLITE" value="true">
<tablespace id="SYSAUX"/>
</option>
<option name="EM_REPOSITORY" value="true">
<tablespace id="SYSAUX"/>
</option>
<option name="SAMPLE_SCHEMA" value="false"/>
<option name="APEX" value="true"/>
<option name="OWB" value="true"/>
<option name="DV" value="false"/>
</CommonAttributes>
<Variables/>
<CustomScripts Execute="false"/>
<InitParamAttributes>
<InitParams>
<initParam name="db_block_size" value="8" unit="KB"/>
<initParam name="memory_target" value="250" unit="MB"/>
Now that we have our new Template without those components we can take it for a spin and we will see how none of those components are installed in the system:
[oracle@rac1-node1 templates]$ time dbca -silent \
> -createDatabase \
> -templateName P42_Database.dbt \
> -sid test \
> -gdbName test \
> -SysPassword Welcome1 \
> -SystemPassword Welcome1 \
> -emConfiguration NONE \
> -storageType ASM \
> -diskGroupName DATA1 \
> -recoveryGroupName RECO \
> -nodelist rac1-node1,rac1-node2 \
> -initParams db_unique_name=test
Creating and starting Oracle instance
1% complete
2% complete
3% complete
7% complete
Creating database files
8% complete
9% complete
15% complete
Creating data dictionary views
17% complete
20% complete
22% complete
23% complete
24% complete
25% complete
26% complete
27% complete
28% complete
29% complete
34% complete
36% complete
38% complete
40% complete
Adding Oracle XML DB
43% complete
44% complete
45% complete
46% complete
47% complete
48% complete
56% complete
Adding Oracle OLAP
57% complete
58% complete
59% complete
60% complete
61% complete
63% complete
Creating cluster database views
65% complete
79% complete
Completing Database Creation
81% complete
83% complete
90% complete
97% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/test/test2.log" for further details.
real 19m7.625s
user 0m32.105s
sys 0m3.921s
[oracle@rac1-node1 templates]$
Just in case you are not sure or you weren't paying any attention, lets check DB components:
sys@test1>select comp_id, version, status from dba_registry;
COMP_ID VERSION STATUS
------------------------------ ------------------------------ -----------
AMD 11.2.0.4.0 VALID
XDB 11.2.0.4.0 VALID
OWM 11.2.0.4.0 VALID
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID
APS 11.2.0.4.0 VALID
XOQ 11.2.0.4.0 VALID
RAC 11.2.0.4.0 VALID
8 rows selected.
I chose to modify some components to make the DB "lighter".. but of course you can just change the template and create the DB with all your specific requirements, like a different db_block_size, RedoLogSize.. etc...
If you don't see a parameter in your template and still want to add it, we can just add them to the section called "MiscParams" as we have below and make sure the new DB is created with those parameters set correctly.
On this case "characterSet" "nationalCharacterSet" were added to that section to make sure the DB was created with that parameter set
## DBCA Template ##
[.....]
<initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
<initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
<initParam name="db_recovery_file_dest" value="{ORACLE_BASE}/fast_recovery_area"/>
<initParam name="db_recovery_file_dest_size" value="" unit="MB"/>
</InitParams>
<MiscParams>
<databaseType>MULTIPURPOSE</databaseType>
<maxUserConn>20</maxUserConn>
<percentageMemTOSGA>40</percentageMemTOSGA>
<customSGA>false</customSGA>
<archiveLogMode>false</archiveLogMode>
<initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>
<characterSet>AL32UTF8</characterSet>
<nationalCharacterSet>UTF8</nationalCharacterSet>
</MiscParams>
<SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
</InitParamAttributes>
## DB created ##
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ---------------------------------------- ----------------------------------------
NLS_CHARACTERSET AL32UTF8 Character set
NLS_NCHAR_CHARACTERSET UTF8 NCHAR Character set
Clone a Database using DBCA
Sometimes you have that small DB and you want to actually clone it.. not only to create a similar DB as we did in the other examples, but you want to clone everything including the data on it
For this process, and if you have enough space in the system.. you can use "createCloneTemplate" option
DB restart
Be careful when you do this process. The source DB will be restarted
as mounted in order to create the Datafiles backup:
## DB Alert ##
Sun Jan 06 13:55:38 2019
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Sun Jan 06 13:55:38 2019
Stopping background process CJQ0
[.......]
ALTER DATABASE MOUNT
This instance was first to mount
Sun Jan 06 13:56:05 2019
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.21)(PORT=1521))' SCOPE=MEMORY SID='db1121';
ALTER SYSTEM SET remote_listener='rac1-scan:1521' SCOPE=MEMORY SID='db1121';
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='db1121';
NOTE: Loaded library: System
SUCCESS: diskgroup DATA1 was mounted
NOTE: dependency between database db112 and diskgroup resource ora.DATA1.dg is established
Successful mount of redo thread 1, with mount id 985562741
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Ok, lets create a clone template of the Database "db112" which has a Tablespace called TEST as additional to the "default" ones:
Create Clone Template
## Command ##
dbca \
-silent \
-createCloneTemplate \
-sourceSID db112 \
-templateName db112_clone.dbc \
-sysDBAUserName SYS \
-sysDBAPassword Welcome1
## Output ##
[oracle@rac1-node1 dbs]$ dbca -silent -createCloneTemplate \
> -sourceSID db112 \
> -templateName db112_clone.dbc \
> -sysDBAUserName SYS \
> -sysDBAPassword Welcome1
Gathering information from the source database
4% complete
8% complete
13% complete
17% complete
22% complete
Backup datafiles
28% complete
88% complete
Creating template file
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2019-01-06_01-55-25-PM" for further details.
[oracle@rac1-node1 dbs]$
Lets see the content of the new Template (db112_clone.dbc) and the Backup (db112_clone.dfb):
## Files Created ##
[oracle@rac1-node1 templates]$ ls -lrth
total 1.2G
[...]
-rw-r----- 1 oracle oinstall 299M Jan 6 13:58 db112_clone.dfb
-rw-r----- 1 oracle oinstall 18M Jan 6 13:58 db112_clone.ctl
-rw-r----- 1 oracle oinstall 6.0K Jan 6 13:58 db112_clone.dbc
[oracle@rac1-node1 templates]$
## Tablespace information from the Template ##
[oracle@rac1-node1 templates]$ grep -i Tablespace db112_clone.dbc
<Name id="1" Tablespace="SYSTEM" Contents="PERMANENT" Size="750" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system.256.994939031</Name>
<Name id="2" Tablespace="SYSAUX" Contents="PERMANENT" Size="720" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux.257.994939031</Name>
<Name id="3" Tablespace="UNDOTBS1" Contents="UNDO" Size="80" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs1.258.994939031</Name>
<Name id="4" Tablespace="USERS" Contents="PERMANENT" Size="5" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users.259.994939031</Name>
<Name id="5" Tablespace="UNDOTBS2" Contents="UNDO" Size="175" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs2.267.994939287</Name>
<Name id="6" Tablespace="TEST" Contents="PERMANENT" Size="100" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/test.282.996846429</Name>
<Name id="7" Tablespace="TEST" Contents="PERMANENT" Size="1024" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/test.281.996846441</Name>
<Name id="1" Tablespace="TEMP" Contents="TEMPORARY" Size="32">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp.266.994939173</Name>
[oracle@rac1-node1 templates]$
To use this template, we just need to make sure we specified the correct name (db112_clone.dbc) and we will have our new Database as a full clone of the source Database
## Command ##
dbca \
-silent \
-createDatabase \
-templateName db112_clone.dbc \
-gdbName clone112 \
-sid clone112 \
-SysPassword Welcome1 \
-SystemPassword Welcome1 \
-emConfiguration NONE \
-storageType ASM \
-nodelist rac1-node1,rac1-node2 \
-initParams db_unique_name=clone112
## Output ##
[oracle@rac1-node1 ~]$ dbca -silent \
> -createDatabase \
> -templateName db112_clone.dbc \
> -gdbName clone112 \
> -sid clone112 \
> -SysPassword Welcome1 \
> -SystemPassword Welcome1 \
> -emConfiguration NONE \
> -storageType ASM \
> -nodelist rac1-node1,rac1-node2 \
> -initParams db_unique_name=clone112
Copying database files
1% complete
3% complete
9% complete
15% complete
21% complete
27% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
85% complete
94% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/clone112/clone112.log" for further details.
[oracle@rac1-node1 ~]$
Top comments (0)